I've set myself a project of getting some geo data into SQL Server but before I get started doing any of the big jobs I wanted to see if I was on the right track.

All of my data is for the UK and is therefore based on Easting & Northing coordinates (OSGB36), not Latitude & Longitude (WGS84).

My understanding is that I cannot create a geography field based on Eastings & Northings in SQL Server without first re-projecting in a GIS package (I have QGIS).

I can however convert Easting & Northings based data into a geometry field with no problems.

I have managed to re-project some point data to WGS84 in QGIS and create a geography field from this, however when I use the QGIS MSSQL link to bring the resulting point data into QGIS, it really doesn't seem to be able to marry it with layers in OSGB36 (despite having on-the-fly re-projection activated).

So my question is: Following the path of least resistance (and maximising compatibility with other UK datasets), is it acceptable to store OSGB36 data as geometry in SQL Server, despite actually being geography?

  • 1
    Least resistance - reproject your OSGB36 to WGS84 in MSSQL Server - osedok.wordpress.com/2012/01/17/… (Having been working with OS Data for 15 years - today 95% of clients want it in WGS84) – Mapperz Aug 23 '12 at 14:22
  • Question what format are you getting from Ordnance Survey? gml.gz? how are you converting that into SQL Import? – Mapperz Aug 23 '12 at 14:32
  • I'll have a read of that, thank you. Data is generally received as ZIP files containing either ESRI Shapefiles or CSV files (in which case I convert to shapefile in QGIS or geocode in SQL Server using the OS Code Point dataset). I'm also receiving data from ONS in the same format. I'm importing either using bog standard Import/Export wizard for CSVs which will then be geocoded. For shapefiles I'm using the infamous SQL Spatial Tools from Morten Nielsen (sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx). – Tumbledown Aug 23 '12 at 15:16
  • There is a real clanger in OS Code Point - BT* postcodes are in Irish National Grid - you need to reproject them separately. see blog.da.vidnicholson.com/2006/12/uk-postcode-coverage-map.html (image is missing these days) – Mapperz Aug 23 '12 at 15:53
  • I would reproject those shapefiles first in WGS84 and then import all into SQL Server. – Mapperz Aug 23 '12 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.