title: How to group nearby latitude and longitude and assign a Name/Number to that group in SQL Server?
body: <p>I have a table with <code>ID</code>, <code>LATITUDE</code>, <code>LONGITUDE</code>, <code>COUNTRY_CD</code> and I am trying to group (clustering) <code>ID</code> together within 40 meters and assign a Name/Number to that group. Ex. there are 7 ID from below records are within 40 meters distance and need to assign a Name/Number.  </p>
  
  <p>My table has 100 K records of latitude, longitude around the world and there will be more than 100 clusters from a country and I do not know how many clusters will be there in each country. </p>
  
  <p>I am fine to picking up chains of nearby points Ex, ID1 and ID3 are both 'close' to ID6 (but not each other).</p>
  
  <pre><code>create table #temp
  (
      ID varchar(10),
      LATITUDE [decimal](11, 8),
      LONGITUDE [decimal](11, 8),
      COUNTRY_CD [char](2)
  )
  
  insert into #temp select 'ID1', 10.81583689, 78.61898689, 'IN'
  insert into #temp select 'ID2', 10.81513789, 78.61898789, 'IN'
  insert into #temp select 'ID3', 10.81514889, 78.61894889, 'IN'
  insert into #temp select 'ID4', 10.81523989, 78.61898989, 'IN'
  insert into #temp select 'ID5', 10.81521089, 78.61891089, 'IN'
  insert into #temp select 'ID6', 10.81551189, 78.61891189, 'IN'
  insert into #temp select 'ID7', 10.81551189, 78.61791189, 'IN'
  insert into #temp select 'ID8', 10.81561189, 78.61792189, 'IN'
  insert into #temp select 'ID9', 10.81571189, 78.61793189, 'IN'
  
  select                    
      t1.ID, t2.ID,
      t1.LATITUDE, 
      t1.LONGITUDE,
      t1.COUNTRY_CD,
      --calculate the distance in meters
      cast(6378137.0 * sqrt(power((radians(t1.LATITUDE) - radians(t2.LATITUDE)), 2) 
           + power((radians(t1.LONGITUDE) - radians(t2.LONGITUDE)) * cos(radians(t1.LATITUDE)), 2)) as integer) as MAPPING_DISTANCE,
      (row_number() over (partition by t1.ID order by
                --rank the distance in meters
                cast(6378137.0*sqrt(power((radians(t1.LATITUDE)-radians(t2.LATITUDE)),2) 
                + power((radians(t1.LONGITUDE)-radians(t2.LONGITUDE))*cos(radians(t1.LATITUDE)),2)) as integer) asc
                )) as DISTANCE_RANK
  from  
      (select 
           ID, LATITUDE, LONGITUDE, COUNTRY_CD
       from   
           #temp) t1
  --join the above list of ID to get near by ID
  inner join 
       (select
            ID, LATITUDE, LONGITUDE, COUNTRY_CD
        from  
            #temp) t2 on t1.COUNTRY_CD = t2.COUNTRY_CD
                      --this brings ID available in 75 meters radius
                      and (t2.LATITUDE between (t1.LATITUDE - 0.00056) and (t1.LATITUDE + 0.00056))
                      and (t2.LONGITUDE between (t1.LONGITUDE - 0.00076) and (t1.LONGITUDE + 0.00076))    
                      --distance between t1 co-ordinates and t2 co-ordinates in meters
                      and (cast(6378137.0*sqrt(power((radians(t1.LATITUDE)-radians(t2.LATITUDE)),2) + power((radians(t1.LONGITUDE)-radians(t2.LONGITUDE))*cos(radians(t1.LATITUDE)),2)) as integer)) &lt;= 40 --limit to 40 meters
                      and t1.ID != t2.ID     --exclude the same ID 
  </code></pre>
  
  <p>The above query is bringing ID that are within 40 meters but I do not know how do filter the ID those are in a cluster?. Ex, 'Cluster_1' ?</p>
  
  <p>Refer this image <a href="https://i.stack.imgur.com/rBMrp.jpg" rel="nofollow noreferrer">2 clusters from above 9 ID</a></p>
  
  <p>Note that, I will not give any specific coordinate as input but the query has to pick ID within distance automatically from available coordinates in the table.</p>
  
  <p>My expected result as below,</p>
  
  <pre><code>ID  LATITUDE    LONGITUDE   COUNTRY_CD  CLUSTER_NAME
  ID1 10.81583689 78.61898689 IN  Cluster_1
  ID2 10.81513789 78.61898789 IN  Cluster_1
  ID3 10.81514889 78.61894889 IN  Cluster_1
  ID4 10.81523989 78.61898989 IN  Cluster_1
  ID5 10.81521089 78.61891089 IN  Cluster_1
  ID6 10.81551189 78.61891189 IN  Cluster_1
  ID7 10.81551189 78.61791189 IN  Cluster_2
  ID8 10.81561189 78.61792189 IN  Cluster_2
  ID9 10.81571189 78.61793189 IN  Cluster_2
  </code></pre>
  
  <p>Any suggestions how to filter ID that are in a cluster ? If there is any other simple way to do this would be great!</p>