3

I have been trying to get this query right and just can't seem to.

I have a building polygon table with over 7 million polygons, indexed. I then have a gridded height XYZ table with attribute 'height' spaced at 50m, which is also indexed.

I want to create a new table by selecting the closest height point to the centroid of the polygon and take the height attribute and add it to the building.

The query I was using at the moment is:

CREATE TABLE buildingheight AS 
    SELECT DISTINCT ON (b.ogc_fid) b.ogc_fid, b.ID, b.wkb_geometry, h.height 
    FROM building b JOIN xy_height h ON ST_DWithin(b.wkb_geometry, h.geom, 50) 
    ORDER BY b.ogc_fid, ST_Distance(b.wkb_geometry, h.geom);

I tested this on a sample of 400 buildings but only a certain number gained the height attribute.

This was a hash of code I found from some other posts.

I have also tried to use the ST_ClosestPoint:

CREATE TABLE buildingheight2 AS 
    SELECT b.ogc_fid, b.ID, b.wkb_geoemtry, h.height 
    FROM vmd.building b, gb_xy_height h 
         ON ST_ClosestPoint(h.geom, ST_Centroid(b.wkb_geoemtry) LIMIT 1;

And also the new nearest neighbour posted on: http://blog.opengeo.org/tag/knn/

I am surprised no one else has done this as I thought with all the LiDAR data, that this query would be straightforward.

  • I think your first query looks right. Are you sure your height table covers all buildings. Have you anylized the buildings that don't get height values? – Nicklas Avén May 6 '12 at 17:34
  • Hi Nicklaus the geometry of the building exists for all 400 but only some have a height. I am running the query on the full tables but it ran for 14 hrs and then crashed as it filled 32gb worth of hard drive space on my laptop. This makes me think it's definitely wrong. – tjmgis May 6 '12 at 20:10
  • Yes, that sounds strange. But take a closer look at those that doesn't get a height of the 400. Is there really a value within 50 meters from those buildings in the xy_height table? – Nicklas Avén May 6 '12 at 20:52
  • Another thought, in what srid are your data? Is it really meter-based. – Nicklas Avén May 6 '12 at 20:54
  • 1
    This works CREATE TABLE building_height AS SELECT geom, (SELECT h.height FROM terrain h ORDER BY b.geom <-> h.geom LIMIT 1) FROM building b; – tjmgis Sep 26 '13 at 14:36
1

As commented by OP a solution is to use the 'distance' operator (<->) from pg_trgm module. It will perform a similarity search and will work with a GiST index (like an index-assisted nearest neighbour searching).

CREATE EXTENSION pg_trgm;

CREATE TABLE building_height AS 
   SELECT b.geom, (SELECT h.height 
                   FROM terrain h 
                   ORDER BY b.geom <-> h.geom
                   LIMIT 1) 
   FROM building b;

For further reference, take a look at How do I find the N nearest things to this point in PostGIS?.

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.