Loading Ordnance Survey Open Data into SQL Server 2008

One of the things that I didn’t realise until I started blogging was how interesting I would find the data collected on who actually reads my blog, where they’ve come from, and what they were looking for. As I expected based on the content I’ve published so far, many of my visitors have been looking for information on the Bing Maps tile system, on WMS servers, SQL Server spatial reference systems, and on terrain and elevation information. I hope that some of them have found some helpful information… However, last month, for example, I was also fascinated to find a number of people had found my site having searched for “Alastair Aitchison professional CV” – who are you? what were you looking for?!

Anyway, reading through my search engine referrals, another big area that people seem to be looking for information on is about using Ordnance Survey data in SQL Server. I have posted about the Ordnance Survey before, and about spatial data in SQL Server, but not specifically about the two together, so those people have probably been left disappointed. This post is an attempt to rectify the situation…

The Ordnance Survey, if you don’t know, is the national mapping agency of Great Britain, and it is the source of most of the high-quality mapping information in the country. For a long time, however, their data was tightly-controlled, and not affordable to use unless you were a large organisation capable of paying a corporate licence. This was hugely frustrating for small or charitable organisations trying to make use of spatial information.

Now, ex-Prime Minister Gordon Brown didn’t do much for the UK, but it is largely thanks to him that in the last year the situation has changed. The story goes that, at some formal dinner or other, Gordon Brown ended up having a conversation with Sir Tim Berners-Lee. The prime minister asked what the government should do to make better use of the internet, to which Berners-Lee replied “Put all the government’s data online”. It’s not happened overnight, but there have been some great steps taken towards increasing public access to UK government data, including crime information, census and demographic information, and spatial information from the Ordnance Survey. So here’s a step-by-step guide as to how to load that data.

Get Some OS Data

First, acquire the data from the Ordnance Survey Open Data website. There are several “products” to choose from. The ones I find most interesting are:

    1. Strategi, Meridian2, OS VectorMap District – vector polygon, polyline, and point features of e.g. administrative boundaries, developed land use areas, woodland, roads, rivers and coastline, at small- medium- and high- scale respectively.
    2. Code-Point Open – locates every postcode unit, and maps to corresponding coordinates / health authority / county etc.
    3. 1:50,000 Scale Gazetteer – as the name says, a gazetteer of placenames and locations

Note that the Ordnance Survey have not made all their data freely-available – if you want the really high-quality datasets (such as OS Mastermap), you’ll still have to pay for it, but OpenData gives you a good start. Even though the OpenData products are of limited resolution (equivalent to, say, the 1:50,000 Landranger series of maps), they are of good quality – better quality data than, say the equivalent  TIGER data in the U.S – and they are much better than having no free data at all, which was the case 12 months ago…

Prepare the Data

Most OS datasets are provided in ESRI shapefile format. If you’re using one of the smaller scale datasets (e.g. Strategi or Meridian2), each feature layer (i.e. roads, rivers, railways) will come in its own shapefile. You’ll probably want to keep each layer in its table in the database, so this is fine.

However, if you want to load the larger scale OS VectorMap data you’ll find that it is split up into smaller files, each one labelled with the appropriate 100km x 100km grid square reference (e.g. TQ, TL, NT…). Then, within the download of each of these grid squares,  there is a subdirectory for each 10km x 10km subsquare. So within the TQ directory there will be folders labelled TQ00, TQ01, TQ02, … , TQ99. Each of these subfolders follows exactly the same structure – containing one shapefile for each of the feature layers within that square. So, if you want to load the features of a complete 100km x 100km square of data in one go, you first need to merge the shapefiles in all of the subdirectories together. You can do so using OGR2OGR from the command line prompt as in the following script:

for /R %f in (*.shp) do (
  if not exist %~nf.shp (
    ogr2ogr -f “ESRI Shapefile” “%~nf.shp” “%f”
  )
  else (
    ogr2ogr -f “ESRI Shapefile” -update -append “%~nf.shp” “%f”
  )
)

This will look for all shapefiles in all subdirectories of the directory in which the script is run – if a file of the same name (e.g. AdministrativeBoundary.shp) has been found in another subdirectory already, they will be merged together, if not, a new file will be created. Resulting merged files will be created in the current directory. (This was adapted from a script by Darren Cope)

Load the Data

Having prepared the shapefiles, you can load them into SQL Server using a tool of your choice…. if you don’t want to splash out a commercial licence for Safe FME then I recommend Shape2SQL – you can use it to load shapefiles to SQL Server either via the GUI interface or via the commandline as described here.

Ordnance Survey data is defined using the National Grid of Great Britain, so always make sure that you load your data into a column of the geometry datatype, using SRID 27700.

image

And that’s all there is to it! Here’s the data loaded from combined table of the OS Vector Map settlement_area and road_line shapefiles, showing the buildings and roads of the city centre of Norwich (still beautiful even when viewed in the pastel shades of SQL Server Management Studio):

image

About these ads
This entry was posted in Spatial, SQL Server and tagged , , . Bookmark the permalink.

