STGeomFromText and Spatial Reference Identifier SRID - Spatial Data in Microsoft SQL Server 2008
No matter you are dealing with geometry Data Type or geography Data Type as a t-sql developer working with SQL Server 2008 spatial data, you can use OGC static STGeomFromText method to create a geometry instance or create a geography instance.
Syntax :
STGeomFromText ( 'geometry / geography tagged text' , SRID )
What is frequently not understood and missed is the SRID argument to the STGeomFromText method.
SRID is the spatial reference ID used to express the geometry data type instance or geography data type instance.
For example if you execute the followin t-sql script on a Microsoft SQL Server 2008 instance ;
CREATE TABLE SpatialTable (
id int IDENTITY (1,1),
geography1 geography,
geography2 AS geography1.STAsText()
);
GO
INSERT INTO SpatialTable (geography1)
VALUES (geography::STGeomFromText('POINT(10 10)'));
SQL Server 2008 Engine will throw the sql exception shown below :
Msg 174, Level 15, State 1, Line 1
The STGeomFromText function requires 2 argument(s).
After you have seen the spatial STGeomFromText syntax, it is obvious that the missing argument for the Spatial method STGeomFromText of the geography class is the SRID (spatial reference identifier).
If you run the above sql code using geometry data type instead of geography data type the result will not differ.
So the sql developer must supply a valid spatial reference identifier or SRID to the Spatial STGeomFromText function call.
One important point with the specified SRID is that spatial reference identifier must be one of the supported SRID 's by Microsoft SQL Server 2008.
The list of MS SQLServer 2008 supported spatial reference identifier 's can be listed by running a SELECT query on sys.spatial_reference_systems catalog view.
It is important that if you work with spatial data using two or more spatial data instances either geometry or geography, they must share the same SRID (spatial reference identifier).
Otherwise, spatial operations or spatial functions over geometry/geography instance with different SRID 's will result NULL values.
Valid Spatial Reference Identifier List (SRID's) Supported by Microsoft SQL Server 2008
The supported spatial reference identifiers can be listed by simply running a tsql SELECT query on system catalog sys.spatial_reference_systems
I have only listed some of the SRID's from the spatial_reference_systems system view using the below sql query.
select * from sys.spatial_reference_systems
where spatial_reference_id in (
4135,4157,4202,4241,4242,4243,4268,4293,4326,4748
)
As a t-sql developer working on MS SQL Server 2008 spatial data, if you deal with geography data type default SRID (Spatial Reference Identifiers) is 4326 :
GEOGCS["WGS 84", DATUM["World Geodetic System 1984", ELLIPSOID["WGS 84", 6378137, 298.257223563]], PRIMEM["Greenwich", 0], UNIT["Degree", 0.0174532925199433]]
So the correct form of the above SQL2008 t-sql command is as follows :
INSERT INTO SpatialTable (geography1)
VALUES (geography::STGeomFromText('POINT(10 10)',4326));
If you are working with geometry instances and geometry data type in your MS SQL Server 2008 spatial applications, you should note that the default spatial reference identifier (SRID) for geometry instances is 0.
For more information on Spatial Reference Identifiers (SRIDs), please refer to MSDN Documentation.