Spatial Referencing Systems in the UK

  • Article

I have had a couple of queries about spatial data in SQL Server regarding SRIDs.  I have posted already on some of this here, and while I mentioned SRIDs I didn’t really cover what that means so hopefully this explains it...

First of all the term Spatial Reference Identifier (SRID) is a European Petroleum Survey Group (EPSG) standard and refers to how the spatial data is plotted on the earth, the most common way of doing this is SRID 4326 which is simply lattitude & longitude, plotted over a flattened sphere (or oblate spheroid to give its technical term) that mimics the surface of the earth.  This standard also equates to OpenGIS standard WGS84.  This is important for several reasons:

  • It’s supported in a number of databases so SQL Server (2008 & later) , Oracle, db2 , MySQL and PostGres.
  • It’s used to plot data onto Bing Maps and Google Earth, you might want to use Bing Maps if you need to plot onto Ordnance Survey maps for example.
  • It’s the standard used by all GPS systems

So why wouldn’t you use it if it’s so ubiquitous?  the answer might well be that you:

a. live in the UK

b. you want to work with the government data on Data.Gov.UK

The spatial data at Data Gov UK is based on a completely different system, the National Grid,not the one you plug your laptop into but the one any Scout or Guide knows which uses eastings and northings.  On this Ordnance survey view you can see the national grid numbers over the sea..

tHAMES mAP

If this map was plotted on the same projection as used for National Grid then the blue squares would be straight up and down and would be exact squares (it’s not apparent they aren’t so trust me on this)  The Ordnance Survey site explains this in more detail.  The National Grid equates to SRID 2770 and converting data in that from to the SRID 4326 is not trivial;

For example on the national grid Microsoft UK is located at SU747742, the SU representing a 100x 100km square and the numbers would be 74.7km east of the bottom left corner of that square and 74.2km north of it. That equates to Longitude 0.9258985519409051 West  Latitude 51.46122678211959 North.      

You can actually import this data into SQL Server 2008, but using the geometry type.  If you run a query in SQL you will see the shapes properly represented but rotated.  However you can’t then play with Bing Maps either with your own code or using reporting services as the Bing Maps layer won’t understand the references.

So you are probably going to need some conversion tools to play to convert from SRID 2770 into SRID 4326 and there is also a company called Safe Software who  have a tool,  FME to do exactly that in SQL Server Integration Services. 

One more thing you should be aware of is that will Bing Maps is only free when you are in consumer mode, or you are working for a not for profit or an educational establishment, and it does need to be licensed if you are using it commercially and details of this are here.  However this could well be a cheaper option than using dedicated GIS tools particularly if don’t need the richer toolset these traditional tools offer.