GeoPandas: Get distance from geom in tableA to a geom in tableB that matches attribute from tableA

Question

UPDATE WITH SOLUTION (at bottom)

Using GeoPandas I am trying to identify the distance between an election candidate's home address (Point) and the electoral district they are running in (Polygon). Often they don't live in their district and so I'm trying to calculate how far each candidate lives from their riding as a crow flies. (I am unsure at this point if GeoPanda's distance method calculates to the nearest vertex/node or the centroid when using a polygon, but at this point its not important)

I have a DISTRICT GeoDataFrame which contains the electoral district boundaries:

id  ed_name     geom
0   MapleRidge  POLYGON(599240.6488817427....)
1   St.Johns    POLYGON(589240.6488427823....)
2   Southgate   POLYGON(563405.6488424563....)
3   etc....     etc...

And my second GeoDataFrame -- CANDIDATES -- contains details about the candidates, including the POINT geom of their home address and the district they are running in.

id  candidate  ed_running_in   geom
0   John       MapleRidge      POINT (523640.6482456427....)
1   Steve      Southgate       POINT(659240.6588817427....)
2   Shelly     St.Johns        POINT(879240.6488817427....)
3   Irene      MapleRidge      POINT(129240.6288817427....)
4   Patrick    MapleRidge      POINT(659240.1688817427....)
5   Ian        Southgate       POINT(929240.9888817427....)
6   etc...     etc...          etc...

(Note that these are fictional tables for illustrative purposes, so the geoms are bogus.)

GeoPandas has the following method: GeoSeries.distance(other).

I want to add a columns to the CANDIDATE table called "Distance" that shows how far the candidate lives from the riding they are running in. The Distance method in geopandas calculates element wise which is not useful here. I need the distance method to choose the correct district to measure against candidate home based on the "ed_running_in" value from the CANDIDATE table.

In PostGIS this is easy by using something to the effect of:

WHERE candidate.ed_running_in = district.ed_name

Below is some invalid code (loc doesn't allow you to reference another df) that illustrates what I'm mechanically trying to accomplish after setting the "ed_name" field as the index for the DISTRICT table:

candidates['distance'] = candidates['geom'].distance(district.loc[candidates['ed_running_in']])

UPDATE AND SOLUTION:
The key is to build a temporary bridge table, which merges over the geometry from the DISTRICT GeoDataFrame (i.e. the boundary file) to a cloned CANDIDATES table. Because the distance method is comparing two geometries pairwise you must create a bridge table of equal length and in matching row order.

So you have to clone the CANDIDATES table (say: CANDIDATES_TEMP) and then merge the geometry from districts over to it based on a match with "ed_running_in" == "ed_name". But the key thing (which took me a while to figure out), is that you must first drop the geometry column (the home addresses point) from the from cloned table, because GeoPandas will not accept two geometry values in a single element -- if you do not do this, it will convert your geometries to a string and therefore also converts the GeoDataFrame to a simple DataFrame. That is really the core Eureka moment here.

From here, you simply have to use the GeoSeries.distance(other) method to get the distance between the POINTS geometry from the CANDIDATE table to the POLYGON geometry from the cloned CANDIDATES_TEMP table.

PS. And in case you're wondering about the behaviour of the distance method when measuring a POINT against a POLYGON: the calculation is based on the nearest vertex, and not the centroid.

Answer 1

I think the problem here is that you are probably doing a many to one query and so pandas is not automatically looking for the first match for each of the rows.

Here I sort the values in the district by the ed_name column

district.sort_values("ed_name",inplace=True)

Then I find all the matches and their index values:

indexes = district.ed_name.searchsorted(candidate['ed_running_in'])

Then use the index positions to pull out the geometry objects in the correct order to match the candidate dataframe.

candidate['distances'] = candidate['geometry'].distance(district.loc[indexes]['geometry'])

I didn't test this thoroughly, but hopefully it will work for you. Or something along these lines.