What Is a Spatial Join? Predicates, Types, and SQL

A spatial join combines two tables by where their geometries sit. It pairs rows whose shapes meet a spatial condition, with no shared ID column needed.

Examples are a point that falls inside a polygon, two parcels that share a boundary, and a facility within 2 kilometers of a river. Data about the physical world rarely shares a key across sources. So the spatial join is the step that connects building footprints to flood zones, delivery stops to sales territories, and satellite pixels to farm fields.

Key takeaways

• A spatial join matches rows on a spatial predicate, such as ST_Intersects or ST_DWithin, between two geometry columns.
• Both sides need geometry in the same coordinate reference system (CRS), and argument order matters for asymmetric predicates such as ST_Contains.
• The four types of spatial join are predicate joins (point in polygon is the common case), distance joins, k nearest neighbor (KNN) joins, and raster-vector joins.
• Engines partition space, filter candidates on bounding boxes with an index, and refine only those with the exact predicate. EXPLAIN FORMATTED shows which join operator WherobotsDB will run.

What is a spatial join?

An attribute join, such as Esri's Add Join tool or a SQL JOIN ... ON a.id = b.id, matches rows when a common field holds equal values. A spatial join matches rows on how their shapes relate. Esri's Spatial Join tool transfers attributes from join features to target features by their spatial relationship. In SQL the relationship goes in the ON clause as a function call.

	Attribute join	Spatial join
Join condition	Equal values in a shared column	A spatial predicate between two geometries
Requirement	A common key in both tables	Geometry in both tables, in one CRS
Example	Policies to customers on customer_id	Policies to flood zones by location
Result size	Bounded by key matches	One row per matching pair, which can exceed both inputs

The two often chain: a spatial join gives each building a parcel ID, and an attribute join on it brings in assessor records.

How a spatial join works

Start from a query. This one counts the points of interest inside each Colorado city and town, using open data from the Overture Maps Foundation:

WITH localities AS (
  SELECT id, names.primary AS city, geometry
  FROM wherobots_open_data.overture_maps_foundation.divisions_division_area
  WHERE subtype = 'locality' AND region = 'US-CO'
)
SELECT l.id, l.city, COUNT(p.id) AS places
FROM localities AS l
JOIN wherobots_open_data.overture_maps_foundation.places_place AS p
  ON ST_Intersects(l.geometry, p.geometry)
GROUP BY l.id, l.city

That JOIN is spatial because of its geometry columns, a shared CRS, and a spatial predicate. Each side has a geometry or geography column. Geometry stores flat-plane coordinates. Geography stores them on the curved Earth, with distances in meters. Both sides share a CRS. The International Association of Oil & Gas Producers (IOGP) keeps a registry of coordinate systems, the EPSG Dataset. It gives each one a code, such as EPSG:4326. EPSG:4326 stores degrees, and EPSG:2263 stores New York State Plane feet. A join across two systems returns wrong rows, so ST_Transform one side first. A spatial predicate sits in the ON clause. A predicate is a test that returns true or false for a pair of shapes. Each left row can match zero, one, or many right rows, so a point on a shared city line can appear twice.

Spatial predicates and the DE-9IM

Topological predicates test how two shapes relate. Max Egenhofer and Robert Franzosa (1991) compared the interiors and boundaries of two shapes. Eliseo Clementini and colleagues (1993) added the dimension of each intersection. The result is the Dimensionally Extended 9-Intersection Model (DE-9IM). It is a 3 by 3 matrix that records where the interior, boundary, and exterior of one shape meet those of the other. The Open Geospatial Consortium (OGC) Simple Feature Access standard (ISO 19125) defines Intersects, Contains, Within, Touches, Crosses, Overlaps, Equals, and Disjoint as patterns of that matrix. Every compliant implementation returns the same result for the same pair.

