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