Hello,

I have downloaded a PBF file for Europe, and loaded it to a PostGIS database (well, it's still ongoing actually after 5 days but hopefully it'll be done soon).

In a separate PostGIS table, I have a 3.5 million point pairs which represent the start and end of road sections. These aren't from OSM.

What I would like to do is to find the average building height and/or number of levels in a bounding box of each point pair. I think I need to do something like this, but I'm unfamiliar with the structure of the OSM data and hope someone can help.

SELECT     points_table.id,
           AVG(buildings.building:height)
FROM       points_table
LEFT JOIN  (SELECT building:height AS height,
                   building:levels AS levels,
                   the_geom 
            FROM   osm_polygon
            WHERE  building:height IS NOT NULL
            OR     building:levels IS NOT NULL) AS buildings
ON         st_intersects(
           st_buffer(st_makeline(points_table.point_a, points_table.pointb),100), 
           buildings.the_geom
                         )
GROUP BY  points_table.id

Thanks,

James

asked 18 Apr '18, 12:35

TheRealJimShady's gravatar image

TheRealJimShady
16225
accept rate: 0%

edited 18 Apr '18, 13:01

1

Have you enabled the hstore option in osm2pgsql? Otherwise you won't have all the tags (such as building heights), just the default subset.

(18 Apr '18, 13:29) Richard ♦
1

Hello Richard. Thanks for the reply. I did this...

osm2pgsql -d osm_europe --style openstreetmap-carto/openstreetmap-carto.style -U james -H localhost --hstore --slim -G europe-latest.osm.pbf

Hopefully this looks ok - it includes the hstore option.

(18 Apr '18, 14:54) TheRealJimShady

You don't say how you are loading the data into a PostGIS database; there are several ways (osm2pgsql, osmosis, imposm, ogr2ogr in descending order of popularity) and each will result in different data schemas. Assuming that you have used osm2pgsql, your buildings will be in planet_osm_polygons, and the building heights will only be available, in the tags column, if you have imported with --hstore.

To ease further processing, I would then create a table with all building centroids of buildings that have a height, like this

SELECT ST_CENTROID(way) geom, tags->'height' as height
INTO mybuildings
FROM planet_osm_polygon
WHERE building is not null and tags?'height';

Ideally you would also want to convert the height value to a floating point value during this step, however simply casting it will fail as soon as someone has added a non-numeric value to the field. People typically use a regular expression match for that:

SELECT ST_CENTROID(way) geom, (tags->'height')::numeric as height
INTO mybuildings
FROM planet_osm_polygon
WHERE building is not null and tags->'height' ~ '^\s*[0-9]+(\.[0-9]+)?\s*$';

Next, you want a spatial index on your new table

CREATE INDEX foo ON mybuildings USING gist(geom);

Now, you'll proceed almost as planned, but be sure to use ST_DWITHIN since it can benefit from the index whereas the ST_CONTAINS/ST_BUFFER construct cannot:

SELECT     points_table.id,
           AVG(mybuildings.height)
FROM       points_table
LEFT JOIN  mybuildings
ON         ST_DWITHIN(st_makeline(point_a, pointb), geom, 100)
GROUP BY  points_table.id;
permanent link

answered 18 Apr '18, 14:42

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
70.9k836431106
accept rate: 24%

Hello Frederik. I've loaded the data like this...:

osm2pgsql -d osm_europe --style openstreetmap-carto/openstreetmap-carto.style -U james -H localhost --hstore --slim -G europe-latest.osm.pbf

So once it has finished loading, I should be good to go I think? Thank you for your inspiration on the SQL too, much appreciated.

(18 Apr '18, 14:56) TheRealJimShady
1

Yes, that looks ok.

(18 Apr '18, 15:23) Frederik Ramm ♦

Great, thanks for your help. One final thing, the log of my import to PostGIS is still running. It says the below. Has it almost finished?

Committing transaction for planet_osm_point

Committing transaction for planet_osm_line

Committing transaction for planet_osm_polygon

Committing transaction for planet_osm_roads


Going over pending ways...

    186420914 ways are pending


Using 1 helper-processes


processing way (39917k) at 0.45k/s

--- skipped 46804 bytes ---

processing way (71218k) at 0.51k/s
(18 Apr '18, 15:34) TheRealJimShady
1

From these numbers, the "going over pending ways" stage is likely to continue for ~ 3 days, after which the index building will take another couple of days. If you order an SSD on the Internet today, get it delivered on Friday, shut down your machine, add the SSD, and restart the whole import, it is likely to finish quicker ;)

(18 Apr '18, 15:41) Frederik Ramm ♦

Haha. Well yes. I'm using a virtual machine ran by our IT team though. Not much control over it. You've made me a tad worried about disk space now. I'm at 93%. I've got about 50GB of space left. Index's won't take that much up will they?

(18 Apr '18, 16:09) TheRealJimShady

I'm afraid they will, a full Europe import with indexes will likely take round about 400 GB. You can save some of that (and some processing time) if you import with --drop in addition to --slim which will get rid of some temporary tables after import, at the expense of not being able to run updates. If your machine has a ton of RAM (about 96 GB or so) you could perhaps even manage without --slim which would need a lot less disk space (just about 200 GB) but would not be updatable either.

(18 Apr '18, 16:37) Frederik Ramm ♦
showing 5 of 6 show 1 more comments
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×146
×121
×55
×10
×9

question asked: 18 Apr '18, 12:35

question was seen: 1,262 times

last updated: 18 Apr '18, 16:37

powered by OSQA