Ordnance Survey have been providing free to use mapping data and it’s great that this data is publicly available and regularly updated. As someone who works with SQL Server daily, I wanted to load this data into SQL Server, using the spatial types.
OS provide this data in the ESRI Shapefile format, which unfortunately is not supported by SQL Server out of the box. With a little bit of conversion, it’s still possible to load this data into SQL Server.
The first step is to register on the Ordnance Survey webiste so you can download the data. I am using the “Boundary Line” dataset, which shows all of the parish boundaries across the country.
In order to load this data you will need to download the following tools:
- Shape2SQL – http://www.sharpgis.net/page/Shape2SQL
- OGR2OGR – http://www.gdal.org/ogr2ogr.html (available as part of the FWTools suite – http://fwtools.loskot.net/)
The ESRI shapefile stores data in a way that SQL Server cannot interpret as a geographic shape. You can load it as a Geometry shape, but SQL Server will not be able to relate it to a latitude or longitude. To make this work, we will need to perform a conversion on the ESRI shapefile.
Step 1 – Convert SRID
Using the ogr2ogr console application (I’m using it through the FWTools command prompt) we will need to perform a data conversion from SRID 27700 (the format the OS provide the data in) to SRID 4326 (the standard geographic SRID).
To do this you will need to adapt the command below:
C:\Program Files (x86)\FWTools2.4.7>ogr2ogr -f "ESRI Shapefile" -s_srs "EPSG:27700" -t_srs "EPSG:4326" c:\parish_geo.shp c:\parish_region.shp
This command tells ogr2ogr to take an ESRI shapefile (using the -f parameter) from the source SRID (-s_srs) and convert it to the target SRID (-t_srs). This file opens the “parish_region.shp” and saves the result in “parish_geo.shp”.
Note: ogr2ogr will require that you keep the files associated to the .shp in the same folder, as these are used as part of the conversion process.
Run this command and check that your output file has been created.
Step 2 – Load the data into SQL Server
Once you have your converted file, you will need to use Shape2SQL to load the data into SQL Server. Note that if, like me, you are using SQL Server 2012 or later, you will need to copy the SqlSpatial.dll file into the same folder as the Shape2SQL executable (the Shape2SQL homepage contains instructions on how to do this).
Open Shape2SQL and select your converted file. Initially you will need to load your file as the Geometry type. I found that trying to directly load the file into a Geograhpy type caused errors with Shape2SQL.
A screenshot of Shape2SQL – note that I am uploading data into SQL Server using the Geometry type
You will want to ensure that the SRID is set to 4326. Set up your connection to your database and you are ready to click upload.
Once your data is uploaded, add a new column to your table that uses the geography type, rather than the geometry type that currently contains your data.
Step 3 – Convert your geometry data into the geography type
Finally, you can convert your geo data from geometry to geography using a statement like the below.
update [Geo_test].[dbo].[parish_geo] set geog = GEOGRAPHY::STGeomFromWKB(geom.STAsBinary(),4326).MakeValid()
Step 4 – Explore!
You now have the Ordnance Survey data stored in SQL Server, using the geography type.
Querying the boundary line dataset!
Links
I couldn’t have done this without help from the following sources: