Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

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

thanks for any help

share|improve this question
    
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
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.