Geometry column: STGeomFromText and SRID (what is

Question

I'm playing with the new geography column in SQL Server 2008 and the STGeomFromText function. Here is my code (works with AdventureWorks2008)

DECLARE @region geography;
set @region = geography::STGeomFromText('POLYGON((
        -80.0 50.0, -90.0 50.0,
        -90.0 25.0, -80.0 25.0,
        -80.0 50.0))', 4326);

SELECT @region;

My question is about the 4326 in the code. It is supposed to be a spacial Reference ID. When I go to MSDN there isn't a lot on it. If I change the value to 56 I get an error telling me the value must be in the sys.spatial_reference_systems table.

You can look at that table by executing:

select * from sys.spatial_reference_systems 

There is a well_known_text column in that table, but it doesn't tell me much. The value for 4326 is:

GEOGCS["WGS 84", DATUM["World Geodetic System 1984", ELLIPSOID["WGS 84", 6378137, 298.257223563]], PRIMEM["Greenwich", 0], UNIT["Degree", 0.0174532925199433]]

Can anyone explain this mystery to me? What is the SRID?

Answer 1

I have found this website: http://spatialreference.org/ref/epsg/4326/ quite helpful in understanding the SRID you intend to use. It provides a handy map, some bounding box information and other links.

For other SRIDs simply change the digits at the end of the URL to what you are after.

Answer 2

The distance returned depends on the "Spatial Reference Identifier (SRID)" you define for your geography types.

In the example below, the default SRID of 4336 is used, see the second argument of STGeomFromText. This means the distance returned is in meters, you find this via querying the catalog view spatial_reference_systems i.e. select srs.unit_of_measure from sys.spatial_reference_systems as srs where srs.spatial_reference_id = 4326

As an alternative to STGeomFromText, you can use parse which assumes a SRID of 4326 and you don't have to specify one explicitly.

When calculating the distance between two points, you must use the same SRID for both geography types else you get an error. Example:

DECLARE @address1 GEOGRAPHY
DECLARE @address2 GEOGRAPHY
DECLARE @distance float
SET @address1 = GEOGRAPHY::STGeomFromText ('point(53.046908 -2.991673)',4326)
SET @address2 = GEOGRAPHY::STGeomFromText ('point(51.500152 -0.126236)',4326)
SET @distance = @address1.STDistance(@address2)
SELECT @distance --this is the distance in meters

Answer 3

SRID = Spatial Reference IDentifier

coordinates must use the same SRID to be comparable. otherwise you'd end up comparing kilometeres and miles. or something similar.

Answer 4

So I ended up talking with an ex-military guy yesterday who was a radar/mapping specialist. Basically, he knew exactly what that number (4326) was, where it came from, and why it is there.

It is an industry standard for computing geography. The problem is that the earth is not a perfect sphere (it bulges in the middle), and SRID 4326 accounts for that.

As I stated, the table sys.spatial_reference_systems lists all of the code and what they are. But the short version is that you are really only going to use 4326 unless you have a very specific reason to use something different.

Answer 5

There are a lot of systems to map the earth. For example you want to map some state in USA. You can set the most south-east point as 0,0 and map all other spatial coordinates according to this point. On the other hand you may want to map some spatial data that span all over the map. In any case you must choose some point as 0,0. In addition you must select some sort of measurement unit: miles/kilometers/degrees/some other magical unit that suits you better. Over the years a lot of such systems where developed. Each has its own zero point, its own coordinates, its own rules about if the earth is flat or not. SRID or SRS is the id of such system. Using this id you can map point expressed in one system to another system, although sometimes it involves some pretty complex math.

And about 4326 SRID. It also called "WGS 84" (http://en.wikipedia.org/wiki/World_Geodetic_System) system. It's the most common system to represent point on spherical(not flat) earth. It uses degree,minute,second notation and its x and y coordinates are usually called latitude and longitude.

Most used non-spherical earth projection is called UTM. You can read about it here: http://en.wikipedia.org/wiki/Universal_Transverse_Mercator_coordinate_system

Anyway, as long you are not doing any spatial conversions from one system to other, you don't really care about the system that you data uses.