1.R

require(dplyr)
require(RPostgreSQL)

# load the pcd11_oa11_lsoa11_msoa11_lad11_ew_lu dataset
pcenum <- read.csv(pipe("unzip -qc \"/Users/giacecco/Data archive/ons/pcd11_oa11_lsoa11_msoa11_lad11_ew_lu/150725/Postcodes_(Enumeration)_(2011)_to_output_areas_(2011)_to_lower_layer_SOA_(2011)_to_middle_layer_SOA_(2011)_to_local_authority_districts_(2011)_E+W_lookup.zip\" *.csv"), na.strings = c(""), stringsAsFactors = F)

# use NSAL calculate how many properties (UPRNs) per Output Area
properties_per_oa <- collect(tbl(src_postgres("olaf"), sql("SELECT oa11, COUNT(uprn) AS oa_properties_no FROM ons_nsal WHERE ctry ~ '^E|^W' GROUP BY oa11")))

# join the two datasets
properties_per_pcd <- inner_join(pcenum[, c("PCD7", "OA11CD", "PCDOASPLT")], properties_per_oa, by = c("OA11CD" = "oa11"))

# show all postcodes in the OA where I live
properties_per_pcd[properties_per_pcd$OA11CD == properties_per_pcd[properties_per_pcd$PCD7 == "HP4 3JD", "OA11CD"], ]

output

> properties_per_pcd[properties_per_pcd$OA11CD == properties_per_pcd[properties_per_pcd$PCD7 == "HP4 3JD", "OA11CD"], ]
          PCD7    OA11CD PCDOASPLT oa_properties_no
678216 HP4 3LW E00118724         0              160
678217 HP4 3LZ E00118724         0              160
678218 HP4 3XZ E00118724         0              160
678219 HP4 3LU E00118724         0              160
678220 HP4 3JD E00118724         0              160
678221 HP4 3YE E00118724         0              160
678222 HP4 3JB E00118724         0              160
678223 HP4 3JN E00118724         0              160
678224 HP4 3YF E00118724         0              160
678225 HP4 3JA E00118724         1              160
>