Python Forum
maintain a postgresql database using osm2pgsql
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
maintain a postgresql database using osm2pgsql
#1
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
Wordpress - super toolkits a. http://wpgear.org/ :: und b. https://github.com/miziomon/awesome-wordpress :: Awesome WordPress: A curated list of amazingly awesome WordPress resources and awesome python things https://github.com/vinta/awesome-python
Reply
#2
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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to Connect to PostgreSQL Through Jump Server and SSH Tunnel using Python? nishans 1 629 Jan-02-2024, 10:37 AM
Last Post: khanzain
  PostgreSQL psycopg2.errors.DuplicateColumn: column specified more than once rajnish_nationfirst 2 3,622 Jun-21-2020, 08:17 AM
Last Post: ibreeden
  StopIteration exception when mock PostgreSQL connection in several tests igor87z 1 2,834 Jun-10-2020, 06:16 PM
Last Post: ibreeden
  Python and Postgresql syntax select statement Nesreenmhd 1 4,885 Sep-07-2019, 06:08 PM
Last Post: ndc85430
  Create table with psycopg2 on postgreSQL DB yhecohen 2 3,229 Aug-23-2019, 05:56 AM
Last Post: massimo_m
  Error while fetching data from PostgreSQL linu 3 4,165 May-13-2019, 02:38 PM
Last Post: rxndy
  Return Row ID postgresql takaa 1 3,588 Nov-24-2017, 03:32 AM
Last Post: Larz60+
  PostgreSQL- import package elhetch 2 4,651 Jun-07-2017, 02:07 PM
Last Post: snippsat
  error while inserting values into a table from form in flask in postgreSQL sahilsiddharth 3 7,023 Jun-05-2017, 07:49 PM
Last Post: sahilsiddharth
  Nginx Setup Django Postgresql Eclipse Adelton 3 4,036 Mar-27-2017, 09:40 PM
Last Post: snippsat

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020