1

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;
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.

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

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.