14 Responses to Loading Ordnance Survey Open Data into SQL Server 2008

  1. Pingback: Tweets that mention New #SQL spatial blogpost - Loading Ordnance Survey open data into #SqlServer - -- Topsy.com

  2. Pure Krome says:

    Hi Alistair,

    two things.

    1) it’s possible to to use ogr2ogr to also CONVERT from 27700 to 4326?
    2) I noticed you imported the data as GEOM. Is it a simple process to convert all geom to geog AFTER it’s imported, so if we were to do geog-math (distance between two points, etc) .. it then takes the globe into account?

    -J-

    • alastaira says:

      Hi Pure Krome, thanks for the comment.
      1.) Yes, if your source shapefile is in a different SRS than you want, you can add the -t_srs parameter in OGR2OGR transform it to a new SRID
      2.) I used geometry, because the coordinate reference system of the ordnance survey shapefiles is 27700, which is a projected spatial reference system (coordinates are X,Y), and that’s the format I want in my DB. If I wanted to have geography (lat/long) data in 4326 instead, then I could either use OGR2OGR to create a new shapefile and import it straight into a geography column, or else you have to use a SQLCLR function within SQL Server to do the necessary math conversions between the coordinates – there’s a couple of .NET libraries that you can use as a basis to create a SQLCLR reprojection function in SQL Server – e.g. http://code.google.com/p/cumberland/ , but that’s the topic of another post… :)

  3. Pure Krome says:

    Hi Alastair,

    maybe this is for another blog post, but

    1) How did you know that 27700 is an x,y / projected system .. compared to 4326 which is a lat/long system?
    2) Why would using geom’s for spatial stuff instead of geog. I know i keep asking this and i’m sure you and everyone keeps answering it .. but i still don’t always get it. Is it because u’re not doing any spatial math -> ie. closest POI’s to a centre point, distance between two points, the STIntersection(..).STArea() of two shapes … which are all polys that represent places in the world?

    I keep thinking, if all it comes down to is plotting shapes over maps, then geom should be fine. Is that thinking, more-or-less .. correct?

    *blush*

    -J-

    • alastaira says:

      1.) Well, for one, I got taught about the OS National Grid in geography lessons at school, so you kinda know it’s a projected map :) Secondly, if you check http://www.spatialreference.org/ref/epsg/27700/html/ you’ll see that the WKT of this spatial reference system begins with PROJCS – i.e. PROJected Coordinate System.
      2.) I *am* doing spatial operations on my geometry data – intersections, distances, lengths etc. is all good with the geometry datatype. The difference is that if you use the geometry datatype it’s like doing calculations on a map, whereas the geography datatype is like doing calculations on a globe. Since the National Grid is a projected map, the geometry datatype is correct in this situation. If I’d been loading a shapefile of WGS84 data, I’d have used the geography datatype instead.

  4. Pingback: SQLBits 8 | Alastair Aitchison

  5. Charles Grayson says:

    Alistair,

    Check out: http://geographika.co.uk/ogr-comes-to-sql-server-2008-spatial#more-699

    A driver has been made available that adds MSSQLSpatial as an read / write format for
    ogr. Could be a neat way to export shape files from SQL server 2008.

    Charles Grayson

    • alastaira says:

      Hey, thanks for the comment, Charles – I hadn’t seen the MSSQL driver because I was using the latest FWTools release (2.4.7), which is still compiled from the GDAL 1.7 source – I’ll definitely give it a go now though!

  6. osedok says:

    Reblogged this on osedok.

  7. MSK says:

    Hi, Thanks for your article, I am new to this Spatial data and SSRS 2008, I got couple of queries while designing a SSRS report, Basically I want to show a drilldown map images in SSRS report and highlight the postcode areas in different color. I just need the boundary and don’t want to show river/roads…etc.

    As you said, I have downloaded the Meridian2 from Ordnance Survey for UK which contains lot .shp files , I have imported district_region.shp, county_region.shp into SQL2008 using the tool “ShapetoSQL”. now can you please clarify my questions ?
    Now I have two tables in my SQL 2008 server, (district_region & county_region)

    1. Do we need to import all the .shp files to have the full map
    2. How to view the geom data in the SQL Server query window or SSRS report from two tables , (e.g. select * from river shows the river map, and select * from roads shows the road map, how to view both of them together, what is the exact SQL)
    3. Can we change the color of the map depends on my business conditions. I need to show each region in a specific color in the district map (don’t want the default color given by SQL)
    4. Can we highlight each point(postcode) different color based on the my conditions.

    Thanks
    MSK

    • alastaira says:

      1. No, you don’t need to import the data into SQL Server – SSRS can display geometry data direct from shapefiles
      2. You need to add two or more layers to the map, with each layer using a different datasource
      3. Yes – you can add colour rules based on the value of any data field.
      4. Yes – colour rules can be applied to polygons, polylines, or points.

      See http://msdn.microsoft.com/en-us/library/ee240825.aspx or read the relevant chapter in “Pro Spatial with SQL Server 2012″

  8. MSK says:

    Thanks Alastaira.. sorry to bother, some more questions.

    County_region and District_region , the shapefiles which I imported into SQL server dont have any relationship , in the databsase table I am not able to find any link column(parent-child) which is required to drilldown from one map to another, (I know how to configure in SSRS) but not sure how to get the maps with the relationship or the tool “Shape2SQL” would have missed anything? or do I have to use any other maps from Ordnance Survey.

  9. alastaira says:

    @MSK – if there’s no attribute field they have in common, then you can explicitly create one by adding an ID field to one table that’s populated something like (SELECT ID FROM OtherTable WHERE OtherShape.STIntersects(ThisShape) = 1;)

  10. Pingback: Factivism | Voice of the DBA

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