Finding the closest polygons to point in PostGIS

Question

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.

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;

Answer 1

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.

Answer 2

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.

Answer 3

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