SRID, Shape2SQL and SQL Server 2008r2 query

Question

I'm using Shape2SQL to load the Ordinance Survey's Meridian 2 map into SQL Server 2008r2 (which is working very smoothly and painlessly).

According to this page (https://alastaira.wordpress.com/2011/02/16/loading-ordnance-survey-open-data-into-sql-server-2008/)

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.

I'd not heard of SRID before so haven't been touching that value when loading the SHP files. The roadnode.shp file is currently loading and I've noticed that SRID has been set at 4326.

Once the map files are all in SQL Server is it a simple matter of changing the value in the SRID (or whatever it is called) column to 27700, or will that alteration affect other data in the table?

Answer 1

Normally, no, you cannot just change the SRID value. SRID stands for Spatial Reference System Identifier and is a coded value describing what coordinate system the data's geometry is in.

Let's take the example of the two CRS (coordinate reference systems) you have. 4326 is WGS84, which is a geographic CRS with degrees for units and the WGS84 datum. 27700 is British National Grid, which is a projected CRS with meters for units and the OSGB_1936 datum. If you change the SRID value, all you change is the label of what the geometry values mean but not the values themselves. So now you have some value that's supposed to be degrees (0.01745329251994328) and you changed the label so it's being read as meters. Obviously you're going to run into scaling issues right away, and the different datums are a whole other issue.

For more details, see:

• What's the difference between a projection and a datum?
• Layers with same coordinate system should align/overlap but do not?

Depending on what you're working with/doing, you need to either leave the SRID field alone so that the software knows the CRS it's supposed to be in and can automatically reproject the data as needed (if it has that capability), or you need to reproject the data yourself so that not only does the SRID value change, but so do the geometry coordinate values as well. Note the distinction between Defining a projection (which changes the label only, as you currently propose) and actually (re)projecting the data, as discussed in that second linked question.

After reviewing the page you linked to, I get the impression that Shape2SQL can't read the CRS from data it takes up so in essence what you have done with your workflow is defined the data to the wrong projection (your inputs are 27700 and you let it set the CRS as 4236). And it doesn't look like Shape2SQL has any reprojection capabilities, so it wouldn't have changed the geometry. So in this specific instance, if it's been writing 4236 to a field in all the records, then yes, you can just change those values to 27700 to correct the loading error. Shape2SQL is mentioned in a few other questions here and it looks like you aren't the first to make the mistake.