1

I'm new to SQL Server's capabilities regarding the Geography data type and how it can be used to calculate distances between two points. However, with the help of a good YouTube video, I was able to quickly write a query to find all the rows in a Locations table that are within a defined radius of specific lat/long point.

DECLARE @Lat DECIMAL(12,9)
DECLARE @Long DECIMAL(12,9)
DECLARE @Miles INT
DECLARE @Meters FLOAT

-- Coordinates set to a reference point
SET @Lat = 29.761209
SET @Long = -95.383513
SET @Miles = 15
SET @Meters = @Miles * 1609.34

DECLARE @Orig GEOGRAPHY = GEOGRAPHY::Point(@Lat, @Long, 4326)

SELECT 
@Orig.STDistance(GEOGRAPHY::Point(latitude, longitude, 4326)) / 1609.34 As MilesDistance,
*
FROM Locations
WHERE @Orig.STDistance(GEOGRAPHY::Point(latitude, longitude, 4326)) <= @Meters
AND latitude IS NOT NULL AND longitude IS NOT NULL
ORDER BY 1

But I need to take this a step further now. Rather than comparing the Locations to a single reference point, I need to compare them to a set of multiple points. This will basically be a cartesian join returning distances for all combinations of my Location records and lat/long values that I pull from another table, which will presumably take the place of my @Orig variable that was created from a single point.

PongGod
  • 829
  • 1
  • 11
  • 19
  • Perhaps take a peek at https://stackoverflow.com/questions/45483254/calculate-distance-between-points-using-long-and-lat-in-sql-server/45483494#45483494 – John Cappelletti Jun 10 '20 at 17:45

1 Answers1

1

OK, so I was able to come up with an effective solution. I first created a SQL function:

ALTER FUNCTION [dbo].[MilesBetweenTwoPoints] 
(
    @LatA DECIMAL(10,6),
    @LongA DECIMAL(10,6),
    @LatB DECIMAL(10,6),
    @LongB DECIMAL(10,6)
)
RETURNS DECIMAL(10,6) AS 
BEGIN
    DECLARE @Miles DECIMAL(10,6)
    DECLARE @PointA GEOGRAPHY = GEOGRAPHY::Point(@LatA, @LongA, 4326)
    DECLARE @PointB GEOGRAPHY = GEOGRAPHY::Point(@LatB, @LongB, 4326)

    SELECT @Miles = @PointA.STDistance(@PointB) / 1609.34

    RETURN @Miles
END

And then I modified my query to look something like this:

SELECT L1.latitude, L1.longitude, L2.latitude, L2.longitude,
dbo.MilesBetweenTwoPoints(L1.latitude, L1.longitude, L2.latitude, L2.longitude) As DistanceMiles
FROM Locations L1 
INNER JOIN OtherLocations L2 ON 1=1
WHERE dbo.MilesBetweenTwoPoints(L1.latitude, L1.longitude, L2.latitude, L2.longitude) < @Miles
PongGod
  • 829
  • 1
  • 11
  • 19