Predicate	Returns true when	Typical use
ST_Intersects(A, B)	A and B have at least one point in common, boundary included	Point in polygon, overlapping polygons, lines that meet
ST_Contains(A, B)	Every point of B lies in A, and their interiors share a point	Assigning assets to a territory
ST_Within(B, A)	B lies inside A. It is the converse of ST_Contains	Filtering records to a boundary
ST_Covers(A, B)	No point of B lies outside A, so points on A's boundary count	Containment where an edge point should match
ST_Touches(A, B)	A and B share boundary points and no interior points	Finding adjacent parcels
ST_Crosses(A, B)	A line passes through a polygon or another line	A pipeline crossing a river
ST_DWithin(A, B, d)	A and B lie within distance d, where d can be a column that varies per row	Proximity with a radius per record

ST_Intersects, the broadest predicate, is symmetric: swapping A and B gives the same answer. Under the PostGIS definition, a polygon does not contain a point on its boundary, because the interiors share no point; ST_Covers matches it. Parcels that share a fence line touch, and parcels that overlap by a meter do not.

Inner, left, and full outer spatial joins

An inner join keeps only matched pairs, so a city with no points of interest drops out of the query above. A left join keeps every left row, with nulls in the right table's columns and a COUNT(p.id) of 0. A full outer join keeps unmatched rows from both sides: cities with no places, and places outside every city.

A left join followed by WHERE p.id IS NULL is an anti join, which returns rows with no spatial match, such as buildings outside every flood zone. ST_Disjoint in a join condition matches almost every pair. In WherobotsDB, predicate, distance, and raster-vector joins accept LEFT JOIN and FULL OUTER JOIN. The ST_KNN join is an inner join on the query table: a query row with no neighbor is left out.

Types of spatial join

Predicate join and point in polygon

A predicate join, also called a range join, pairs rows whose geometries satisfy a topological predicate. Point in polygon is the standard case: ON ST_Contains(zones.geometry, assets.geometry) gives each address or store its zone. The exact test checks the point against every polygon edge, so its cost grows with the number of vertices.

Distance join

A distance join pairs rows whose geometries lie within a distance of each other, such as ON ST_DWithin(stations.geom_m, homes.geom_m, stations.walk_radius_m). The distance can be a column, so each row carries its own radius. For geometry the distance follows the CRS: on EPSG:4326 data, ST_DWithin(a, b, 2) means 2 degrees, hundreds of kilometers. Project both sides to meters, pass useSpheroid = true (centroid to centroid), or use the geography type.

Nearest-neighbor join

A nearest-neighbor join returns, for each row on one side, the k closest rows on the other, with no radius. ST_KNN(R, S, k, use_sphere, search_radius) takes five arguments: the query geometry, the object geometry, k, the distance model, and an optional search radius. With use_sphere = false, the distance is measured on a flat plane, boundary to boundary. With true, it is great-circle meters between centroids, which shifts results for large shapes. Distances in degrees rank neighbors wrongly, because a degree of longitude shrinks toward the poles. Project to meters first. ST_AKNN returns approximate neighbors faster.

Raster-vector join

A raster stores a value in every grid cell. A raster-vector join pairs raster tiles with the geometries they overlap, using RS_Intersects, RS_Contains, or RS_Within. It then summarizes the pixels inside each geometry with zonal statistics, such as their count, sum, or mean. Examples are mean elevation per town from a digital elevation model and vegetation per field from a Sentinel-2 Normalized Difference Vegetation Index (NDVI) raster.

How engines run spatial joins

A spatial predicate has no equal-value key to group rows by, so a naive join compares every pair. Matching 10 million buildings against 50,000 flood zones takes 500 billion tests. A join that runs on a sample in a single-node database such as PostGIS can stall on a full country. Filtering on boxes, partitioning space, and indexing cut the cost.

Filter and refine

The filter step compares minimum bounding rectangles, the smallest upright box around each shape, at four number comparisons per pair. It keeps the pairs whose boxes overlap. The refine step runs the exact predicate on those candidates only. It drops the false hits, pairs whose boxes overlap while their shapes do not. Brinkhoff, Kriegel, Schneider, and Seeger formalized the multi-step processing of spatial joins (SIGMOD 1994), adding cheaper approximations between the two steps so fewer candidates reach the costly geometry test.

