In this blog post we’ll learn how to use Ordnance Survey AddressBase data with the QGIS Discovery plugin for searching addresses.
The AddressBase data will be loaded into a PostGIS table for Discovery to query. At this stage we should already have a functioning PostgreSQL / PostGIS installation.
A previous blog post describes how to quickly set up such an environment.
Let’s now create a table for storing the addressbase data. In the example below we’ll create a table called
addressbase in the
The script below can be executed through pgAdminIII.
To run the script:
When the query has finished you should see Query returned successfully with no result in … seconds. in the Messages panel:
At this point we should be able to locate the new
addressbase table within the
If you can’t see the schema / table you probably need to refresh the schemas / tables views in pgAdminIII’s Object browser panel by hitting F5.
-- Create the destination schema if required CREATE SCHEMA IF NOT EXISTS os_address; -- Create a function which will populate the full_address and geom columns as -- data are imported CREATE OR REPLACE FUNCTION create_geom_and_address() RETURNS trigger AS $$ BEGIN -- The geometry -- Set it based on the x_coord and y_coord fields NEW.geom = ST_SetSRID(ST_MakePoint(NEW.x_coordinate, NEW.y_coordinate), 27700); -- The full address -- Initialise it NEW.full_address = ''; -- Build the full address by only including optional address components if they -- exist IF NEW.organisation_name IS NOT NULL AND length(NEW.organisation_name) > 0 THEN NEW.full_address = NEW.full_address || NEW.organisation_name || ', '; END IF; IF NEW.department_name IS NOT NULL AND length(NEW.department_name) > 0 THEN NEW.full_address = NEW.full_address || NEW.department_name || ', '; END IF; IF NEW.po_box_number IS NOT NULL AND length(NEW.po_box_number) > 0 THEN NEW.full_address = NEW.full_address || NEW.po_box_number || ', '; END IF; IF NEW.sub_building_name IS NOT NULL AND length(NEW.sub_building_name) > 0 THEN NEW.full_address = NEW.full_address || NEW.sub_building_name || ', '; END IF; IF NEW.building_name IS NOT NULL AND length(NEW.building_name) > 0 THEN NEW.full_address = NEW.full_address || NEW.building_name || ', '; END IF; IF NEW.building_number IS NOT NULL THEN NEW.full_address = NEW.full_address || NEW.building_number || ', '; END IF; IF NEW.dependent_thoroughfare IS NOT NULL AND length(NEW.dependent_thoroughfare) > 0 THEN NEW.full_address = NEW.full_address || NEW.dependent_thoroughfare || ', '; END IF; IF NEW.thoroughfare IS NOT NULL AND length(NEW.thoroughfare) > 0 THEN NEW.full_address = NEW.full_address || NEW.thoroughfare || ', '; END IF; NEW.full_address = NEW.full_address || NEW.post_town || ', '; IF NEW.double_dependent_locality IS NOT NULL AND length(NEW.double_dependent_locality) > 0 THEN NEW.full_address = NEW.full_address || NEW.double_dependent_locality || ', '; END IF; IF NEW.dependent_locality IS NOT NULL AND length(NEW.dependent_locality) > 0 THEN NEW.full_address = NEW.full_address || NEW.dependent_locality || ', '; END IF; NEW.full_address = NEW.full_address || NEW.postcode; RETURN NEW; END; $$ LANGUAGE 'plpgsql'; -- Drop any existing addressbase table DROP TABLE IF EXISTS os_address.addressbase CASCADE; CREATE TABLE os_address.addressbase ( -- id will be the primary key, populated automatically id serial NOT NULL, uprn bigint NOT NULL, os_address_toid varchar(24) NOT NULL, -- os_address_toid bigint NOT NULL, udprn integer NOT NULL, organisation_name varchar(60), department_name varchar(60), po_box_number varchar(6), sub_building_name varchar(30), building_name varchar(50), building_number smallint, dependent_thoroughfare varchar(80), thoroughfare varchar(80), post_town varchar(30) NOT NULL, double_dependent_locality varchar(35), dependent_locality varchar(35), postcode varchar(8) NOT NULL, postcode_type char(1) NOT NULL, x_coordinate numeric(8,2) NOT NULL, y_coordinate numeric(9,2) NOT NULL, latitude numeric(9,7) NOT NULL, longitude numeric(8,7) NOT NULL, rpc char(1) NOT NULL, country char(1) NOT NULL, change_type char(1) NOT NULL, la_start_date date NOT NULL, rm_start_date date NOT NULL, last_update_date date NOT NULL, class char(1) NOT NULL, -- the next two fields are populated automatically on insert full_address text NOT NULL, geom geometry(Point,27700) NOT NULL, CONSTRAINT addressbase_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); -- Create a pg_trgm index on the full_address column -- This will allow super-fast, case-insensitive search on the column CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX addressbase_full_address_gin_trgm ON os_address.addressbase USING gin ("full_address" gin_trgm_ops); -- Spatial index for the geometry column CREATE INDEX addressbase_geom_gist ON os_address.addressbase USING gist (geom); -- trigger to create points and addresses -- This trigger will be executed on each row inserted, calling the function defined above CREATE TRIGGER tr_create_geom_and_address BEFORE INSERT ON os_address.addressbase FOR EACH ROW EXECUTE PROCEDURE create_geom_and_address();
The script above has:
full_address will be used to store various address components into a sensible, human readable address. geom will be used to store point geometry based on address eastings/northings.
See the script comments for more information / detail.
At this point we have an empty table ready to accept our AddressBase data. We will now import the data using pgAdminIII. Extract the CSV files for the addresses, you should end up seeing one or more CSV files, for example AddressBase_FULL_2016-03-19_001.csv
An import dialog should appear. Select the first CSV file and set the settings in the File Options tab as shown here:
Uncheck the id, full_address and geom columns in the Columns tab as shown here:
Click Import. After a few seconds the dialog may report (Not Responding). This is nothing to worry about, be patient.
When the import process completes, close the import dialog and repeat the above steps with any remaining CSV files.
At this stage the data has been imported and the full_address field should contain sensible, human-readable addresses.
With the data loaded in QGIS, we can now configure Discovery to make use of it.
Congratulations! QGIS should now be set up to search your AddressBase data.