ETL Ordnance Survey AddressBase Plus to Postgresql on a mac

readme.md

Addressbase plus data loading to postgres on mac

The instructions in the official getting started are tailored to Windows and AddressBase premium. This is a simple guide for those working on mac using Addressbase Plus.

You should received a zip file with the following folder structure when unzipped;

ABFLGB_CSV
|
+-- data
|   |-- AddressBasePlus_FULL_2018-03-19_001.csv
|   |-- AddressBasePlus_FULL_2018-03-19_036.csv
+-- doc
|   |-- medialis.txt
+-- resources
|   |-- Addressbase-products-local-custodian-codes.docx
|   |-- AddressBasePlus_Header.csv
|   |-- AddressBase_products_classification_scheme.csv
+-- readme_GDBL.txt

First merge the csv data files together, there should be ~36. Open mac Terminal at folder with unzipped csv files, run this command

cat *.csv > merged.csv 

This will take a while to run, once done go to your database client and set up a table

--From original OS sql script 
CREATE TABLE addressbase_plus(
UPRN bigint NOT NULL,
UDPRN bigint,
CHANGE_TYPE varchar,
STATE bigint,
STATE_DATE Date,
CLASS varchar,
PARENT_UPRN bigint,
X_COORDINATE numeric,
Y_COORDINATE numeric,
LATITUDE numeric,
LONGITUDE numeric,
RPC bigint,
LOCAL_CUSTODIAN_CODE bigint,
COUNTRY varchar,
LA_START_DATE Date,
LAST_UPDATE_DATE Date,
ENTRY_DATE Date,
RM_ORGANISATION_NAME varchar,
LA_ORGANISATION varchar,
DEPARTMENT_NAME varchar,
LEGAL_NAME varchar,
SUB_BUILDING_NAME varchar,
BUILDING_NAME varchar,
BUILDING_NUMBER varchar,
SAO_START_NUMBER bigint,
SAO_START_SUFFIX varchar,
SAO_END_NUMBER bigint,
SAO_END_SUFFIX varchar,
SAO_TEXT varchar,
ALT_LANGUAGE_SAO_TEXT varchar,
PAO_START_NUMBER bigint,
PAO_START_SUFFIX varchar,
PAO_END_NUMBER bigint,
PAO_END_SUFFIX varchar,
PAO_TEXT varchar,
ALT_LANGUAGE_PAO_TEXT varchar,
USRN bigint,
USRN_MATCH_INDICATOR varchar,
AREA_NAME varchar,
LEVEL varchar,
OFFICIAL_FLAG varchar,
OS_ADDRESS_TOID varchar,
OS_ADDRESS_TOID_VERSION bigint,
OS_ROADLINK_TOID varchar,
OS_ROADLINK_TOID_VERSION bigint,
OS_TOPO_TOID varchar,
OS_TOPO_TOID_VERSION bigint,
VOA_CT_RECORD bigint,
VOA_NDR_RECORD bigint,
STREET_DESCRIPTION varchar,
ALT_LANGUAGE_STREET_DESCRIPTION varchar,
DEPENDENT_THOROUGHFARE varchar,
THOROUGHFARE varchar,
WELSH_DEPENDENT_THOROUGHFARE varchar,
WELSH_THOROUGHFARE varchar,
DOUBLE_DEPENDENT_LOCALITY varchar,
DEPENDENT_LOCALITY varchar,
LOCALITY varchar,
WELSH_DEPENDENT_LOCALITY varchar,
WELSH_DOUBLE_DEPENDENT_LOCALITY varchar,
TOWN_NAME varchar,
ADMINISTRATIVE_AREA varchar,
POST_TOWN varchar,
WELSH_POST_TOWN varchar,
POSTCODE varchar,
POSTCODE_LOCATOR varchar,
POSTCODE_TYPE varchar,
DELIVERY_POINT_SUFFIX varchar,
ADDRESSBASE_POSTAL varchar,
PO_BOX_NUMBER varchar,
WARD_CODE varchar,
PARISH_CODE varchar,
RM_START_DATE Date,
MULTI_OCC_COUNT bigint,
VOA_NDR_P_DESC_CODE varchar,
VOA_NDR_SCAT_CODE varchar,
ALT_LANGUAGE varchar);

You will now have a blank table ready to receive the data, you copy this in by running this command. This will take a long time to run.

COPY addressbase_plus
FROM 'filepath/to/merged.csv' delimiter ',' quote '"' escape '"' csv;