Importing Ordnance Survey Open Data in to PostgreSQL with PostGIS

Some time ago, I looked at some uses for Ordnance Survey Open Data, coming to the conclusion that a sensible way to work with it would be to import it in to a geospatial-enabled database.

Each set of data is provided in ESRI Shapefile format, and has four files:

  • shp – shape format
  • shx – shape index format
  • dbf – attribute format in dBase IV format
  • prj – projection format

The shp2pgsql command converts SHP files in to a set of SQL commands which will effectively import the data in to PostgreSQL. Here’s a ridiculously simple guide to importing a file:

createdb os_opendata
psql -d os_opendata -c "CREATE EXTENSION POSTGIS"
shp2pgsql <filename>.shp <table_name> | psql -d os_opendata

Depending on the speed of your machine, in a few seconds you’ll find a new table in your database with all the data included.

And finally, what if you just want to import all of the data at once? Try this:

find Data/ -name "*.shp" | xargs -I % -n1 shp2pgsql % | psql -d os_opendata

Converting OSGB36 (Eastings/Northings) to WGS84 (Longitude/Latitude) in Ruby

The excellent people at the Greater London Assembly have released a list of bus stops and bus routes in London. The coordinates of each bus stop are in eastings and northings, and I wanted to convert these to longitude and latitude for my Ruby on Rails application.

Using the proj4rb gem and some projection definitions from spatialreference.org – with some help from Harry Wood’s blog, I came up with the following code:

#!/usr/bin/ruby

require 'rubygems'
gem 'proj4rb'
require 'proj4'

easting = 529978
northing = 186491

srcPoint = Proj4::Point.new(easting, northing)

srcProj = Proj4::Projection.new('+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +datum=OSGB36 +units=m +no_defs')
dstProj = Proj4::Projection.new('+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs')

dstPoint = srcProj.transform(dstProj, srcPoint)

print "lat=#{dstPoint.lat * (180 / Math::PI)}n"
print "lon=#{dstPoint.lon * (180 / Math::PI)}n"

To convert WGS84 to OSGB36:

#!/usr/bin/ruby

require 'rubygems'
gem 'proj4rb'
require 'proj4'

latitude = 51.5623279577278
longitude = -0.126277004538848

srcPoint = Proj4::Point.new(longitude * (Math::PI / 180), latitude * (Math::PI / 180))

srcProj = Proj4::Projection.new('+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs')
dstProj = Proj4::Projection.new('+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +datum=OSGB36 +units=m +no_defs')

dstPoint = srcProj.transform(dstProj, srcPoint)

print "easting=#{dstPoint.x}n"
print "northing=#{dstPoint.y}n"