1.R

require(dplyr)
require(RPostgreSQL)

target_town <- "SOUTHAMPTON"

# get all postcodes for the target town from LRPP, remembering that the town in LRPP refers to the
# "post town", hence include smaller populated places (hamlets etc.) that are described as
# independent entities in OSON
lrpp_postcodes <- collect(tbl(src_postgres("olaf"), sql(paste0("SELECT DISTINCT pcd FROM lr_pp WHERE town = '", target_town , "'", collapse = ""))))

# TODO: because of being a historical archive, LRPP often refers to retired postcodes! If I wanted 
# to be thorough here I should "translate" the old postcodes to the current

# the same, but from OSON
oson_postcodes <- collect(tbl(src_postgres("olaf"), sql(paste0("SELECT DISTINCT pcd FROM os_open_names WHERE pcd IS NOT NULL AND UPPER(populated_place) = '", target_town , "'", collapse = ""))))

# get the OS ids of the town and villages etc. associated to any of the above postcodes
oson_populated_places_ids <- unique((collect(tbl(src_postgres("olaf"), sql("SELECT DISTINCT pcd, 'osgb' || SUBSTRING(populated_place_uri FROM '/([0-9]+)$') AS populated_place_uri FROM os_open_names WHERE pcd IS NOT NULL AND populated_place_uri != ''"))) %>% filter(pcd %in% union(lrpp_postcodes$pcd, oson_postcodes$pcd)))$populated_place_uri)

# and then their actual names and local_type, too
oson_populated_places <- collect(tbl(src_postgres("olaf"), sql(paste0("SELECT DISTINCT name1 AS name, local_type as localType FROM os_open_names WHERE id IN (", paste0("'", oson_populated_places_ids, "'", collapse=", "), ")", collapse = ""))))