How to return a result with a given radius

Question

I have the following SQL (SQL Server) and it works for the most part. The problem is that I'm really creating a square, not a real circle. My goal is to go through a city and state that has lat and long, and then find all the cities within a radius of 100 miles from this lat. Latitude and longitude are stored in the database, so all my values ​​are. I just need a more accurate way to do this. Here is my code:

ALTER PROCEDURE [dbo].[sp_StoresByZipArea] (@zip nvarchar(5), @Radius float) AS DECLARE @LatRange float DECLARE @LongRange float DECLARE @LowLatitude float DECLARE @HighLatitude float DECLARE @LowLongitude float DECLARE @HighLongitude float DECLARE @istartlat float DECLARE @istartlong float SELECT @iStartlat=Latitude, @iStartLong=Longitude from zipcodes where zipcode=@ZIP SELECT @LatRange = @Radius / ((6076 / 5280) * 60) SELECT @LongRange = @Radius / (((cos((@iStartLat * 3.141592653589 / 180)) * 6076.) / 5280.) * 60) SELECT @LowLatitude = @istartlat - @LatRange SELECT @HighLatitude = @istartlat + @LatRange SELECT @LowLongitude = @istartlong - @LongRange SELECT @HighLongitude = @istartlong + @LongRange /** Now you can create a SQL statement which limits the recordset of cities in this manner: **/ SELECT * FROM ZipCodes WHERE (Latitude <= @HighLatitude) AND (Latitude >= @LowLatitude) AND (Longitude >= @LowLongitude) AND (Longitude <= @HighLongitude) 

Answer 1

I used a great lap distance to do this in the past. Below is information about the distance between two different points that you can use to accomplish what you are talking about:

 create function dbo.GreatCircleDistance ( @Latitude1 float, @Longitude1 float, @Latitude2 float, @Longitude2 float ) returns float as /* FUNCTION: dbo.GreatCircleDistance Computes the Great Circle distance in kilometers between two points on the Earth using the Haversine formula distance calculation. Input Parameters: @Longitude1 - Longitude in degrees of point 1 @Latitude1 - Latitude in degrees of point 1 @Longitude2 - Longitude in degrees of point 2 @Latitude2 - Latitude in degrees of point 2 */ begin declare @radius float declare @lon1 float declare @lon2 float declare @lat1 float declare @lat2 float declare @a float declare @distance float -- Sets average radius of Earth in Kilometers set @radius = 6371.0E -- Convert degrees to radians set @lon1 = radians( @Longitude1 ) set @lon2 = radians( @Longitude2 ) set @lat1 = radians( @Latitude1 ) set @lat2 = radians( @Latitude2 ) set @a = sqrt(square(sin((@ lat2-@lat1 )/2.0E)) + (cos(@lat1) * cos(@lat2) * square(sin((@ lon2-@lon1 )/2.0E))) ) set @distance = @radius * ( 2.0E *asin(case when 1.0E < @a then 1.0E else @a end )) return @distance end 

Answer 2

Not sure if this helps, but I think there is an error here:

 SELECT @LatRange = @Radius / ((6076 / 5280) * 60) 

The part (6076/5280) will always evaluate to 1.

Answer 3

This functionality is available in a box for SQL Server 2012 and higher. See Request spatial data for nearest neighbor :

 DECLARE @g geography; DECLARE @h geography; -- SRID 4326 specifies the use of WGS 84 coordinate system (same as GPS) SET @g = geography::STGeomFromText('POINT(-122.360 47.656)', 4326); SET @h = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326); -- Returns 995 meters SELECT @g.STDistance(@h);