Find duplicates between two Pandas based on lat, long intersect

Question

I have two pandas DataFrames with Address, Lat and Long as columns. I'd like to find rows that are duplicates between the 2 dataframes based on the intersection of their coordinates. I'll define the logic for intersection as two points that within 50ft of each other.

import pandas as pd

df1 = pd.DataFrame({'addr': ['NBC Tower, 455 N Cityfront Plaza Dr #2700, Chicago, IL 60611, United States','340 E North Water St, Chicago, IL 60611, United States'],
                    'Lat': [41.890100, 41.889680],
                    'Lon': [-87.621150, -87.618790]
                   })

df1 = pd.DataFrame({'addr': ['455 N Cityfront Plaza Dr #2900, Chicago, IL 60611, United States','255 E Grand Ave, Chicago, IL 60611, United States'],
                    'Lat': [41.890100, 41.891392],
                    'Lon': [-87.621150, -87.621323] 
                   })

If the intersection between the coordinates is less than 50ft, then insert match, otherwise insert No match found.

Would prefer to have a function that find the intersection and use .apply. Something like below:

def find_geomatch(args...):

    if match:

      txt = 'match'
 
    else:
   
      txt = 'No match found'

    return txt

df1['geomatch'] = df1.apply(lambda x: geomatch(x), axis=1)

Answer 1

You can add geometry columns with units in meters (or feet), cross join the two data frames, and find the rows where distance is less than some threshold:

import pandas as pd
import geopandas as gpd

df1 = pd.DataFrame({'addr': ['NBC Tower, 455 N Cityfront Plaza Dr #2700, Chicago, IL 60611, United States','340 E North Water St, Chicago, IL 60611, United States'],
                    'Lat': [41.890100, 41.889680],
                    'Lon': [-87.621150, -87.618790],
                    'id' : [1,2]
                   })

df2 = pd.DataFrame({'addr': ['455 N Cityfront Plaza Dr #2900, Chicago, IL 60611, United States','255 E Grand Ave, Chicago, IL 60611, United States'],
                    'Lat': [41.890100, 41.891392],
                    'Lon': [-87.621150, -87.621323],
                    'id' : ['a','b']
                   })

gdf1 = gpd.GeoDataFrame(df1, geometry=gpd.points_from_xy(x=df1.Lon, y=df1.Lat)) #You should be able to set crs here, but I cant get it to work
gdf2 = gpd.GeoDataFrame(df2, geometry=gpd.points_from_xy(x=df2.Lon, y=df2.Lat))
gdf1.crs = "EPSG:4326"
gdf2.crs = "EPSG:4326"

gdf1['geometry_in_m'] = gdf1.geometry.to_crs("EPSG:26916") #Create an additional geometry column with crs in meters
gdf2['geometry_in_m'] = gdf2.geometry.to_crs("EPSG:26916")

#Cross join the dataframes https://stackoverflow.com/questions/34161978/pandas-two-dataframe-cross-join
gdf1['key']=0
df2['key']=0
gdf3 = gdf1.merge(gdf2, on='key', how='outer')

def givedist(row):
   """ Returns the distance between two geometries """
    return row['geometry_in_m_x'].distance(row['geometry_in_m_y'])

gdf3['distance'] = gdf3.apply(givedist, axis=1)

gdf3.loc[gdf3.distance<1][['id_x','id_y']] #Select the rows where distance is less than 1 m

#   id_x id_y
#0     1    a

#So id 1 in gdf1 is a duplicate to "a" in gdf2