Spatial partitioning

On a cluster, space is split first so that rows that could match land on the same worker:

• Uniform grid. Equal cells, badly balanced when data clusters: one downtown cell can outweigh a thousand rural ones.
• Quadtree. Raphael Finkel and Jon Bentley introduced the quad tree (Acta Informatica, 1974), which splits a crowded cell into four children, so dense areas get small cells.
• KDB-tree. John Robinson's K-D-B-tree (SIGMOD 1981) splits at the median along alternating axes, so each cell holds about the same number of rows. It is the default partitioning for WherobotsDB joins (sedona.join.gridtype = kdbtree).

A polygon that crosses a cell edge is copied to every cell it touches, and duplicate pairs are removed after the join.

Spatial indexes

Inside each partition, an index finds candidates without a full scan. Antonin Guttman's R-tree (SIGMOD 1984) nests nearby bounding boxes in larger rectangles. A lookup then searches only the branches that overlap its own box. A quadtree index does the same with square cells. WherobotsDB supports both (sedona.global.indextype), and its optimizer documentation shows the index as a SpatialIndex node in the plan.

Broadcast joins vs. partitioned joins

A small side, such as a few hundred city polygons, can be broadcast: copied to every worker and indexed there while the large side stays in place. Two large sides are partitioned into the same KDB-tree cells and joined cell by cell. WherobotsDB names the two plans BroadcastIndexJoin and RangeJoin (or DistanceJoin for distance predicates). A broadcast applies to a side under the size threshold or with a /*+ BROADCAST(t) */ hint, where the join type allows it.

Grids such as H3 offer another route. Each row on both sides gets a grid cell ID, and an attribute join matches equal IDs. A refine step then runs the exact predicate, because two shapes in one cell may still not touch.

Spatial join examples

• Insurance. Policy locations joined to flood risk polygons give exposed value per zone, the first step of a catastrophe model.
• Land and siting. Buildings join to parcel data, and candidate sites join to power lines to screen data center sites.
• Agriculture and access. Fields join to Sentinel-2 imagery for a vegetation time series, and a KNN join assigns each stop its nearest depot.

Spatial joins in Wherobots

WherobotsDB, the cloud spatial engine in Wherobots for vector and raster data, comes from the original creators of Apache Sedona and is compatible with Sedona's APIs. The four queries below ran in a Wherobots SQL session, and every join result below comes from them. They read three tables in Wherobots Open Data. Each is a Havasu table, Apache Iceberg with geometry and raster column types.

Source	Table	Contents	Rows (October 2026)	What the queries read
Overture Maps divisions	overture_maps_foundation.divisions_division_area	Boundary polygons	1,080,667	The 304 Colorado localities, and subsets around Boulder and the Rockies
Overture Maps places	overture_maps_foundation.places_place	Points of interest	81,455,423	368,631 places in the Colorado box, plus Boulder places and Denver schools and hospitals
Copernicus digital elevation model (DEM) GLO-30	copernicus_dem.glo_30m	Elevation raster tiles with footprint polygons	3,961,635	The 256 tiles that touch the mountain box

Raster and vector joins ran in one engine, each query in 9.5 to 61.9 seconds; most stacks pick one data model. In a notebook, pass the same SQL to sedona.sql():

from sedona.spark import SedonaContext

config = SedonaContext.builder().getOrCreate()
sedona = SedonaContext.create(config)

Places per Colorado town with a point-in-polygon left join

Count Overture places in every Colorado locality, and keep localities with none. Window functions, which compute totals across all result rows, add the summary columns.

