4

I have two data frames both containing x,y GPS coordinates and I'm trying to determine if they are within a 1/4, 1/2 and 1 mile of each other. Like this:

# COORDS1
            DAY   LATITUDE  LONGITUDE
0        Friday  40.521504 -74.623842
1        Sunday  40.957955 -74.956354
2      Saturday  40.955194 -74.992973
3     Wednesday  40.983414 -74.783138
4        Friday  40.759895 -74.939406
5        Friday  40.632494 -74.896660
6      Thursday  40.786528 -74.738685
7       Tuesday  40.586635 -74.553266
8       Tuesday  40.596116 -74.690472
9       Tuesday  40.634075 -74.857825
10       Sunday  40.898246 -74.505033

# COORDS2
    LOCATION          LAT          LON
0        NaN  1000.000000  1000.000000
1  location1    40.999999   -74.999123
2  location2    40.555555   -74.666123
3  location3    40.777777   -74.777123


# Determine if coords2 are within a 1/4, 1/2 and 1 mile of coords1 :
            DAY   LATITUDE  LONGITUDE quart_mile half_mile   one_mile  five_mile
0     Wednesday  40.941523 -74.527043        NaN       NaN        NaN        NaN
1     Wednesday  40.936384 -74.575850        NaN       NaN        NaN        NaN
2       Tuesday  40.598387 -74.721645        NaN       NaN        NaN  location2
3        Monday  40.935171 -74.638239        NaN       NaN        NaN        NaN
4     Wednesday  40.931664 -74.896922        NaN       NaN        NaN        NaN
5       Tuesday  40.734261 -74.828369        NaN       NaN        NaN  location3
6       Tuesday  40.991758 -74.842690        NaN       NaN        NaN        NaN

Right now I'm estimating how many degrees lat/lon = 1/4 mile and then checking if the points are within <= to that distance. The problem is, a 1/4 mile is different for latitude than longitude so it's very inaccurate (incidentally it turns out to be an ellipse instead of a circle). How can I use vincenty or do point in polygon with 2 data frames to do this properly?

Here is my sample attempt that produces the above output.

from geopy.distance import vincenty
import pandas as pd
import numpy as np


# DF1
df = pd.DataFrame({ 'DAY': np.random.choice(['Monday','Tuesday','Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'], 10000),
                    'LATITUDE': np.random.uniform(low=40.5, high=41, size=10000),
                    'LONGITUDE': np.random.uniform(low=-74.5, high=-75, size=10000)})
coords = df[['LATITUDE', 'LONGITUDE']]
coords1 = coords.values[:, None].astype(float)


# DF2
data = [
    ['LOCATION', "LAT", "LON"],
    ['NaN', 1000, 1000],
    ['location1', 40.999999, -74.999123],
    ['location2', 40.555555, -74.666123],
    ['location3', 40.777777, -74.777123],
]
df_loc = pd.DataFrame(data[1:], columns=data[0])
coords = df_loc[['LAT', 'LON']]
coords2 = df_loc.values[None, :, 1:].astype(float)

#df_loc['lon_quart_mile_scalar'] = 1 / ((np.cos(np.radians(df_loc['LAT'])) * 69.172) / .25)
#df_loc['lat_quart_mile_scalar'] = (((np.abs(df_loc['LAT'] - 41)) * .00004139) + .00362289)
quart_mile_approx = .00362287
df['quart_mile'] = df_loc.LOCATION.iloc[(np.abs(coords1 - coords2) <= (quart_mile_approx)).all(2).argmax(1)].values
df['half_mile'] = df_loc.LOCATION.iloc[(np.abs(coords1 - coords2) <= (quart_mile_approx * 2)).all(2).argmax(1)].values
df['one_mile'] = df_loc.LOCATION.iloc[(np.abs(coords1 - coords2) <= (quart_mile_approx * 4)).all(2).argmax(1)].values
df['five_mile'] = df_loc.LOCATION.iloc[(np.abs(coords1 - coords2) <= (quart_mile_approx * 20)).all(2).argmax(1)].values

# How do I get this statement to work as a replacement for the above statements?
#df['TWO_MILE_TEST'] = df_loc.LOCATION.iloc[(vincenty(coords1, coords2).miles <= 2).all(2).argmax(1)].values

print(coords1)
print(coords2)

with pd.option_context('display.width', 1000, 'display.max_rows', 50):
    print(df)

How do I properly measure the distance between two sets of GPS coordinates using vincenty like the attempt below. Should I use df.apply?

df['TWO_MILE_TEST'] = df_loc.LOCATION.iloc[(vincenty(coords1, coords2).miles <= 2).all(2).argmax(1)].values

But it throws an error:

TypeError: __new__() takes from 1 to 4 positional arguments but 5 were given

geopy.distance.vincenty expects two parameters like this ((x,y), (x,y)).

Here are similar questions with useful snippets/attempts:

https://stackoverflow.com/questions/34621118/distance-calculation-in-geopy

https://stackoverflow.com/questions/30969282/how-to-use-geopy-vicenty-distance-over-dataframe-columns

https://stackoverflow.com/questions/48596213/grouped-by-dataframe-use-column-values-in-current-and-previous-row-in-function

1 Answer 1

4

You can do it like this:

  • Cross join the two dataframes using pd.merge. (The key field is just an auxiliary to be able to compute a full cross join.)

    df_all = pd.merge(df.assign(key=0), df_loc.assign(key=0), on='key').drop('key', axis=1)
    
  • Calculate the distance between each pair of points using apply with vincenty.

    df_all['MILES'] = df_all.apply(
        (lambda row: vincenty(
            (row['LATITUDE'], row['LONGITUDE']),
            (row['LAT'], row['LON'])
        ).miles),
        axis=1
    )
    

    This takes quite a while if you are to process more than a few thousand points; you might be interested in some less precise but faster methods of distance calculation such as the haversine formula - it is less precise, which probably does not matter if you are working on a mile scale, but is not iterative so can be computed using array calculations.

  • For each point from the first dataframe, select only the closest point from the second dataframe.

    closest = df_all.loc[df_all.groupby(["DAY", 'LATITUDE', 'LONGITUDE'])["MILES"].idxmin()]
    

    If you have a unique key for the points from the first dataframe, use it here as the groupby argument.

  • Merge the location names and distances back to the first dataframe.

    df_withlocs = df.merge(
        closest,
        on=["DAY", 'LATITUDE', 'LONGITUDE'],
        suffixes=('', '_cl')
    ).drop(['LAT', 'LON'], axis=1)
    
  • Generate the label columns for the individual distance threshold. I did it like this:

    DISTANCES = [
        (.25, 'quart_mile'),
        (5, 'five_mile'),
    ]
    
    for dist, column in DISTANCES:
        locs = df_withlocs['LOCATION'].copy()
        locs[df_withlocs['MILES'] > dist] = np.nan
        df[column] = locs
    

    Just add the distance thresholds you need. NaN will appear where no location is in the desired range.

1
  • Thanks I verified it works, now need sometime to digest what's going on :)
    – Calculus
    Mar 2, 2018 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.