GeoPandas Spatial Join Returns Duplicate Rows: How to Fix It
On this page
Problem statement
A common surprise with geopandas.sjoin() is that the result has more rows than the left layer, or that the same feature appears several times. On the data used here β the 531 points of interest in OpenStreetMap for Rutland, joined to the county's 2,405 land-use polygons β the join returns 569 rows: 38 points matched two polygons each.
Every extra row is a real match β a village green mapped inside a residential area is both β so nothing is broken; the fix is deciding what one output row should mean. This page finds the doubled points, shows why an OpenStreetMap ID cannot be the key to deduplicate on, and keeps one match by an explicit rule or aggregates them all.
Quick answer
Number the rows before the join, then keep one match per number by an explicit rule β here the smallest, most specific polygon:
import geopandas as gpd
pois = gpd.read_file("data/rutland.zip", layer="gis_osm_pois_free_1").to_crs(27700)
landuse = gpd.read_file("data/rutland.zip", layer="gis_osm_landuse_a_free_1").to_crs(27700)
pois["poi_id"] = range(len(pois))
landuse["area_m2"] = landuse.area
joined = gpd.sjoin(pois, landuse[["fclass", "area_m2", "geometry"]], how="left", predicate="within",
lsuffix="poi", rsuffix="landuse")
one_each = joined.sort_values("area_m2").drop_duplicates(subset="poi_id", keep="first")
print(len(pois), "points ->", len(joined), "joined rows ->", len(one_each), "after keeping the smallest polygon")
531 points -> 569 joined rows -> 531 after keeping the smallest polygon
The duplicates are real matches. Whether you drop or combine them depends on what you want each output row to mean.
Choosing what each row should mean
Confirm the fan-out, then keep one match or aggregate
Confirm the result really is one-to-many
Compare row counts, and count how many rows each left feature produced:
matches = joined.groupby("poi_id").size()
print("Left rows:", len(pois), "| joined rows:", len(joined))
print("matched once:", (matches == 1).sum(), "| twice or more:", (matches > 1).sum(),
"| no polygon (NaN):", joined["index_landuse"].isna().sum())
Left rows: 531 | joined rows: 569
matched once: 493 | twice or more: 38 | no polygon (NaN): 108
The extra 38 rows are 38 points that matched two polygons, not corrupted data. With how="left", the 108 points in no land-use polygon at all are kept with NaN on the right-hand side.
Make sure the left layer has a stable unique ID
To find and resolve duplicates reliably, you need a column that identifies each left feature uniquely. An ID from the source data may not be one:
print("osm_id unique:", pois["osm_id"].is_unique)
shared = pois[pois["osm_id"].duplicated(keep=False)].sort_values("osm_id")
print(shared[["osm_id", "fclass", "name"]].head(4).to_string(index=False))
osm_id unique: False
osm_id fclass name
11047197103 bench Barrowden Village Bench Sign
11047197103 artwork Barrowden Village Bench Sign
1748426083 restaurant Hambleton Hall
1748426083 hotel Hambleton Hall
Geofabrik writes one point for each class an OpenStreetMap object has, so Hambleton Hall appears once as a restaurant and once as a hotel under the same osm_id. Deduplicating on osm_id after the join would silently merge the two. That is why the quick answer numbered the rows itself β poi_id is unique by construction. sjoin() also adds an index column (index_landuse here, index_right by default) holding the index of the matched right feature.
Inspect which features matched multiple polygons
Look at the duplicate rows and what they matched:
doubled = joined[joined["poi_id"].isin(matches[matches > 1].index)]
print(doubled[["poi_id", "name", "fclass_poi", "fclass_landuse"]].head(6).to_string(index=False))
pairs = doubled.groupby("poi_id")["fclass_landuse"].apply(lambda s: " + ".join(sorted(s)))
print(pairs.value_counts().head(5).to_dict())
poi_id name fclass_poi fclass_landuse
14 NaN camp_site forest
14 NaN camp_site grass
17 The Finch's Arms pub residential
17 The Finch's Arms pub meadow
74 NaN telephone residential
74 NaN telephone grass
{'grass + residential': 21, 'forest + grass': 6, 'meadow + residential': 5, 'park + residential': 4, 'forest + park': 1}
This tells you whether the duplicates are caused by overlapping zones, boundary points, or repeated polygons. Here they are overlapping zones: OpenStreetMap mappers draw landuse=grass, meadow or park inside larger residential areas, so a village green is both. One point sits in two grass polygons, which is a repeated polygon rather than a nesting.
Deduplicate by keeping one match
If you only want one land-use class per point, drop the extra matches β but sort first, so the row you keep is the one you want:
arbitrary = joined.drop_duplicates(subset="poi_id", keep="first")
by_area = joined.sort_values("area_m2").drop_duplicates(subset="poi_id", keep="first")
kept_first = arbitrary.set_index("poi_id")["fclass_landuse"].sort_index().fillna("")
kept_smallest = by_area.set_index("poi_id")["fclass_landuse"].sort_index().fillna("")
print(len(by_area), "rows either way;", (kept_first != kept_smallest).sum(), "points get a different class depending on the rule")
print(by_area.loc[by_area["name"] == "The Finch's Arms", ["name", "fclass_landuse", "area_m2"]].round(0).to_string(index=False))
531 rows either way; 22 points get a different class depending on the rule
name fclass_landuse area_m2
The Finch's Arms residential 125299.0
Both give one row per point, but for 22 of the 38 doubled points they keep different classes. Without a sort, drop_duplicates keeps whichever row came first, which is an accident of file order. Sorting by area keeps the more specific polygon β and "more specific" is not always the one you would guess: for The Finch's Arms it is the 12.5 ha residential area, because the meadow it was also mapped in is larger.
Aggregate the many matches instead of dropping them
If every match matters, keep them all but collapse them into one row per left feature:
summary = (
joined.groupby("poi_id")
.agg(landuse_count=("fclass_landuse", "count"),
landuse=("fclass_landuse", lambda s: " + ".join(sorted(s.dropna()))))
)
result = pois.merge(summary, left_on="poi_id", right_index=True)
print(len(result), "rows, one per point")
print(result["landuse_count"].value_counts().sort_index().to_dict())
531 rows, one per point
{0: 108, 1: 385, 2: 38}
This keeps every point geometry and records how many land-use polygons each fell in, and which. count skips NaN, so the unmatched points get 0.
Fix overlapping or duplicate right polygons
Sometimes the duplicates come from the data, not the geography. Dissolving the right layer by its class merges polygons of the same class:
by_class = landuse.dissolve(by="fclass").reset_index()
again = gpd.sjoin(pois, by_class[["fclass", "geometry"]], how="left", predicate="within",
lsuffix="poi", rsuffix="landuse")
print(len(landuse), "polygons ->", len(by_class), "classes;", len(again), "joined rows")
2405 polygons -> 19 classes; 568 joined rows
Dissolving by class removed only the one duplicate that two polygons of the same class caused. The rest remain, because a grass polygon inside a residential one is two different classes overlapping: dissolving cannot remove that, and it is not wrong β the green really is inside the village. For a layer that should be a clean partition, the fix is to cut the overlaps out (for example with overlay(..., how="difference")) or to choose a winner per point as above.
Rutland's points of interest and land use
The data used on this page
- Points and land use β the
gis_osm_pois_free_1andgis_osm_landuse_a_free_1layers of Geofabrik's OpenStreetMap shapefile extract for Rutland, reprojected to British National Grid. OpenStreetMap is edited daily, so the counts drift slightly. - Wards β Rutland's 15 electoral wards from the ONS Wards (December 2024) Boundaries UK BFC service (full resolution), in British National Grid, for the boundary examples.
import io
import os
import geopandas as gpd
import requests
os.makedirs("data", exist_ok=True)
if not os.path.exists("data/rutland.zip"):
reply = requests.get("https://download.geofabrik.de/europe/united-kingdom/england/rutland-260101-free.shp.zip",
timeout=300)
reply.raise_for_status()
open("data/rutland.zip", "wb").write(reply.content)
if not os.path.exists("data/rutland_wards.gpkg"):
reply = requests.get("https://services1.arcgis.com/ESMARspQHYMw9BZ9/arcgis/rest/services/"
"Wards_December_2024_Boundaries_UK_BFC/FeatureServer/0/query",
params={"where": "LAD24NM='Rutland'", "outFields": "WD24NM", "outSR": 27700, "f": "geojson"},
timeout=120)
reply.raise_for_status()
gpd.read_file(io.BytesIO(reply.content)).to_file("data/rutland_wards.gpkg")
print(len(gpd.read_file("data/rutland_wards.gpkg")), "wards")
15 wards
Example 1: A point on a shared boundary
A vertex of the boundary that two wards share is, by definition, on both of their edges. Taking one from the data and joining it with three predicates:
import shapely
wards = gpd.read_file("data/rutland_wards.gpkg")
a, b = wards[wards["WD24NM"] == "Barleythorpe"].geometry.iloc[0], wards[wards["WD24NM"] == "Exton"].geometry.iloc[0]
shared_edge = a.boundary.intersection(b.boundary)
on_edge = gpd.GeoDataFrame({"poi_id": [0]}, geometry=[shapely.Point(shapely.get_coordinates(shared_edge)[5])],
crs=wards.crs)
for predicate in ["within", "intersects", "touches"]:
hits = gpd.sjoin(on_edge, wards, predicate=predicate)
print(f"{predicate:10} {len(hits)} match(es) {sorted(hits['WD24NM'])}")
print(f"{'nearest':10} {len(gpd.sjoin_nearest(on_edge, wards))} match(es), both at distance 0")
within 0 match(es) []
intersects 2 match(es) ['Barleythorpe', 'Exton']
touches 2 match(es) ['Barleythorpe', 'Exton']
nearest 2 match(es), both at distance 0
within matches neither ward, because a point on the edge is not in either interior; intersects and touches match both; sjoin_nearest returns every feature tied at the minimum distance, here both. If boundary points must resolve to exactly one zone, deduplicate after the join with an explicit rule rather than relying on the predicate to break the tie.
Example 2: Keep one match per point with a priority rule
A priority can come from a ranking of classes instead of area:
priority = {"residential": 1, "commercial": 1, "retail": 1, "industrial": 1} # built-up areas win
ranked = joined.assign(rank=joined["fclass_landuse"].map(priority).fillna(2))
built_up_first = ranked.sort_values(["poi_id", "rank"]).drop_duplicates(subset="poi_id", keep="first")
print(built_up_first["fclass_landuse"].value_counts().head(5).to_dict())
{'residential': 224, 'retail': 102, 'grass': 41, 'nature_reserve': 12, 'park': 11}
Each point now takes the built-up class when it has one. Use whatever column encodes your rule β area, rank, date β and make it explicit.
Example 3: A second join needs the index column gone
first = gpd.sjoin(pois, landuse[["fclass", "geometry"]], how="left", predicate="within")
try:
gpd.sjoin(first, wards[["WD24NM", "geometry"]], how="left", predicate="within")
except ValueError as err:
print("ValueError:", err)
second = gpd.sjoin(first.drop(columns="index_right"), wards[["WD24NM", "geometry"]], how="left", predicate="within")
print(len(first), "->", len(second), "rows; points in no ward:", second["WD24NM"].isna().sum())
ValueError: 'index_right' cannot be a column name in the frames being joined
569 -> 569 rows; points in no ward: 10
The row count survives the second join unchanged only because the wards do not overlap: each point is in at most one of them.
Why the rule has to be yours
Why a spatial join returns one row per match, and the general ways to get one row per input, are in spatial join cardinality explained. What this data adds is that every way of choosing a row is a decision about the world. Keeping the smallest polygon and keeping the first row disagreed for 22 of the 38 doubled points, and the smallest is not always the one you would guess: The Finch's Arms sits in a 12.5 ha residential area that is smaller than the meadow it is also mapped in. A class ranking (Example 2) gives a third answer. None is wrong; each says something different about what a point's land use is, so the rule belongs in the code where a reader can see it.
The key matters as much as the rule. Geofabrik writes one point per class an object has, so osm_id is not unique β Hambleton Hall is a restaurant and a hotel under one ID β and deduplicating on it after a join would silently merge two features.
A second join and the index column
Keeping versus dropping index_right
sjoin() adds index_right (or index_<rsuffix>) to record which right feature each row matched. It is useful for inspecting and deduplicating, but you usually do not want it in the final output. Drop it once you are done:
result = one_each.drop(columns="index_landuse")
If you run a second sjoin() on the result while index_right is still present, GeoPandas raises ValueError: 'index_right' cannot be a column name in the frames being joined (Example 3), so remove it between joins.
Internal links
- How to Perform a Spatial Join in Python (GeoPandas)
- Spatial Join Returns Empty Results in GeoPandas: How to Fix It
- How to Count Points in Polygons with GeoPandas
- How to Select Features by Location in GeoPandas
- How to Aggregate Spatial Data by Region in GeoPandas
- How to Dissolve Polygons in Python (GeoPandas)
FAQ
Why does my spatial join have more rows than the input layer?
Because at least one left feature matched more than one right polygon. A spatial join returns one row per matching pair, so overlapping zones or boundary points naturally produce extra rows.
Is duplicate rows after sjoin() a bug?
No. It is expected behavior when a geometry genuinely relates to several features. The fix is to decide whether to keep one match, aggregate all matches, or clean overlapping source polygons.
How do I keep only one match per feature?
Sort the joined GeoDataFrame by a meaningful column, then call drop_duplicates(subset="poi_id", keep="first") on an ID that is unique in the left layer. The sort decides which match survives, so without it the kept row is arbitrary.
Why does my left join keep rows that matched nothing, and how do I find them?
With how="left", unmatched left features are retained with NaN in the right-hand columns, including index_right. Find them with joined[joined["index_right"].isna()]; switch to how="inner" if you want to drop non-matching features instead.
Does sjoin_nearest() also produce duplicate rows?
Yes. If two right features are tied at the same minimum distance, gpd.sjoin_nearest() returns one row per tie β a point on the boundary between two wards got both, at distance 0. Deduplicate the same way: sort by a tie-breaker column, then drop_duplicates(subset="poi_id").
Can I count how many polygons each point matched without aggregating away the geometry?
Yes. Compute the counts separately and merge them back: counts = joined.groupby("poi_id").size().rename("match_count"), then pois.merge(counts, left_on="poi_id", right_index=True). This keeps the original point geometries while adding the match tally.
Why does my second spatial join fail with "'index_right' cannot be a column name"?
The first sjoin() left its index_right column in the result, and the second join refuses to overwrite it. Drop it between joins with result = result.drop(columns="index_right") before calling sjoin() a second time.