maintain a postgresql database using osm2pgsql

[apollo]

hi there good day dear Python-friends,


I am trying to maintain a postgresql database using osm2pgsql to maintain my database.

idea: I want to track the changes made by the daily replication files. do i need to create triggers that fired after delete, update and insert. should i do a daily update process.

the idea: having a 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.
- gasoline stations and so on and so forth

Well 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, gasoline stations')

well - how do you think about this approach? Is is a good method do do so!?

By looking at the results, it just extracts some of the existing hospitals. i am pretty sure that there are much more 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.

Question: How can I include all of these buildings?

assumption: i guess that i am missing those hospitals mapped as areas. We have to run the same query on planet_osm_polygon as well, or we could construct a "union" query: This would come with alot of benefits for the request: That gives us the centrepoints of polygons in addition to the points we have above.

i guess that we need to use the amenity = 'hospital' from the polygon table and union all with the points. The benefit: it would gives a few more hospitals. The next step needs to be to find out the node, way or relation ID of a hospital that we're missing and query our database for it.


btw: can i do this with Python - working on the Overpass API

what about the query OSM data with the Overpass API, but how can we use this data now? a idea and a method to download the data is by using the command line tools curl or wget. In order to do this we need to access one of the Overpass API endpoints, where the one we will look go by the format http://overpass-api.de/api/interpreter?data=query. When using curl we can download the OSM XML of our query by running the command

curl --globoff -o output.xml http://overpass-api.de/api/interpreter?data=node(1);out;

well - the previously crafted query comes after data= and the query needs to be urlencoded. The --globoff is important in order to use square and curly brackets without being interpreted by curl. This query returns the following XML result

<?xml version="1.0" encoding="UTF-8"?>
<osm version="0.6" generator="Overpass API 0.7.54.13 ff15392f">
<note>The data included in this document is from www.openstreetmap.org. 
      The data is made available under ODbL.</note>
<meta osm_base="2018-02-24T21:09:02Z"/>  <node id="1" lat="61.4779481" lon="-0.0014863">
    <tag k="historic" v="memorial"/>
    <tag k="memorial" v="stone"/>
    <tag k="name" v="Gasoline-station at the corner"/>
  </node></osm>

regarding the methods to get the data which method is better and more appropiate?

regarding the formats: well we have to say: there are various output formats to choose from in the documentation. In order to download the query result as JSON we need to add [out:json]; to the beginning of our query as in the command:

curl --globoff - o output.json http://overpass-api.de/api/interpreter?data=[out:json];node(1);out;

...giving us the previous XML result in JSON format. You can test the query also in the browser by accessing

http://overpass-api.de/api/interpreter?data=[out:json];node(1);out;

which way would you go?

look forward to hear from you

[Larz60+]

if you use SqlAlchemy, rather than osm2pgsql, you can easily tract all transactions.
see: https://docs.sqlalchemy.org/en/13/orm/se...vents.html
I have a short tutorial on this forum which shows the basics for SqlAlchemy here: https://python-forum.io/Thread-SqlAlchem...-Data-Load

the tutorial is for sqlite, but you should be able to use PostgreSQL with a one line change to line 17 of the database model.
It's well worth the small learning curve.