Geocoding with PostGis

For small scale geocoding needs I’ve always used the excellent Google maps API, but I recently wanted to do some custom geocoding and return the nearest NHS trust based on a user’s postcode.

Turns out building your own geocoder is really easy and worthwhile. Postcode data for the UK is available from the ONS:

https://www.ordnancesurvey.co.uk/business-and-government/products/code-point-open.html

(There are many other useful spatial data sets there as well!)

Each postcode comes with an easting and a northing, which isn’t the format I wanted to work with, but PostGis makes it easy to convert between geographies.

Just create a postcode table in your PostGres database (with PostGis extension enabled)  with the following properties:

create table postcode(
        postcode varchar(10) unique primary key,
        northing integer,
        easting integer,
        longitude decimal(18,6),
        latitude decimal(18,6),
        location geometry
);

Then insert the postcode data using a query like:

insert into postcode(postcode, easting, northing, location)
values 
('AB101AA',394251,806376,
ST_SetSRID(ST_MakePoint(394251,806376),27700))

The key thing here is to specify the geography 27700 in the function

ST_SetSRID(ST_MakePoint(easting, northing),27700)

Once all the postcodes are in, the following commands will populate the latitude and longitude columns and set the location column to use the lat/lng geometry instead of the easting/northing one. So you’re converting between geography 27700 and 4326.

update postcode set 
longitude=ST_x(ST_Transform(location,4326)), 
latitude=ST_y(ST_Transform(location,4326));

update postcode set location = 
ST_SetSRID(ST_MakePoint(longitude, latitude),4326);

Finally if you’re definitely not using the easting and northing, you can drop those columns. And there’s your reference table of postcodes set up.

Once you’ve got other tables of entities with locations (and PostGis supports all kinds of geometries) it’s easy to build queries based on postcodes.

For example, to find the area a postcode is in from a table of areas with locations that are polygons:

select * from area
where st_contains(location, 
(select location from postcode where postcode = 'SW8 5PZ'));

Or to find the nearest entity from a table of entities with single point locations:

select
(select e from entity as e
order by st_distance(p.location, e.location) limit 1)
from postcode as p
where p.postcode = 'SW8 5PZ';

Finally, a really helpful function is ST_AsGeoJSON; return a geometry field in GeoJson format:

select ST_AsGeoJson(location) as location
from postcode
where postcode = 'SW8 5PZ';

Code for my geocoder, including all the sql I used to populate the database is up on Github: https://github.com/labourdigital/labour-geocoder

And here’s an app we built over at the Labour Party using it:
http://www.labour.org.uk/content/torylocalcutscalculator

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s