Step 2 - Create AddressBase CSV database

Owned by Kim Stimpson
Last updated: 16 Aug, 2018
15 min read

Now we are going to create a PostgreSQL database based on the PostGIS template and load in the AddressBase data.

  • Create AddressBase database - this is the same as for AddressBase GML so click on the link for details.
  • Right click on the Schemas node, under your newly created addressbase database, and select New Schema... to create a schema called premium_csv and select iShareData as the Owner.

Now you need to run the following scripts in order:

Create the AddressBase Tables

Create tables 
DROP TABLE IF EXISTS premium_csv.basiclandpropertyunit;

CREATE TABLE premium_csv.basiclandpropertyunit
(
  record_identifier integer,
  change_type character varying,
  pro_order character varying,
  uprn bigint,
  logical_status integer,
  blpu_state character varying,
  blpu_state_date character varying(10),
  parent_uprn character varying,
  x_coordinate double precision,
  y_coordinate double precision,
  latitude double precision,
  longitude double precision,
  rpc integer,
  local_custodian_code integer,
  country character varying(1),
  start_date character varying(10),
  end_date character varying(10),
  last_update_date character varying(10),
  entry_date character varying(10),
  postal_address character varying(1),
  postcode_locator character varying(8),
  multi_occ_count integer
)
WITH (
  OIDS=FALSE
);
ALTER TABLE premium_csv.basiclandpropertyunit OWNER TO "iShareData";

-- Index: premium_csv.blpu_uprn_idx

-- DROP INDEX premium_csv.blpu_uprn_idx;

CREATE INDEX blpu_uprn_idx
  ON premium_csv.basiclandpropertyunit
  USING btree
  (uprn);

DROP TABLE IF EXISTS premium_csv.deliverypointaddress;

