# Join to the nearest location

Join / spatial

A variant of [Join by location](https://meridian.online/operations/join-by-location).

Find, for each object, the nearest objects in another table, with their distance.

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

## Status

Runs as a SQL step.

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

## The DuckDB step

the two nearest depots to each customer, with the distance

```sql
INSTALL spatial; LOAD spatial;
SET geometry_always_xy = true;
CREATE TEMP TABLE depots AS SELECT * FROM (VALUES ('Amsterdam', 52.37, 4.90), ('Lyon', 45.76, 4.84), ('Lisbon', 38.72, -9.14)) t(depot, lat, lon);
CREATE TEMP TABLE sites AS SELECT * FROM (VALUES
  ('Alice Ng', 52.52, 13.40), ('Bob Okafor', 48.85, 2.35), ('Carla Diaz', 40.42, -3.70), ('Frank Moore', 51.51, -0.13), ('Nowhere', NULL, NULL)) t(site, lat, lon);
-- for each site, the 2 nearest depots: a lateral subquery ordered by distance with a limit; a site with no location gets no depot, but stays
CREATE TEMP TABLE result AS
SELECT s.site, n.depot, n.km
FROM sites s
LEFT JOIN LATERAL (
  SELECT d.depot, ST_Distance_Sphere(ST_Point(s.lon, s.lat), ST_Point(d.lon, d.lat)) / 1000 AS km
  FROM depots d WHERE km IS NOT NULL ORDER BY km LIMIT 2) n ON true;
SELECT site, depot, round(km, 1) AS km FROM result ORDER BY site, km;
SELECT CASE WHEN ((SELECT count(*) = 9 AND count(*) FILTER (WHERE depot IS NULL) = 1 AND min(site) FILTER (WHERE depot IS NULL) = 'Nowhere' FROM result)
  AND (SELECT list(depot ORDER BY km) FILTER (WHERE site = 'Alice Ng') = ['Amsterdam', 'Lyon']
       AND list(depot ORDER BY km) FILTER (WHERE site = 'Bob Okafor') = ['Lyon', 'Amsterdam']
       AND list(depot ORDER BY km) FILTER (WHERE site = 'Carla Diaz') = ['Lisbon', 'Lyon']
       AND list(depot ORDER BY km) FILTER (WHERE site = 'Frank Moore') = ['Amsterdam', 'Lyon'] FROM result)
  AND (SELECT min(km) FILTER (WHERE site = 'Frank Moore') BETWEEN 350 AND 365 AND min(km) FILTER (WHERE site = 'Bob Okafor') BETWEEN 385 AND 398 FROM result)) THEN 'as expected' ELSE error('each site with a location should list its 2 nearest depots in distance order (Frank Moore: Amsterdam about 358 km, then Lyon), and the site with no location should stay with no depot') END AS verdict;
```

The statement builds its own data.

It needs the `spatial` extension.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Alteryx Designer: [`Find Nearest`](https://help.alteryx.com/current/en/designer/tools/spatial/find-nearest-tool.html) (read 2026-09-27)