Correspondence between two tables

I have table the table code_Commune contains gps for the city in general code_Commune:

Code_commune           Nom_commune          coordonnees_gps
01263                MONTMERLE SUR SAONE             46.0849573236,4.7608579486

Now I need to do correspondence with the table lead to add code_commune and Nom_Commune:

Table lead

Id_lead     latitude     Longitude   
F439130C    46.00000000  6.39522000  
24A09223    46.00000000  6.39522000  
42899ABF    46.00000000  4.75212800  

Is there any way to do correspondence between the two tables?

Answer

You can find the closest city using some sort of distance measure. First, fix your first table:

alter table code_commune
    add latitude as (convert(decimal(14,10), 
                             left(coordonnees_gps, charindex(',', coordonnees_gps) - 1)
                            )
                    );

alter table code_commune
    add longitude as (convert(decimal(14,10), 
                              stuff(coordonnees_gps, 1, charindex(',', coordonnees_gps), '')
                             )
                     );

Then you can use apply, once you decide on a distance metric. The following uses Manhattan distance:

select l.*, cc.code_comune
from leads l outer apply
     (select top (1) cc.*
      from code_commune cc
      order by abs(cc.latitude - l.latitude) + abs(cc.longitude - l.longitude)
     );

This is highly inefficient. If you need efficiency, then you should be looking into SQL Server’s support for spatial data.