I have been trying to get this query right and just can' seem to.
I have a building polygon table with over 7 million polygons, indexed
I then have a grided 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 it
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 here
I am surprised no one else has done this as I thought with all the LiDAR data that this query would be straight forward.
thanks for any help