# How to find the closest polygons to a point

I am trying to the find the four closest building polygons to a junction point layer. I understand how to obtain this for a single point but am unsure of how to specify the four closest polygons for every point in my layer.

CODE:

``````DROP TABLE IF EXISTS corner_buildings;

CREATE TABLE corner_buildings AS
SELECT buildings.geom AS buildgeom,
nodes.geom AS nodegeom
FROM buildings, nodes
WHERE nodes.objectid = 1
AND ST_DWithin(buildings.geom, nodes.geom, 200)
ORDER BY st_distance(buildings.geom, nodes.geom)
LIMIT 4;
``````

An option is to use a `lateral join` and to fetch the node table twice.

The main query gets all source ID, then, for each of them, the subquery finds the 4 closest points:

``````SELECT n.objectid, near.buildgeom, near.nodegeom
FROM node n,
LATERAL (
SELECT buildings.geom AS buildgeom, nodes.geom AS nodegeom
FROM buildings, nodes
WHERE nodes.objectid=n.objectid AND st_Dwithin(buildings.geom, nodes.geom, 200)
ORDER BY st_distance(buildings.geom, nodes.geom)
LIMIT 4
) near;
``````

Note that you may want to include a few more fields - other than the unreliable objectid - to be able to mach the found geometries to their original node. You will also want to add a new primary key.

• This is exactly what I was looking for. Thank you. – Daykray Feb 25 '19 at 19:44

Window functions are particularly useful in this case. Here's a solution making use of `row_number() over()` :

``````select t.*
from ( select b.geom as buildgeom, n.geom as nodegeom,
row_number() over(order by st_distance(b.geom, n.geom)) as proximity
from buildings b, nodes n
where n.objectid=1 AND st_Dwithin(b.geom, n.geom, 200)
) t
where t.proximity <= 4 ;
``````

The trick is to do it in two steps, with embedded queries: the first step enumerates the building according to the distance to a node, while the second one filters out the excess cases.

I suggest doing an efficient KNN neighborhood query for each candidate building in the lateral join (which is a simplified variant of JGH's suggestion).

``````DROP TABLE IF EXISTS corner_buildings;

CREATE TABLE corner_buildings AS
SELECT near.buildgeom, near.nodegeom
FROM nodes
CROSS JOIN LATERAL
(SELECT buildings.geom AS buildgeom, nodes.geom AS nodegeom
FROM buildings
ORDER BY buildings.geom <-> nodes.geom
LIMIT 4) AS near;

CREATE INDEX buildings_geom_ix ON buildings USING GIST(geom);
``````

And if you expect duplicate node geometries remove them using this instead `FROM nodes`:

``````FROM (SELECT DISTINCT ON (geom) * FROM nodes WHERE objectid IS NOT NULL) AS nodes
``````