Join / spatial · All operations

Join by location

Join on spatial relationship, distance, containment.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today, with the spatial extension.

Shape of the result
one table from two or more
Level
needs a DuckDB extension

The DuckDB step

Points within a distance

It reads the customers table the proofs share. Create it first:

SQL
CREATE TABLE customers AS SELECT * FROM (VALUES
  ('Alice Ng',    'retail',    DATE '2025-06-01', 52.52, 13.40),
  ('Bob Okafor',  'wholesale', DATE '2025-09-15', 48.85,  2.35),
  ('Carla Diaz',  'retail',    DATE '2025-11-30', 40.42, -3.70),
  ('Frank Moore', 'retail',    DATE '2026-01-01', 51.51, -0.13)
) t(customer, segment, joined, lat, lon);

Then run the statement:

SQL
INSTALL spatial; LOAD spatial;
SET geometry_always_xy = true;
CREATE TEMP TABLE result AS
SELECT a.customer, b.customer AS neighbour, ST_Distance_Sphere(ST_Point(a.lon, a.lat), ST_Point(b.lon, b.lat)) / 1000 AS km
FROM customers a JOIN customers b ON a.customer < b.customer
WHERE ST_Distance_Sphere(ST_Point(a.lon, a.lat), ST_Point(b.lon, b.lat)) < 1000000;
SELECT customer, neighbour, round(km) AS km FROM result ORDER BY km;
SELECT CASE WHEN ((SELECT count(*) = 3 AND max(km) FILTER (WHERE customer = 'Bob Okafor' AND neighbour = 'Frank Moore') BETWEEN 340 AND 348 AND max(km) FILTER (WHERE customer = 'Alice Ng' AND neighbour = 'Bob Okafor') BETWEEN 870 AND 885 AND count(*) FILTER (WHERE customer = 'Carla Diaz' OR neighbour = 'Carla Diaz') = 0 FROM result)) THEN 'as expected' ELSE error('Paris to London is not about 344 km, or the pairs within 1000 km are not the three expected') END AS verdict;
  • It needs the spatial extension.
  • The statement checks its own result and raises an error when it is wrong: “Paris to London is not about 344 km, or the pairs within 1000 km are not the three expected”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Join on a spatial relationship

Join rows whose shapes intersect, contain, touch or lie within one another.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (2)
  • Find Nearestread 2026-09-27

    For each object in one input find the nearest objects in a second input, with distance

  • Spatial Matchread 2026-09-27

    Join two sets of spatial objects by relationship: contains, intersects, touches, within

Duckle (1)
  • Spatial Joinread 2026-09-25

    join two inputs on a spatial predicate such as ST_Intersects or ST_Within

Tableau Prep (1)

Background

A spatial join combines the attribute tables of two spatial layers based on a spatial relation between their geometries.[1] A spatial join is like a relational table join, except that each pair of rows is matched by location rather than by a common key value.[1] Spatial joins are used in spatial analysis and management, including allocating individuals to districts and statistical aggregation.[1]

When to use it

In PostGIS, a spatial join can find in one step the neighbourhood that contains a given subway station, rather than first extracting the station's point and then querying with it.[2] In PostGIS, a spatial join combined with GROUP BY gives the kind of analysis usually done in a GIS system, such as the population of each neighbourhood from the census blocks that intersect it.[2] In PostGIS, a distance test such as ST_DWithin can be the join condition, which gives summaries of all the items within a radius.[2]

When a spatial join is run, the direction of attachment must be specified, because a spatial predicate may not be commutative and rows are often related many to one.[1] Some spatial relations are commutative, as when A overlaps B exactly when B overlaps A, while others are not: A within B does not mean B is within A.[1] When many rows match one row, some GIS software does not allow the join, but most implementations allow an aggregate join that attaches summaries such as counts, sums or means.[1] Decide which table each output row belongs to before writing the join, and aggregate the other side wherever one row can match many. (Meridian's view)

The method

A spatial join rests on a spatial relationship between two geometries written as a logical predicate, a criterion that is either true or false.[1] A topological relation is a qualitative relationship that does not depend on coordinates, such as one shape being inside, overlapping, adjacent to or disjoint from another.[1] Topological predicates are commonly specified with some form of the 9-Intersection Model, which is incorporated into the Simple Feature Access specification (ISO 19125-2).[1] The Dimensionally Extended 9-Intersection Model (DE-9IM) describes how two geometries relate through a 3×3 matrix of the intersections of their interiors, boundaries and exteriors.[3] The DE-9IM was developed by Clementini and others, based on the work of Egenhofer and others.[3] A metric relation is a measurable relationship between two shapes in a coordinate space, most commonly a distance or direction, and not all software supports metric relations.[1]

The output rows of a spatial join are the pairs from the Cartesian product of the two tables for which the spatial predicate is true.[1] In DuckDB, a spatial join needs no special syntax: two tables with a GEOMETRY column are joined on a spatial predicate function such as ST_Intersects.[4] When a join condition is an arbitrary function, DuckDB normally falls back to a nested loop join, which evaluates the condition for every possible pair of rows.[4] Almost all spatial predicates imply intersection, and two geometries that intersect have intersecting bounding boxes, so a cheap bounding-box test can filter candidate pairs before the precise check.[4] DuckDB's SPATIAL_JOIN operator, introduced in DuckDB 1.3.0, builds an R-tree index over the input expected to be smaller and looks up each row of the other input in it.[4]

The haversine formula gives the great-circle distance between two points on a sphere from their latitudes and longitudes.[5] The term haversine was coined in 1835 by James Inman, as a contraction of half-versed sine.[5] In DuckDB, ST_Distance_Sphere returns the haversine (great-circle) distance between two geometries in metres, and supports only POINT geometries.[6] The haversine formula is only an approximation on the Earth, which is not a perfect sphere, and methods that consider the Earth's ellipticity, such as Vincenty's formulae, are more accurate.[5]

Compared with the alternatives

A spatial join is also similar to vector overlay operations such as Intersect and Union, but its output holds only the merged attributes, with no composite geometry.[1] Reach for an overlay when the new shapes themselves are the result, and for a spatial join when the question is about the rows and their attributes. (Meridian's view)

Sources

  1. [1]Spatial join - Wikipediasecondary source
  2. [2]13. Spatial Joins — Introduction to PostGISdocumentation
  3. [3]DE-9IM - Wikipediasecondary source
  4. [4]Spatial Joins in DuckDB – DuckDBdocumentation
  5. [5]Haversine formula - Wikipediasecondary source
  6. [6]Spatial Functions – DuckDBdocumentation

Types it works with