Join / spatial · All operations

Join to the nearest location

A variant of Join by location

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

Status

In arcform

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.
  • The statement checks its own result and raises an error when it is wrong: “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”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

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

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

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

Background

A spatial join can attach to each row its single nearest match, such as the school nearest to each student's home.[1] Nearest neighbour search (NNS) is the problem of finding the point in a given set that is closest to a given point.[2] A k-nearest-neighbour (k-NN) search, a direct generalisation of NNS, finds the k closest points.[2]

When to use it

In PostGIS, a nearest-neighbour search, unlike a distance search, sets no limit on how far away a candidate may be: a feature at any distance is accepted as long as it is the nearest.[3] Reach for a nearest join when every row needs an answer, such as the closest depot to each customer, and for a distance join when only matches within a set radius matter. (Meridian's view) In GeoPandas, sjoin_nearest takes an optional max_distance, which can significantly improve performance by reducing the number of geometries evaluated.[4] Where a match far away would be meaningless, cap the distance as well as taking the nearest. (Meridian's view)

In GeoPandas, sjoin_nearest returns several output rows for one input row when several neighbours are equally near.[4] Alteryx's Find Nearest tool can return more records than the number asked for when several records are the same distance from the target.[5] When exactly one match per row is needed, break ties with a second ordering column rather than leaving the choice to the engine. (Meridian's view) In GeoPandas, sjoin_nearest's exclusive option stops geometries equal to the input geometry from being returned as its nearest.[4] When a table is joined to itself to find each row's nearest neighbour, exclude the row's own match. (Meridian's view)

GeoPandas computes sjoin_nearest distances in the units of the coordinate reference system (CRS), and its results are inaccurate when the geometries are in a geographic CRS.[4] In PostgreSQL, a LEFT JOIN to a LATERAL subquery keeps source rows in the result even when the subquery produces no rows for them.[6] Use a left lateral join so that rows with no location stay in the result with no match, rather than silently dropping out. (Meridian's view)

The method

Donald Knuth, in volume 3 of The Art of Computer Programming (1973), called nearest neighbour search the post-office problem, after assigning a residence to its nearest post office.[2]

In PostGIS, the order-by-distance operator (<->) lets the database use an index for a sorted result, so the N nearest features come from ordering by distance and limiting the result to N rows.[3] In PostGIS, the index-assisted <-> operator works only with a single geometry literal on one side, so on its own it finds the nearest objects to one query object, not to every row of a table.[3] In PostGIS, a CROSS JOIN LATERAL feeds each row of the driving table into the ordered, limited subquery one at a time, which gives a nearest result for every row.[3] In DuckDB, the LATERAL keyword lets a subquery in the FROM clause refer to earlier subqueries, and a lateral join can be thought of as a loop over the rows of the first.[7] In PostGIS, the <-> operator on the geography type returns the sphere distance, not the more accurate spheroidal distance that ST_Distance returns.[3]

In PostGIS, a nearest-neighbour query can give correct answers without using an index, and the lack of an index may not be obvious until the tables grow.[3] Check the query plan of a nearest join before running it on a large table. (Meridian's view)

Compared with the alternatives

Choosing the nearest match is one way to resolve a spatial join with several matches per row, by selecting one row from the matching set with a spatial optimisation criterion.[1] Not all software implements a nearest-match spatial join directly, although in some cases it can be built from a combination of tools.[1] In DuckDB, an as-of join finds the nearest (first) event in a reference table, such as a price for each stock trade, and needs an inequality condition on the ordering field.[7]

Sources

  1. [1]Spatial join - Wikipediasecondary source
  2. [2]Nearest neighbor search - Wikipediasecondary source
  3. [3]29. Nearest-Neighbour Searching — Introduction to PostGISdocumentation
  4. [4]geopandas.sjoin_nearest — GeoPandas 1.2.1.dev0+g199fc53c8.d20260928 documentationdocumentation
  5. [5]Find Nearest Tooldocumentation
  6. [6]PostgreSQL: Documentation: 18: 7.2. Table Expressionsdocumentation
  7. [7]FROM and JOIN Clauses – DuckDBdocumentation