WITH localities AS (
  SELECT id, names.primary AS city, geometry
  FROM wherobots_open_data.overture_maps_foundation.divisions_division_area
  WHERE subtype = 'locality' AND region = 'US-CO'
),
places AS (
  -- Prefilter places to Colorado's bounding box before the join
  SELECT id, geometry
  FROM wherobots_open_data.overture_maps_foundation.places_place
  WHERE bbox.xmin BETWEEN -109.06 AND -102.04 AND bbox.ymin BETWEEN 36.99 AND 41.0
),
counts AS (
  SELECT l.id, l.city, COUNT(p.id) AS place_count
  FROM localities l
  LEFT JOIN places p ON ST_Intersects(l.geometry, p.geometry)
  GROUP BY l.id, l.city
)
SELECT city, place_count,
       RANK() OVER (ORDER BY place_count DESC) AS place_rank,
       COUNT(*) OVER () AS localities,
       SUM(CASE WHEN place_count = 0 THEN 1 ELSE 0 END) OVER () AS localities_with_no_places,
       SUM(place_count) OVER () AS matched_pairs
FROM counts
ORDER BY place_count DESC, city

The query ran in 23.6 seconds and returned all 304 Colorado localities, with 243,542 locality and place pairs. Colorado Springs leads with 33,521 places, ahead of Aurora with 15,931 and Boulder with 12,701. Eleven localities have no places, rows an inner join would hide. Most are campgrounds tagged as localities, such as Kelly Dahl Campground, and one is the town of Delta, a boundary worth checking. The locality filter sits in the WITH clause, before the join, so it never removes the unmatched rows.

Full outer join of places and towns around Boulder

A full outer join needs both sides filtered before the join, and an ID from each side in the output, because an unmatched place has no city to group by:

-- Boulder area
WITH localities AS (
  SELECT id, geometry
  FROM wherobots_open_data.overture_maps_foundation.divisions_division_area
  WHERE subtype = 'locality' AND region = 'US-CO'
    AND bbox.xmin BETWEEN -105.35 AND -105.0 AND bbox.ymin BETWEEN 39.9 AND 40.1
),
places AS (
  SELECT id, geometry
  FROM wherobots_open_data.overture_maps_foundation.places_place
  WHERE bbox.xmin BETWEEN -105.3 AND -105.2 AND bbox.ymin BETWEEN 39.98 AND 40.06
),
pairs AS (
  SELECT l.id AS locality_id, p.id AS place_id
  FROM localities l
  FULL OUTER JOIN places p ON ST_Intersects(l.geometry, p.geometry)
)
SELECT SUM(CASE WHEN locality_id IS NOT NULL AND place_id IS NOT NULL THEN 1 ELSE 0 END) AS matched_pairs,
       SUM(CASE WHEN place_id IS NULL THEN 1 ELSE 0 END) AS localities_with_no_places,
       SUM(CASE WHEN locality_id IS NULL THEN 1 ELSE 0 END) AS places_outside_every_locality
FROM pairs

Around Boulder the query returns 11,914 matched pairs, 6 localities with no places, and 321 places outside every locality. It took 42.2 seconds, and the plan in the next section shows why.

Nearest hospitals to Denver schools with KNN and distance joins

Find the nearest hospital to every school around central Denver, and count the hospitals within 2 km of each. Both sides move to Universal Transverse Mercator (UTM) zone 13N (EPSG:26913), a CRS in meters, so distances come back in meters. The query writes that CRS as a string for PROJ, the open source projection library. The hospital box is wider, so schools near its edge still match their true nearest hospital.

