DEV Community

KISHAN RAMLAKHAN NISHAD
KISHAN RAMLAKHAN NISHAD

Posted on

he distance between two geographical points SQL

CREATE FUNCTION [dbo].[Distance] 
(
    @lat1 FLOAT, 
    @long1 FLOAT, 
    @lat2 FLOAT, 
    @long2 FLOAT
) 
RETURNS FLOAT
AS
BEGIN
    DECLARE @DegToRad FLOAT = 57.29577951;
    DECLARE @Ans FLOAT = 0;
    DECLARE @Miles FLOAT = 0;

    -- If any input is null or 0, return 0 distance
    IF @lat1 IS NULL OR @lat1 = 0 
        OR @long1 IS NULL OR @long1 = 0 
        OR @lat2 IS NULL OR @lat2 = 0 
        OR @long2 IS NULL OR @long2 = 0
    BEGIN
        RETURN 0; -- No distance when coordinates are invalid
    END

    -- Calculate the Haversine distance
    SET @Ans = SIN(@lat1 / @DegToRad) * SIN(@lat2 / @DegToRad) 
             + COS(@lat1 / @DegToRad) * COS(@lat2 / @DegToRad) * COS(ABS(@long2 - @long1) / @DegToRad);

    -- Compute distance in miles
    SET @Miles = 3959 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans);

    -- Convert miles to kilometers and then to meters
    RETURN @Miles * 1.60934 * 1000;
END
Enter fullscreen mode Exit fullscreen mode

Top comments (0)