Step 2 - Create AddressBase CSV database

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

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

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.