-- Schools and hospitals in a box around central Denver, in UTM zone 13N meters (EPSG:26913)
WITH schools AS (
  SELECT id, names.primary AS school, geometry,
         ST_Transform(geometry, 'EPSG:4326', '+proj=utm +zone=13 +datum=NAD83 +units=m +no_defs') AS geom_m
  FROM wherobots_open_data.overture_maps_foundation.places_place
  WHERE taxonomy.primary IN ('elementary_school', 'middle_school', 'high_school')
    AND bbox.xmin BETWEEN -105.11 AND -104.6 AND bbox.ymin BETWEEN 39.61 AND 39.91
),
hospitals AS (
  -- Search a wider area so schools near the edge still find their true nearest hospital
  SELECT id, names.primary AS hospital, geometry,
         ST_Transform(geometry, 'EPSG:4326', '+proj=utm +zone=13 +datum=NAD83 +units=m +no_defs') AS geom_m
  FROM wherobots_open_data.overture_maps_foundation.places_place
  WHERE taxonomy.primary = 'hospital'
    AND bbox.xmin BETWEEN -105.3 AND -104.4 AND bbox.ymin BETWEEN 39.4 AND 40.1
),
nearest AS (
  -- k-nearest-neighbor join: the one closest hospital to each school
  SELECT s.id, s.school, h.hospital,
         ST_X(s.geometry) AS school_lon, ST_Y(s.geometry) AS school_lat,
         ST_X(h.geometry) AS hospital_lon, ST_Y(h.geometry) AS hospital_lat,
         ST_Distance(s.geom_m, h.geom_m) AS distance_m
  FROM schools s
  JOIN hospitals h ON ST_KNN(s.geom_m, h.geom_m, 1, false)
),
within_2km AS (
  -- Distance join: every hospital within 2 km of each school, keeping schools with none
  SELECT s.id, COUNT(h.id) AS hospitals_within_2km
  FROM schools s
  LEFT JOIN hospitals h ON ST_DWithin(s.geom_m, h.geom_m, 2000)
  GROUP BY s.id
)
SELECT n.school, n.hospital, ROUND(n.distance_m) AS distance_m, w.hospitals_within_2km,
       n.school_lon, n.school_lat, n.hospital_lon, n.hospital_lat,
       COUNT(*) OVER () AS schools,
       ROUND(AVG(n.distance_m) OVER ()) AS mean_distance_m,
       ROUND(MAX(n.distance_m) OVER ()) AS max_distance_m,
       SUM(CASE WHEN w.hospitals_within_2km = 0 THEN 1 ELSE 0 END) OVER () AS schools_with_none_within_2km,
       SUM(CASE WHEN n.distance_m > 2000 THEN 1 ELSE 0 END) OVER () AS schools_nearest_over_2km
FROM nearest n
JOIN within_2km w ON n.id = w.id
ORDER BY distance_m DESC

In 9.5 seconds the query paired 596 schools with a hospital, at a mean distance of 1,589 meters. Turnberry Elementary School, the farthest, sits 9,915 meters from Intermountain Health Platte Valley Hospital. The distance join found 151 schools with no hospital within 2 km. The KNN join found the same 151 schools with a nearest hospital beyond 2 km, so the two join types agree.

Surface height per mountain town with a raster-vector join

Compute the mean surface height of the towns in a 1-degree box over the central Colorado Rockies. The source is the Copernicus 30 m DEM, a surface model that includes roofs and trees. A town can span several tiles, so the query sums pixel values and counts per tile and divides once per town.

-- Mean surface height of the towns in a 1-degree box over the upper Arkansas Valley and South Park
WITH towns AS (
  SELECT id, names.primary AS town, geometry
  FROM wherobots_open_data.overture_maps_foundation.divisions_division_area
  WHERE subtype = 'locality' AND region = 'US-CO'
    AND bbox.xmin >= -106.6 AND bbox.xmax <= -105.6 AND bbox.ymin >= 38.8 AND bbox.ymax <= 39.8
),
dem AS (
  -- Keep only the elevation tiles that touch the box
  SELECT rast
  FROM wherobots_open_data.copernicus_dem.glo_30m
  WHERE ST_Intersects(footprint, ST_GeomFromText(
    'POLYGON((-106.6 38.8, -105.6 38.8, -105.6 39.8, -106.6 39.8, -106.6 38.8))', 4326))
),
per_tile AS (
  -- One zonal-statistics call per town-tile pair returns the pixel count and sum together
  SELECT t.id, t.town, RS_ZonalStatsAll(d.rast, t.geometry, 1) AS z
  FROM towns t
  JOIN dem d ON RS_Intersects(d.rast, t.geometry)
)
SELECT town, COUNT(*) AS tiles, SUM(z.count) AS cells,
       ROUND(SUM(z.sum) / SUM(z.count)) AS mean_surface_m
FROM per_tile
GROUP BY id, town
ORDER BY mean_surface_m DESC

