# Join by location

Join / spatial

join on spatial relationship, distance, containment

Page: <https://meridian.online/operations/join-by-location>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today, with the `spatial` 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.

## Variants

- [Join to the nearest location](https://meridian.online/operations/join-by-location/join-to-the-nearest-location): Find, for each object, the nearest objects in another table, with their distance.
- 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: [`Find Nearest`](https://help.alteryx.com/current/en/designer/tools/spatial/find-nearest-tool.html) (read 2026-09-27)
- Alteryx Designer: [`Spatial Match`](https://help.alteryx.com/current/en/designer/tools/spatial/spatial-match-tool.html) (read 2026-09-27)
- Duckle: [`Spatial Join`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)
- Tableau Prep: [`Spatial join (Intersects)`](https://help.tableau.com/current/prep/en-us/prep_spatial_calculations_and_joins.htm) (read 2026-09-27)