[GIS] Converting OSGB easting and northing to geometry on fly in SQL Server 2008 R2

sql server

I have a table in a database that contains an easting and a northing for broadband relay stations. I am trying to create a view in SQL Server 2008 R2 that I can use in QGIS and I want to create a geometry field on the fly from the easting and northing. An example is Easting 257306 and northing 142708 (stored in separate fields) converted to POINT(257306 142708) as a geometry.

I've used the following code:

geography::STGeomFromText('POINT(' + CONVERT(varchar(20), Easting) + ' ' +
   CONVERT(varchar(20), Northing) + ')', 27700) AS Geometry

but I get an error that states that the specified SRID must match one of the supported ones in sys.spatial_reference_systems and I've looked and 27700 does not appear in that table.

How do I create this on the fly geometry field in my view?

Best Answer

In SQL Server 2008 there is no reprojection from one coordinate system to another, which is probably what you were expecting to happen automatically, when you put 27700 inside the STGeomFromText function. EPSG:27700 is the British National Grid, which is a projected coordinate system, ie, it is in meters and on a Cartesian plane. The geography datatype, EPSG:4326, is in degrees, which is why your POINT(257306 142708) is failing, as it falls out of the (-180,180), (-90,90) range.

The solution, as @iant has rather amusingly put it, it to use a geometry datatype instead, ie, projected, not geographic. However, not because the UK is flat, but because 27700 is projected onto a plane using an ellipsoid (geoid) that fits the UK's shape, see British National Grid, if you really want to know any more about this.

geometry::STGeomFromText('POINT(' + CONVERT(varchar(20), Easting) + ' ' +
  CONVERT(varchar(20), Northing) + ')', 27700) AS Geometry

SQL Server insists on there being a SRID, even though you can't actually do anything useful with it, such as convert for one coordinate system to another. It can get really confusing if you accidentally mix geometries with different SRIDs as you will simply get no results (witout warning, but this is an aside).

Related Question