SQL Server 2016 How can I map latitude and longitude to an address matching on latitude and longitude if the coordinates vary slightly?

Question

I have 2 latitude and longitude set that are very close in proximity. How can I have SQL Server 2016 consider them the same address?

35.555925   -97.677617
35.55595    -97.677454

Answer 1

You need to define some limit for the maximum acceptable distance between two points for them to be considered the "same adress". And then the functions or formulas to calculate that distance. Take a look at Calculating distance between two points (Latitude, Longitude)

Answer 2

In the example below, we have 3 records. Two of them are approx 15 meters away, while the third is much further away.

Here we compare the distance of each record to every other, and if less than 25 meters apart, we can consider them to be the same location. (Clearly you can set your own tolerance)

You can econimize this a bit more. Keep in mind that every "1" of Lat or Lng is approx 69 miles, so the JOIN condition could be futher qualified.

Example

Declare @YourTable table ([PosNr] int,Lat float,Lng float,GeoPoint Geography)
Insert Into @YourTable values
 (1,35.555925, -97.677617,null)
,(2,35.55595,  -97.677454,null)  
,(3,38.55595,  -97.677454,null)  

Update @YourTable Set GeoPoint = GEOGRAPHY::Point([Lat], [Lng], 4326)

Select Distinct
       Pos1     = case when Pos1<Pos2 then Pos1 Else Pos2 end
      ,Pos2     = case when Pos1<Pos2 then Pos2 Else Pos1 end
      ,Meters
 From (
        Select Pos1   = A.[PosNr]
              ,Pos2   = B.[PosNr]
              ,Meters = A.GeoPoint.STDistance(B.GeoPoint) 
         From  @YourTable A
         Join  @YourTable B on A.PosNr<>B.PosNr
       ) A
 Where Meters<=25

Returns

Pos1    Pos2    Meters
1       2       15.0366793848766