Adding height from nearest XYZ point to geom table in PostGIS?

Question

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.

Answer 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?.