STDistance Unit in SQL Server 2008

Question

I've got a SPROC in SQL Server 2008 where I'm trying to calculate the distance between a point and a polygon, and I'm getting unexpected results. I am passing a parameter (@Radius) in miles to the SPROC, but the distances don't seem to be in KM. I can't figure out what they are in. My SPROC is below:

DECLARE  @Point geometry;
SET   @Point = geometry::STGeomFromText('POINT(' + CAST(@Longitude AS nvarchar(32)) + ' ' + CAST(@Latitude AS nvarchar(32)) +')', 4326);

SELECT  [FS_Geometries].[Description], ([Geometry].MakeValid()).STDistance(@Point)
FROM  [FS_Geometries]
WHERE  (([Geometry].MakeValid()).STDistance(@Point) <= (@Radius * 1609.344))
AND   [ParentId] = @CustomerId
AND   [ParentType] = 80
ORDER BY (([Geometry].MakeValid()).STDistance(@Point)) ASC

In all my other queries, I need to convert my radius from miles to meters. When I do that here, the results are not correct. I have a result set of about 750 polygons that are separated by about 25 miles of geography, so when I pass "1.0", I'd expect to get less than my 750 polygons. Unfortunately, I get the full 750 in the results.

Is the STDistance() method in SQL Server 2008 based on something other than meters? Also, is the STDistance() method in this case measuring from the edge of the polygon or the centroid of the polygon?

Thanks!

UPDATE ---

My updated conversion function...

public static double ConvertMilesToDegrees(double miles, bool isLatitude)
{
    return isLatitude
        ? miles * (((double)1) / 69)  // 1 Deg. Latitude = 69 Miles...
        : miles * (((double)1) / 61); // Avg of 69 and 53...

    // NOTES: A degree of longitude is widest at the equator at 69.172 miles (111.321) 
    // and gradually shrinks to zero at the poles. At 40° north or south the distance 
    // between a degree of longitude is 53 miles (85 km).
}

Answer 1

I guess it works the same as PostGIS. If you are working in geometry type with geometry functions, the function just calculate with the unit the map has. In your case it seems to be lon lat degrees. Then your distance will not make much sense because the lat and lon degrees is of different length except on the equator.

So, what you have to do is transform your data to some meter based projection or use the geography type with geodetic functions.

HTH

Nicklas

Answer 2

Jason,

Steve is right. If you are going to be keeping your data in long lat, you really need to use the geography type. The planar calculation as you get further and further away will become more wrong. Plus you really aren't taking full advantage of what SQL 2008 spatial has to offer in terms of speed and indexing etc by querying that way.

As Nicklas pointed out, you'd have to transform your geometries to a planar coordinate if you want to use geometry and get more accurate and speedier answers, but unfortunately SQL Server 2008 doesn't have transform (reprojection) support that PostGIS offers to make that process easy, so I would just use geography type unless you have a compelling reason not to.

Answer 3

quoting Steven Hemingray on msdn forum :

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 non-flat earth.