Given two lat/longs, how can I tell if they are within 1 mile of each other?

Question

I'm trying to implement a very efficient check to see whether two points are within a mile of each other.

I only care whether they are within a mile - nothing else about the distance matters to me.

Because of that narrow focus, I am not looking for a general purpose "how far apart are these points" function.

My current approach is to compute the Haversine distance, and then check to see if it's less than a mile.

Efficiency matters in this case because I have to compute this yes/no flag for large record sets.

So, what is the most efficient way to tell whether two lat/long points are within a mile of each other?

I'm doing this check in T-SQL, not that it matters much. My current haversine computation is below.

CREATE FUNCTION dbo.USR_UFN_HAVERSINE_DISTANCE
(
  @LAT1 FLOAT(18)
 ,@LONG1 FLOAT(18)
 ,@LAT2 FLOAT(18)
 ,@LONG2 FLOAT(18)
 ,@UnitOfMeasure NVARCHAR(10) = 'KILOMETERS'
)
RETURNS FLOAT(18)
AS
BEGIN
  DECLARE
    @R FLOAT(8)
   ,@DLAT FLOAT(18)
   ,@DLON FLOAT(18)
   ,@A FLOAT(18)
   ,@C FLOAT(18)
   ,@D FLOAT(18)
   ;
  SET @R =
    CASE @UnitOfMeasure
      WHEN 'MILES'      THEN 3956.55 
      WHEN 'KILOMETERS' THEN 6367.45
      WHEN 'FEET'       THEN 20890584
      WHEN 'METERS'     THEN 6367450
      ELSE 6367.45  --km
    END
  SET @DLAT = RADIANS(@LAT2 - @LAT1);
  SET @DLON = RADIANS(@LONG2 - @LONG1);
  SET @A = SIN(@DLAT / 2) 
         * SIN(@DLAT / 2) 
         + COS(RADIANS(@LAT1))
         * COS(RADIANS(@LAT2)) 
         * SIN(@DLON / 2) 
         * SIN(@DLON / 2);
  SET @C = 2 * ASIN(MIN(SQRT(@A)));
  SET @D = @R * @C;
  RETURN @D;
END;

Answer 1

Since you specify that you need to run this over large data sets, I'd suggest a table-valued function. Better if you can pre-compute the geography points, but this does it all inline.

create function dbo.fn_areWithinOneMile(@long1 float, @lat1 float, @long2 float, @lat2 float)
returns table
as
return

    select cast(
        case when 
            geography::Point(@lat1, @long1, 4236).STDistance(geography::Point(@lat2, @long2, 4236)) > 1609.34 then 0 
            else 1 
        end as bit) as [withinOneMile?]

go

with cte as (select * from (values
    (42, 42),
    (43, 43),
    (44, 44)
    ) as x(lat, long)
), j as (
    select long, lat, lag(long, 1) over (order by lat) as long2, lag(lat, 1) over (order by lat) as lat2
    from cte
)
select *
from j
cross apply dbo.fn_areWithinOneMile(long, lat, long2, lat2) as o
where long2 is not null;