What is SRID 0 for geometry columns?

Question

So I added geometry columns to a spatial table and using some of the msdn references I ended up specifying the SRID as 0 like so:

  update dbo.[geopoint] set GeomPoint = geometry::Point([Longitude], [Latitude], 0) 

However, I believe this was a mistake, but before having to update the column, is 0 actually the default = 4326? The query works as long as I specify the SRID as 0 on the query, but I'm getting weird results in comparison to the geography field I have... SRID 0 does not exist in sys.spatial_reference_systems and I haven't been able to dig up any information on it. Any help would be appreciated.

Answer 1

A SRID of 0 doesn't technically exist, it just means no SRID -- ie, the default if you forget to set it. So, technically, you can still perform distance, intersection and all other queries, so long as both sets of geometries have a SRID of 0. If you have one field of geometries with a SRID of 0 and another set with a SRID that actually exists, you will most likely get very strange results. I remember scratching my head once when not getting any results from a spatial query in exactly this situation and SQL Server did not complain, just 0 results (for what is is worth Postgis will actually fail, with a warning about non-matching SRIDs).

In my opinion, you should always explicitly set the SRID of your geometries (or geographies, which naturally will always be 4326), as not only does it prevent strange query results, but it means you can convert from one coordinate system to another. Being able to convert on the fly from lat/lon (4326), to Spherical Mercator (3857), as used in Google Maps/Bing, which is in meters, or some local coordinate system, such as 27700, British National Grid, also in meters, can be very useful. SQL Server does not to my knowledge support conversion from one SRID to another, but as spatial types are essentially CLR types, there are .NET libraries available should you ever need to do so, see Transform/ Project a geometry from one SRID to another for an example.

If you do decide to change you geometries, you can do something like:

UPDATE your_table SET newGeom = geometry::STGeomFromWKB(oldGeom.STAsBinary(), SRID);

which will create a new column or to do it in place:

UPDATE geom SET geom.STSrid=4326;

where 4326 is just an example SRID.

There is a good reference for SRIDs at http://spatialreference.org/, though this is essentially the same information as you find in sys.spatial_reference_systems.