4.R

# 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 = ""))))

output

> nrow(lrpp_addresses_with_numeric_aon)
[1] 74713
>