Skip to content

Instantly share code, notes, and snippets.

@giacecco
Created July 27, 2015 08:43
Show Gist options
  • Save giacecco/a97c5527184efc4c5152 to your computer and use it in GitHub Desktop.
Save giacecco/a97c5527184efc4c5152 to your computer and use it in GitHub Desktop.
# read from LRPP all addresses whose street names are not NULL and PAON or SAON are numeric; if both
# are, take the PAON only the DISTINCT below is important, otherwise down this script the AONs of
# properties that have been sold many times will weight more than the others
# (we did something similar to this already for
# http://sociam-olaf.tumblr.com/post/124663267575/how-many-addresses-in-one-town )
lrpp_addresses_with_numeric_aon <- collect(tbl(src_postgres("olaf"), sql(paste0("SELECT DISTINCT street, aon, pcd FROM ((SELECT street, CAST(SUBSTRING(paon, '^([0-9]+)') AS NUMERIC) AS aon, pcd FROM lr_pp WHERE town = '", target_town, "' AND street IS NOT NULL AND paon ~ '^[0-9]+') UNION (SELECT street, CAST(SUBSTRING(saon, '^([0-9]+)') AS NUMERIC) AS aon, pcd FROM lr_pp WHERE town = '", target_town, "' AND street IS NOT NULL AND paon !~ '^[0-9]+' AND saon ~ '^[0-9]+')) AS a", collapse = ""))))
> nrow(lrpp_addresses_with_numeric_aon)
[1] 74713
>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment