OSM postgresql query does not show all hospitals?

Question

Hi,

I have OSM dataset on Postgresql via osm2pgsql. Now I am trying to query all public buildings in UK such as hospitals, schools, fire stations and churches. In order to do that, I use something like this query:

SELECT *
FROM planet_osm_point pop
WHERE pop.amenity IN ('hospital','fire_station','place_of_worship','school')

By looking at the results, it just extracts some of the hospitals. I can see the red plus logo for hospitals that I also know that they exist there. But it does not show them in the query results.

How can I include all of these buildings?

Here is the example picture: https://imgur.com/a/jjTlZ4N

Blue color is from the results. But the red circle hsopitals are not included in the query results.

Answer 1

0

The problem likely is that you're missing those hospitals mapped as areas. You have to run the same query on planet_osm_polygon as well, or you could construct a "union" query that gives you the centrepoints of polygons in addition to the points.

permanent link

answered 24 Apr '19, 10:32

Frederik Ramm ♦
82.5k●92●720●1273
accept rate: 23%

Thank you for your answer. I tried this. I got the amenity = 'hospital' from polygon table and unioned all with the points. It only gives a few more hospitals. Still, I see many red plus signs that are not included in the query result.

(24 Apr '19, 10:41) sypolt

2

Your next step needs to be to find out the node, way or relation ID of a hospital that you're missing and query your database for it. If you can give an example object ID that you are missing people will be able to offer more help.

(24 Apr '19, 11:25) SomeoneElse ♦

Thank you. May I ask how I can find node/way id? For example, I know this particular hospital does not appear in the results: "St thomas hospital, London". So I queried the points which have the name of "%St Thomas%". And there are results for surrounding bus stops and bike parking around the hospital. But still, there is no results for the hospital.

(24 Apr '19, 11:57) sypolt

This would be way 122227536. This information can be obtained by using the search function or the query features tool (button 11 at Browsing).

(24 Apr '19, 12:11) scai ♦

As an example, here's what I did to check that it was in a local database:

psql -c "select osm_id,name from planet_osm_polygon where osm_id = '122227536'" gis

I got back:

  osm_id   |                              name
-----------+-----------------------------------------------------------------
 122227536 | St Thomas' Hospital (Guy's and St Thomas' NHS Foundation Trust)
(1 row)

(24 Apr '19, 12:47) SomeoneElse ♦

Thank you for providing the osm_id. I found this osm id only in "planet_osm_ways" table. And this table has these columns: nodes, tags, pending. And Pending = True for this hospital. What does that mean? Do I use an older version of OSM maybe?

(24 Apr '19, 13:11) sypolt

@sypolt I think you need to tell us what you actually did. Did the import process actually complete?

(24 Apr '19, 13:14) SomeoneElse ♦

Yes. So I downloaded this dataset: download.geofabrik.de/europe/british-isles.html. and then used osm2pgsql to import to database. The process was completed. I did this task a long time ago.

(24 Apr '19, 13:19) sypolt

showing 5 of 8 show 3 more comments

OSM postgresql query does not show all hospitals?

Follow this question

Related questions