The join paired 22 localities with their tiles in 61.9 seconds. Alma comes out highest at a mean of 3,185 meters over 1,262 cells in 2 tiles, followed by Montezuma at 3,140 meters and Leadville at 3,088 meters.

Reading the plan with EXPLAIN FORMATTED

Prefix a query with EXPLAIN FORMATTED to print its physical plan without running it. Each plan came back in under 5 seconds and names the join operator WherobotsDB will run:

Query	Join operator in the plan	What it means
Colorado, LEFT JOIN on ST_Intersects	BroadcastIndexJoin (LeftOuter, INTERSECTS) over SpatialIndex (RTREE) on places	A left outer join can broadcast only its right side, so the Colorado places are indexed and sent to every worker
Boulder, FULL OUTER JOIN	BroadcastNestedLoopJoin (FullOuter), with ST_Intersects as the join condition	No spatial operator covers a full outer join, so every locality is tested against every place
Denver, ST_KNN	BroadcastQuerySideKNNJoin (LeftSide, Inner, k = 1)	The schools, the query side, are broadcast and each finds its nearest hospital
Denver, ST_DWithin	BroadcastIndexJoin (LeftOuter, ST_DWithin) over SpatialIndex (RTREE, 2000.0)	An R-tree of hospitals, expanded by the 2 km distance, is broadcast; schools with no match survive the left join
Mountain towns, RS_Intersects	BroadcastIndexJoin (Inner, INTERSECTS, RS_Intersects) over SpatialIndex (RTREE) on rast	An R-tree of the DEM tiles is broadcast, and the town polygons stream past it

None of the plans contains a CartesianProduct, and four of the five use an R-tree. The Boulder plan explains its 42 seconds. The broadcast join types are inner, left semi, left anti, left outer, and right outer. A full outer spatial join is none of these, so it runs as a nested loop. On large tables, build the same result from a left join plus a left anti join from the other side. Both run as indexed joins. A plan also shows where filters run: in the raster plan the footprint test runs after the scan of glo_30m, so every tile row is read first.

Spatial join research and what changes at scale

From z-order to R-tree joins

Jack Orenstein's Spatial query processing in an object-oriented database system (SIGMOD 1986) mapped two-dimensional space onto a one-dimensional z-order curve. A spatial join became a merge of two sorted lists of z-values followed by an exact check, an early filter-and-refine join. Thomas Brinkhoff, Hans-Peter Kriegel, and Bernhard Seeger joined two R-trees by traversing them together in Efficient processing of spatial joins using R-trees (SIGMOD 1993).

Joins without an index

R-tree joins assume both inputs carry an index. Jignesh Patel and David DeWitt's Partition based spatial-merge join (SIGMOD 1996), PBSM, handled inputs with none. It partitions both sides on a grid and copies shapes that cross an edge. It joins each partition with a plane sweep, which scans shapes in coordinate order, and removes duplicates. Edwin Jacox and Hanan Samet's Spatial join techniques (ACM TODS, 2007) surveys the field.

Nick Roussopoulos and colleagues gave an R-tree algorithm for nearest neighbor queries (SIGMOD 1995). Christian Böhm and Florian Krebs defined the k-nearest neighbour join (2004) as an operator.

Spatial joins on clusters

Ablimit Aji and colleagues built Hadoop-GIS (PVLDB 2013) on MapReduce. Ahmed Eldawy and Mohamed Mokbel's SpatialHadoop (ICDE 2015) added a global spatial index to Hadoop, and Eldawy, Louai Alarabi, and Mokbel compared seven partitioning schemes for joins (PVLDB 2015).

Jia Yu, Jinxuan Wu, and Mohamed Sarwat brought these ideas to Apache Spark with GeoSpark (ACM SIGSPATIAL 2015). Yu, Zongsi Zhang, and Sarwat described its partitioners and indexes in a 2019 GeoInformatica paper. GeoSpark became Apache Sedona, the lineage of the RangeJoin and BroadcastIndexJoin operators in WherobotsDB. Varun Pandey and colleagues benchmarked these systems in How good are modern spatial analytics systems? (PVLDB 2018). On SpatialBench at scale factor 1000, the current WherobotsDB runs spatial queries and joins up to 2.5 times faster than the previous generation, with a mean speedup of 1.9 times.

