Selecting geographical points within area

问题:

I have a SQL Server 2008 table with a column of the geography datatype. The value is a point (latitude and longitude).

How do I query the table to return all rows where the location is within a 10 kilometer radius of a given coordinate?

回答1:

You probably want the STDistance method: http://msdn.microsoft.com/en-us/library/bb933952.aspx

Or the STWithin method: http://msdn.microsoft.com/en-us/library/bb933991.aspx

回答2:

This query eventually solved my problem:

DECLARE @geoMyPoint geography
SET @geoMyPoint = geography::STGeomFromText('POINT(56.5667 9.0333)', 4326);

SELECT vchZipCode, nvcCity, vchLat, vchLong,
  (geography::STGeomFromText('POINT(' + vchLat + ' ' + vchLong + ')', 4326)).STDistance(@geoMyPoint) 
FROM MyTable