Answered by:
STDistance Semantics
Question

I've run into an odd case and I'm hoping you can help. Consider the following:
 Center point of ZipCode 97223
declare @p1 geometry = geometry:TPointFromText('POINT (45.434899488464 122.782945968211)',4326)
 Center point of ZipCode 01801
declare @p2 geometry = geometry:TPointFromText('POINT (42.4745389446616 71.1530022136867)',4326)
select @p1.STDistance(@p2)The result is 51.7147447701779. But 51.7147447701779 whats? sys.spatial_reference_systems tells me the UOM is metres, but it seems like its a heck of a lot more than 52 metres from Beaverton to Boston.
How do I interpret this value?
Friday, December 28, 2007 4:09 PMAnswerer
Answers

For the geometry type, the linear units returned by methods such as Distance, Length, and Area are always in the unit coordinates of the points. Since this is latitude longitude data, the unit will be decimal degrees. However, it's pretty clear that performing this type of calculation in the planar system used by the geometry type doesn't make much sense when you really want a distance on the nonflat earth.
The srids listed in sys.spatial_reference_systems refer to the geography type only. If you substituted geography for geometry in your example, you would indeed get the correct distance (on the ellipsoid defined by WGS84) in meters between Beaverton and Boston.
Friday, December 28, 2007 10:31 PM
All replies

The emoticons aren't actual... grrr, bad parsing engine!
I've run into an odd case and I'm hoping you can help. Consider the following:
 Center point of ZipCode 97223 declare @p1 geometry = geometry:: STPointFromText('POINT (45.434899488464 122.782945968211)',4326)  Center point of ZipCode 01801 declare @p2 geometry = geometry:: STPointFromText('POINT (42.4745389446616 71.1530022136867)',4326) select @p1.STDistance(@p2)
The result is 51.7147447701779. But 51.7147447701779 whats? sys.spatial_reference_systems tells me the UOM is metres, but it seems like its a heck of a lot more than 52 metres from Beaverton to Boston.
How do I interpret this value?
Friday, December 28, 2007 4:13 PMAnswerer 
For the geometry type, the linear units returned by methods such as Distance, Length, and Area are always in the unit coordinates of the points. Since this is latitude longitude data, the unit will be decimal degrees. However, it's pretty clear that performing this type of calculation in the planar system used by the geometry type doesn't make much sense when you really want a distance on the nonflat earth.
The srids listed in sys.spatial_reference_systems refer to the geography type only. If you substituted geography for geometry in your example, you would indeed get the correct distance (on the ellipsoid defined by WGS84) in meters between Beaverton and Boston.
Friday, December 28, 2007 10:31 PM 
Thanks Steven, and yes, I knew that Geography would work right, but BOL doesn't really make it clear what STDistance returns, that might be something to take up with AlanB's crew.
Friday, December 28, 2007 10:46 PMAnswerer