Samriddhi Singla, Ahmed Eldawy, and colleagues defined Raptor (SIGSPATIAL 2021), a raster-vector join operator for Spark. It reads pixels in place in one scan, so neither side of the raster versus vector split needs conversion.

Open problems

• Skew. KDB-tree partitions balance row counts, which are only part of the cost. A partition holding a detailed coastline or a dense downtown runs far longer than the rest. Suprio Ray and colleagues showed that the refine step causes this processing skew and partitioned by estimated compute cost in Skew-resistant parallel in-memory spatial join (SSDBM 2014).
• Huge polygons. A country or river basin crosses many partitions, is copied to each, and costs a long edge walk in every refine test. Thanasis Georgiadis and Nikos Mamoulis approximated polygons with Raster Intervals (SIGMOD 2023), an intermediate filter before the exact test that cut end-to-end join cost by up to 10 times. Subdividing large polygons, or joining on H3 cells first, reduces the same cost.
• Choosing the plan. The best partitioning, index, and broadcast settings depend on the data. Dimitrios Tsitsigkos and colleagues tuned the classic partitioning join for multicore memory in Parallel in-memory evaluation of spatial joins (SIGSPATIAL 2019). Bouros and Mamoulis name complex data types, new join predicates, modern hardware, and parallel processing as the open directions in Spatial joins: what's next? (SIGSPATIAL Special, 2019).

Conclusions

1. A distance or nearest-neighbor join is correct only when both sides share a CRS in meters. The Denver query projected schools and hospitals to UTM zone 13N, so its 1,589 m mean distance is in meters.
2. A left spatial join keeps rows with no match, and an inner join drops them. Eleven Colorado localities with no places survived the left join. Filter each side in the WITH clause before the join, so the filter keeps them too.
3. A full outer spatial join runs as a nested loop in WherobotsDB, so avoid it on large tables. The Boulder query planned as a BroadcastNestedLoopJoin and took 42.2 seconds.
4. A raster-vector join is a spatial join plus a pixel summary. Call zonal statistics once per town-tile pair and add up the results in SQL. RS_ZonalStatsAll returned count and sum for 22 towns in 61.9 seconds.
5. The plan shows whether a spatial join will scale. Run EXPLAIN FORMATTED before a large join. A BroadcastIndexJoin or RangeJoin over a SpatialIndex is the target. A CartesianProduct or nested loop is the warning.
6. A spatial join returns only the pairs its predicate and boundaries allow. ST_Contains(asset, zone) returns almost nothing for points, so check argument order. Denver is absent from the locality results above, so check how each boundary is typed. ST_MakeValid repairs the self-intersecting polygons that ST_IsValid flags.

Related terms

• Geospatial database: geometry types, a spatial index and spatial SQL in one system
• Raster vs. vector data: the two data models a join connects
• Digital elevation model: a raster summarized per polygon
• Parcel data: land records joined by location
• Flood risk data: hazard zones joined to assets
• Points of interest (POI) data: points of interest counted per area
• Geospatial AI: model outputs as geometries to join
• Geospatial analysis: the methods that spatial joins support
• H3: a hexagonal grid for pre-filtering joins
• Coordinate reference system: both sides of a join must share one
• Ontology: the relations spatial joins compute
• Geospatial analytics at scale: why joins are one of five causes of slow spatial work
• Geofence: a point-in-polygon join between locations and zones, run in time order
• Map matching: a nearest-road join that places each GPS record on a road

Read more from Wherobots

• Scaling spatial analysis: how KNN solves the spatial density problem
• El Niño 2026, atmospheric rivers, and California's burn scars: one SQL engine, two data models
• Raster spatial joins at scale: Google Earth Engine and BigQuery vs Apache Sedona and Wherobots
• How to score every building in a state for catastrophe risk
• Spatial joins in Wherobots: the Getting Started notebook, Part 4

Run your first spatial join with a Wherobots free trial at cloud.wherobots.com.