SQL Server units question

Question

This may be a really dumb question, but...

What units does Geography.STLength return? The official MSDN page doesn't say anything about the units returned, and this blog entry here says STLength() returns a float indicating the length of the instance in units. Yes, that's right, it says it returns it in units.

Can anyone shed some light on what units STLength returns? Feet? Meters? Inches? Help!

Answer 1

The units are entirely dependent on the Spatial Reference ID (SRID) of the geography/geometry data being used. By convention, you would generally use an SRID of "0" for geometry types if all the data is in the same unit system.

However, usually the geography type uses an SRID of 4326, which is the reference ID of the latitude/longitude ellipsoidal earth coordinate system known as WGS 84. When you specify point coordinates in this system, it is in degrees of angle of latitude and longitude, rather than some distance from an origin. Length and area calculations on points in this reference system will return completely different results from geometric calculations on the exact same point positions (for a great example see Differences between Geography and Geometry here, and as for why this happens, see here).

So if your data columns were created with an SRID of "0", then the system is defined to be unitless and you would need some metadata about the data model to figure out the units. If they were defined with a real SRID, then you can use this query:

SELECT spatial_reference_id
   , well_known_text
   , unit_of_measure
   , unit_conversion_factor
FROM sys.spatial_reference_systems

to check what units the SRID represents. Most are in metres, but a few are in feet.