CODEWITHSUNDEEP
sql-serverlatitude-longitudesqlgeography
PongGod
PongGod

Reputation: 859

SQL Server Geography data type and distances between all combinations of points

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.

Upvotes: 1

Views: 1151

Answers (1)

PongGod
PongGod

Reputation: 859

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

Upvotes: 1

Related Questions