1

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?

1

If you make your point as a Geometry instead of a Geography it will work. Geography objects must be on round earth and the UK is flat :-)

6

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).

  • I tried this on a view that contained live eastings and northings from a database and got a Geometry column, worked a treat. Thanks for this John. – PTrodd Feb 18 '15 at 10:23

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.