How to Calculate Distance Between Coordinates in SQL Server

Question

Post reply

How to Calculate Distance Between Coordinates in SQL Server

Imran2629

SSC Rookie

Points: 43
More actions
May 8, 2026 at 12:00 am

#4782605

Comments posted to this topic are about the item How to Calculate Distance Between Coordinates in SQL Server

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

reece.watkins 16343 Valued Member Points: 72 More actions · Answer 1

For some reason, the article cuts off two lines into the code box for Step 4?

reece.watkins 16343 Valued Member Points: 72 More actions · Answer 2

Using the spherical law of cosines like you did is not really accurate on computers for small distances due to accuracy issues with small numerical differences in floating-point libraries. You really want to use the haversine function instead of the spherical law of cosines.

However, SQL Server already provides an even more accurate way to determine distance:

SELECT
    geography::Point(40.7128, -74.0060, 4326)
        .STDistance(geography::Point(34.0522, -118.2437, 4326)) / 1000.0 AS DistanceKm;

This also takes the oblate spheroidal nature of the Earth into account, which neither the haversine nor the spherical law of cosines does.

michel.droguettc Newbie Points: 2 More actions · Answer 3

I can't read Step 4, it is cut off

Attachments:

You must be logged in to view attached files.

paul_milligan01 SSC Enthusiast Points: 157 More actions · Answer 4

Here is the SQL for the CREATE FUNCTION that was truncated in step 4:

CREATE FUNCTION dbo.ConvertToDecimalDegrees
(
    @Input VARCHAR(50)
)
RETURNS FLOAT
AS
BEGIN
    DECLARE
        @Degrees FLOAT,
        @Minutes FLOAT,
        @Seconds FLOAT,
        @Result FLOAT;

    -- Replace symbols with spaces
    SET @Input = REPLACE(
                    REPLACE(
                        REPLACE(@Input, '°', ' '),
                        '''', ' '
                    ),
                    '"', ' '
                 );

    SELECT
        @Degrees = PARSENAME(REPLACE(@Input, ' ', '.'), 3),
        @Minutes = PARSENAME(REPLACE(@Input, ' ', '.'), 2),
        @Seconds = PARSENAME(REPLACE(@Input, ' ', '.'), 1);

    SET @Result =
        @Degrees
        + (@Minutes / 60)
        + (@Seconds / 3600);

    IF @Input LIKE '%S%' OR @Input LIKE '%W%'
        SET @Result = @Result * -1;

    RETURN @Result;
END;