CREATE TABLE premium_csv.deliverypointaddress
(
  record_identifier character varying,
  change_type character varying,
  pro_order character varying,
  uprn double precision,
--  parent_addressable_uprn character varying(12),
  rm_udprn character varying(8),
  organisation_name character varying(60),
  department_name character varying(60),
  sub_building_name character varying(60),
  building_name character varying(60),
  building_number character varying(4),
  dependent_thoroughfare_name character varying(80),
  throughfare_name character varying(80),
  double_dependent_locality character varying(35),
  dependent_locality character varying(35),
  post_town character varying(30),
  postcode character varying(8),
  postcode_type character varying(2),
  delivery_point_suffix character varying(2),
  welsh_dependent_thoroughfare character varying(80),
  welsh_thoroughfare_name character varying(80),
  welsh_double_dependent_locality character varying(35),
  welsh_dependent_locality character varying(35),
  welsh_post_town character varying(30),
  po_box_number character varying(6),
  process_date character varying(10),
  start_date character varying(10),
  end_date character varying(10),
  last_update_date character varying(10),
  entry_date character varying(10)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE premium_csv.deliverypointaddress OWNER TO "iShareData";

-- Index: premium_csv.deliverypointaddress_uprn_idx

-- DROP INDEX premium_csv.deliverypointaddress_uprn_idx;

CREATE INDEX deliverypointaddress_uprn_idx
  ON premium_csv.deliverypointaddress
  USING btree
  (uprn);

DROP TABLE IF EXISTS premium_csv.landpropertyidentifier;

CREATE TABLE premium_csv.landpropertyidentifier
(
  record_identifier integer,
  change_type character varying,
  pro_order character varying,
  uprn double precision,
  lpi_key character varying(14),
  "language" character varying(3),
  logical_status character varying(1),
  start_date character varying(10),
  end_date character varying(10),
  last_update_date character varying(10),
  entry_date character varying(10),
  sao_start_number character varying(4),
  sao_start_suffix character varying(2),
  sao_end_number character varying(4),
  sao_end_suffix character varying(2),
  sao_text character varying(90),
  pao_start_number character varying(4),
  pao_start_suffix character varying(2),
  pao_end_number character varying(4),
  pao_end_suffix character varying(2),
  pao_text character varying(90),
  usrn numeric(8,0),
  usrn_match_indicator character varying(1),
  area_name character varying(35),
  "level" character varying(30),
  official_flag character varying(1)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE premium_csv.landpropertyidentifier OWNER TO "iShareData";

-- Index: premium_csv.lpi_uprn_idx

-- DROP INDEX premium_csv.lpi_uprn_idx;

CREATE INDEX lpi_uprn_idx
  ON premium_csv.landpropertyidentifier
  USING btree
  (uprn);

-- Index: premium_csv.lpi_usrn_idx

-- DROP INDEX premium_csv.lpi_usrn_idx;

CREATE INDEX lpi_usrn_idx
  ON premium_csv.landpropertyidentifier
  USING btree
  (usrn);

DROP TABLE IF EXISTS premium_csv.organisation;

CREATE TABLE premium_csv.organisation
(
  record_identifier character varying,
  change_type character varying,
  pro_order character varying,
  uprn double precision,
  org_key character varying(14),
  organisation character varying(100),
  legal_name character varying(60),
  start_date character varying(10),
  end_date character varying(10),
  last_update_date character varying(10),
  entry_date character varying(10)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE premium_csv.organisation OWNER TO "iShareData";

-- Index: premium_csv.organisation_uprn_idx

-- DROP INDEX IF EXISTS premium_csv.organisation_uprn_idx;

CREATE INDEX organisation_uprn_idx
  ON premium_csv.organisation
  USING btree
  (uprn);

DROP TABLE IF EXISTS premium_csv.street;

CREATE TABLE premium_csv.street
(
  record_identifier character varying,
  change_type character varying,
  pro_order character varying,
  usrn double precision,
  record_type numeric(1,0),
  swa_org_ref_naming character varying(4),
  state character varying(1),
  state_date character varying(10),
  street_surface character varying(1),
  street_classification character varying(2),
  "version" character varying(3),
  street_start_date character varying(10),
  street_end_date character varying(10),
  last_update_date character varying(10),
  record_entry_date character varying(10),
  street_start_x double precision,
  street_start_y double precision,
  street_start_lat double precision,
  street_start_long double precision,
  street_end_x double precision,
  street_end_y double precision,
  street_end_lat double precision,
  street_end_long double precision,
  street_tolerance character varying(3)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE premium_csv.street OWNER TO "iShareData";

-- Index: premium_csv.street_usrn_idx

-- DROP INDEX IF EXISTS premium_csv.street_usrn_idx;

CREATE INDEX street_usrn_idx
  ON premium_csv.street
  USING btree
  (usrn);

DROP TABLE IF EXISTS premium_csv.streetdescriptiveidentifier;

CREATE TABLE premium_csv.streetdescriptiveidentifier
(
  record_identifier character varying,
  change_type character varying,
  pro_order character varying,
  usrn double precision,
  street_description character varying(100),
  locality_name character varying(35),
  town_name character varying(30),
  administrative_area character varying(30),
  "language" character varying(3),
  start_date character varying(10),
  end_date character varying(10),
  last_update_date character varying(10),
  entry_date character varying(10)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE premium_csv.streetdescriptiveidentifier OWNER TO "iShareData";

CREATE INDEX streetdesc_usrn_idx
  ON premium_csv.streetdescriptiveidentifier
  USING btree
  (usrn);

Load the AddressBase data into the database

Load Addressbase
copy premium_csv.street from E'F:\\os_data_supply\\output\\11_street.csv' WITH DELIMITER ',' CSV;
copy premium_csv.streetdescriptiveidentifier from E'F:\\os_data_supply\\output\\15_streetdescriptor.csv' WITH DELIMITER ',' CSV;
copy premium_csv.organisation from E'F:\\os_data_supply\\output\\31_organisation.csv' WITH DELIMITER ',' CSV;
copy premium_csv.basiclandpropertyunit from E'F:\\os_data_supply\\output\\21_blpu.csv' WITH DELIMITER ',' CSV;
copy premium_csv.landpropertyidentifier from E'F:\\os_data_supply\\output\\24_lpi.csv' WITH DELIMITER ',' CSV;
copy premium_csv.deliverypointaddress from E'F:\\os_data_supply\\output\\28_dpa.csv' WITH DELIMITER ',' CSV;

Create Views

Create views 
DROP VIEW IF EXISTS premium_csv.addresses_geo_for_ishare;
DROP TABLE IF EXISTS premium_csv.blpu_geo;
CREATE TABLE premium_csv.blpu_geo AS
SELECT row_number() OVER() AS ogc_fid, st_setsrid(st_point(x_coordinate,y_coordinate),27700) as wkb_geometry, * FROM premium_csv.basiclandpropertyunit;

CREATE OR REPLACE VIEW premium_csv.addresses_geo_for_ishare AS 
  SELECT 
	ogc_fid, b.uprn::character varying AS uprn, wkb_geometry, dpa.rm_udprn, dpa.organisation_name, b.blpu_state, b.logical_status, b.postal_address, b.postcode_locator, 
	l.sao_start_number, l.sao_end_number, l.sao_start_suffix, l.sao_end_suffix, l.sao_text, 
	l.pao_start_number, l.pao_end_number, l.pao_start_suffix, l.pao_end_suffix, l.pao_text, 
	s.usrn, s.street_description, s.locality_name, s.town_name
  FROM premium_csv.blpu_geo b
              LEFT JOIN premium_csv.landpropertyidentifier l ON b.uprn = l.uprn
         LEFT JOIN premium_csv.deliverypointaddress dpa ON b.uprn = dpa.uprn
    LEFT JOIN premium_csv.streetdescriptiveidentifier s ON l.usrn = s.usrn
   WHERE b.postal_address <> 'N' and b.logical_status = '1' and (b.blpu_state = '1' or b.blpu_state = '2' or b.blpu_state = '3' or b.blpu_state = '4' or b.blpu_state = '5' or length(b.blpu_state) = 0);
  
ALTER TABLE premium_csv.addresses_geo_for_ishare OWNER TO "iShareData";
COMMENT ON VIEW premium_csv.addresses_geo_for_ishare IS 'Simple view of AddressBase (premium) geographic addresses in PostgreSQL from CSV for iShare to build - excludes non postal and incorrect blpu state!

     Author: Mike Saunt, Astun Technology Ltd
     Version 0.1 - 21st July 2014
     Version 0.2 - 26th July 2014
     © Copyright 2014
';

-- View: premium_csv.addresses_geo

-- DROP VIEW premium_csv.addresses_geo;

CREATE OR REPLACE VIEW premium_csv.addresses_geo AS 
 SELECT formatted_address.ogc_fid, formatted_address.wkb_geometry, formatted_address.uprn, formatted_address.rm_udprn, ((((ltrim(formatted_address.saon || formatted_address.paon) || formatted_address.street_description) || ', '::text) || formatted_address.localitytown) || ' '::text) || formatted_address.postcode::text AS full_address_caps, (initcap(((ltrim(formatted_address.saon || formatted_address.paon) || formatted_address.street_description) || ', '::text) || formatted_address.localitytown) || ' '::text) || formatted_address.postcode::text AS full_address, formatted_address.postcode
   FROM ( SELECT premium_addresses.ogc_fid, premium_addresses.wkb_geometry, premium_addresses.uprn, premium_addresses.rm_udprn, btrim(((((
                CASE
                    WHEN length(premium_addresses.organisation_name::text) > 0 THEN premium_addresses.organisation_name::text || ', '::text
                    ELSE ''::text
                END || 
                CASE
                    WHEN length(premium_addresses.sao_start_number::text) > 0 THEN premium_addresses.sao_start_number::text
                    ELSE ''::text
                END) || 
                CASE
                    WHEN length(premium_addresses.sao_start_suffix::text) > 0 THEN premium_addresses.sao_start_suffix::text
                    ELSE ''::text
                END) || 
                CASE
                    WHEN length(premium_addresses.sao_end_number::text) > 0 THEN '-'::text || premium_addresses.sao_end_number::text
                    ELSE ''::text
                END) || 
                CASE
                    WHEN length(premium_addresses.sao_end_suffix::text) > 0 THEN premium_addresses.sao_end_suffix::text
                    ELSE ''::text
                END) || 
                CASE
                    WHEN length(premium_addresses.sao_text::text) > 0 THEN (' '::text || premium_addresses.sao_text::text) || ', '::text
                    ELSE ''::text
                END) || ' '::text AS saon, ltrim((((
                CASE
                    WHEN length(premium_addresses.pao_start_number::text) > 0 THEN premium_addresses.pao_start_number::text
                    ELSE ''::text
                END || 
                CASE
                    WHEN length(premium_addresses.pao_start_suffix::text) > 0 THEN premium_addresses.pao_start_suffix::text
                    ELSE ''::text
                END) || 
                CASE
                    WHEN length(premium_addresses.pao_end_number::text) > 0 THEN '-'::text || premium_addresses.pao_end_number::text
                    ELSE ''::text
                END) || 
                CASE
                    WHEN length(premium_addresses.pao_end_suffix::text) > 0 THEN premium_addresses.pao_end_suffix::text
                    ELSE ''::text
                END) || 
                CASE
                    WHEN length(premium_addresses.pao_text::text) > 0 THEN (' '::text || premium_addresses.pao_text::text) || ', '::text
                    ELSE ' '::text
                END) AS paon, 
                CASE
                    WHEN length(premium_addresses.street_description::text) > 0 THEN premium_addresses.street_description::text
                    ELSE ''::text
                END AS street_description, 
                CASE
                    WHEN length(premium_addresses.locality_name::text) > 0 THEN premium_addresses.locality_name::text || ', '::text
                    ELSE ''::text
                END || 
                CASE
                    WHEN length(premium_addresses.town_name::text) > 0 THEN premium_addresses.town_name::text
                    ELSE ''::text
                END AS localitytown, premium_addresses.postcode_locator AS postcode
           FROM ( SELECT row_number() OVER ()::integer AS ogc_fid, b.uprn, st_setsrid(st_point(b.x_coordinate::numeric::double precision, b.y_coordinate::numeric::double precision), 27700) AS wkb_geometry, dpa.rm_udprn, b.postal_address, b.postcode_locator, l.sao_start_number, l.sao_end_number, l.sao_start_suffix, l.sao_end_suffix, l.sao_text, l.pao_start_number, l.pao_end_number, l.pao_start_suffix, l.pao_end_suffix, l.pao_text, s.usrn, s.street_description, s.locality_name, s.town_name, dpa.organisation_name
                   FROM premium_csv.basiclandpropertyunit b
              LEFT JOIN premium_csv.landpropertyidentifier l ON b.uprn::text = l.uprn::text
         LEFT JOIN premium_csv.deliverypointaddress dpa ON b.uprn::text = dpa.uprn::text
    LEFT JOIN premium_csv.streetdescriptiveidentifier s ON l.usrn::text = s.usrn::text) premium_addresses) formatted_address;

ALTER TABLE premium_csv.addresses_geo OWNER TO "iShareData";
COMMENT ON VIEW premium_csv.addresses_geo IS 'Simple, nicely formatted view of AddressBase (premium) geographic addresses in PostgreSQL from CSV!

     Author: Mike Saunt, Astun Technology Ltd
     Version 0.1 - 21st July 2014
     © Copyright 2014
';


-- View: premium_csv.addresses_postal

-- DROP VIEW premium_csv.addresses_postal;

CREATE OR REPLACE VIEW premium_csv.addresses_postal AS 

 SELECT row_number() OVER ()::bigint AS ogc_fid, blpu.uprn, st_setsrid(st_point(blpu.x_coordinate::numeric::double precision, blpu.y_coordinate::numeric::double precision), 27700) AS wkb_geometry, dpa.rm_udprn, (((((((((
        CASE
            WHEN length(dpa.department_name::text) > 0 THEN dpa.department_name::text || ', '::text
            ELSE ''::text
        END || 
        CASE
            WHEN length(dpa.organisation_name::text) > 0 THEN dpa.organisation_name::text || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.sub_building_name::text) > 0 THEN dpa.sub_building_name::text || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.building_name::text) > 0 THEN dpa.building_name::text || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.building_number::text) > 0 THEN dpa.building_number::text || ' '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.dependent_thoroughfare_name::text) > 0 THEN dpa.dependent_thoroughfare_name::text || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.throughfare_name::text) > 0 THEN dpa.throughfare_name::text || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.double_dependent_locality::text) > 0 THEN dpa.double_dependent_locality::text || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.dependent_locality::text) > 0 THEN dpa.dependent_locality::text || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.post_town::text) > 0 THEN dpa.post_town::text || ' '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.postcode::text) > 0 THEN dpa.postcode
            ELSE ''::character varying
        END::text AS full_address_caps, (((((((((
        CASE
            WHEN length(dpa.department_name::text) > 0 THEN initcap(dpa.department_name::text) || ', '::text
            ELSE ''::text
        END || 
        CASE
            WHEN length(dpa.organisation_name::text) > 0 THEN initcap(dpa.organisation_name::text) || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.sub_building_name::text) > 0 THEN initcap(dpa.sub_building_name::text) || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.building_name::text) > 0 THEN initcap(dpa.building_name::text) || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.building_number::text) > 0 THEN initcap(dpa.building_number::text) || ' '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.dependent_thoroughfare_name::text) > 0 THEN initcap(dpa.dependent_thoroughfare_name::text) || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.throughfare_name::text) > 0 THEN initcap(dpa.throughfare_name::text) || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.double_dependent_locality::text) > 0 THEN initcap(dpa.double_dependent_locality::text) || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.dependent_locality::text) > 0 THEN initcap(dpa.dependent_locality::text) || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.post_town::text) > 0 THEN initcap(dpa.post_town::text) || ' '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN length(dpa.postcode::text) > 0 THEN dpa.postcode
            ELSE ''::character varying
        END::text AS full_address, 
        CASE
            WHEN dpa.postcode IS NOT NULL THEN dpa.postcode
            ELSE ''::character varying
        END AS postcode
   FROM premium_csv.deliverypointaddress dpa
   LEFT JOIN premium_csv.basiclandpropertyunit blpu ON dpa.uprn::text = blpu.uprn::text;

ALTER TABLE premium_csv.addresses_postal OWNER TO "iShareData";
COMMENT ON VIEW premium_csv.addresses_postal IS 'Simple, nicely formatted view of AddressBase (premium) postal addresses in PostgreSQL that has been imported from CSV!

     Author: Mike Saunt, Astun Technology Ltd
     Version 0.1 - 17th July 2014
     © Copyright 2014

';
 
CREATE INDEX blpu_geo_geom_idx
  ON premium_csv.blpu_geo
  USING gist
  (wkb_geometry);

CREATE INDEX blpu_geo_uprn_idx
  ON premium_csv.blpu_geo
  USING btree
  (uprn);

-- View: premium_csv.addresspoint

-- DROP VIEW premium_csv.addresspoint;

CREATE OR REPLACE VIEW premium_csv.addresspoint AS 
 SELECT st_geomfromtext(((('POINT('::text || blpu.x_coordinate) || ' '::text) || blpu.y_coordinate) || ')'::text, 27700) AS wkb_geometry,
    blpu.uprn::character(18) AS "OSAPR",
    dpa.department_name::character(80) AS "DEPARTMENT",
    dpa.po_box_number::character(6) AS "BOX_NUMBER",
    dpa.organisation_name::character(70) AS "ORGANISATION",
    dpa.building_number::character(5) AS "BUILDING_NUMBER",
    dpa.sub_building_name::character(60) AS "SUB_BUILDING",
    dpa.building_name::character(60) AS "BUILDING",
    dpa.throughfare_name::character(60) AS "THOROUGHFARE",
    dpa.dependent_thoroughfare_name::character(80) AS "DEPENDENT_THOROUGHFARE",
    dpa.post_town::character(60) AS "POST_TOWN_NAME",
    dpa.dependent_locality::character(50) AS "DEPENDANT_LOCALITY",
    dpa.double_dependent_locality::character(50) AS "DOUBLE_DEPENDANT_LOCALITY",
    ''::character(50) AS "POSTAL_COUNTY",
    dpa.postcode::character(10) AS "POSTCODE",
    blpu.x_coordinate AS "COORD_X",
    blpu.y_coordinate AS "COORD_Y"
   FROM premium_csv.deliverypointaddress dpa,
    premium_csv.basiclandpropertyunit blpu
  WHERE dpa.uprn = blpu.uprn::double precision;

ALTER TABLE premium_csv.addresspoint
  OWNER TO "iShareData";
COMMENT ON VIEW premium_csv.addresspoint
  IS 'Simple, nicely formatted view of AddressPoint postal addresses in PostgreSQL that has been imported from AdderssBase Premium CSV!';


Once you have downloaded the scripts you can run them in pgAdmin by clicking the SQL magnifying glass on the toolbar.