Friday, 31 March 2017

spatial database - STDistance Unit in SQL Server 2008


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



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


No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...