• 0

maintain a postgresql database using osm2pgsql


Question

hi there good day dear 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

Edited by tarifa

1 answer to this question

Recommended Posts

  • 0


update: i have found a good and solid manual that describes to get started with open-street-map-to-postgis-the-basics

 

https://www.cybertec-postgresql.com/en/open-street-map-to-postgis-the-basics/

  Quote

OSM to PostGIS – The Basics
Ever wondered how to import OSM (OpenStreetMap) data into PostGIS [1] for the purpose of visualization and further analytics? Here are the basic steps to do so. There are a bunch of tools on the market— osm2pgsql; imposm; ogr2org; just to mention some of those. In this article I will focus on osm2pgsql [2].

Let’s start with the software prerequisites. PostGIS comes as a PostgreSQL database extension, which must be installed in addition to the core database. Up till now, the latest PostGIS version is 3, which was released some days ago. For the current tasks I utilized PostGIS 2.5 on top of PostgreSQL 11.
This brings me to the basic requirements for the import – PostgreSQL >= 9.4 and PostGIS 2.2 are required, even though I recommend installing PostGIS >=2.5 on your database;  it’s supported from 9.4 upwards. Please consult PostGIS’ overall compatibility and support matrix [3] to find a matching pair of components.

Osm2pgsql Setup
Let’s start by setting up osm2pgsql on the OS of your choice – I stick to Ubuntu 18.04.04 Bionic Beaver and compiled osm2gsql from source to get the latest updates.

Install required libraries

Expand  
sudo apt-get install make cmake g++ libboost-dev libboost-system-dev \
libboost-filesystem-dev libexpat1-dev zlib1g-dev \
libbz2-dev libpq-dev libproj-dev lua5.2 liblua5.2-dev
Grab the repo

git clone https://github.com/openstreetmap/osm2pgsql.git
Compile

mkdir build && cd build
cmake ..
make
sudo make install
If everything went fine, I suggest checking the resulting binary and its release by executing

./osm2pgsql-version. 
osm2pgsql version 1.0.0 (64 bit id space)
[/CODE]

 

[B]Data acquisition[/B]In the world of OSM, data acquisition is a topic of its own, and worth writing a separate post discussing different acquisition strategies depending on business needs, spatial extent and update frequency. I won’t get into details here, instead, I’ll just grab my osm data for my preferred area directly from Geofabrik, a company offering data extracts and related daily updates for various regions of the world. This can be very handy when you are just interested in a subregion and therefore don’t want to deal with splitting the whole planet osm depending on your area of interest – even though osm2pgsql offers the possibility to hand over a bounding box as a spatial mask. As a side note – osm data’s features are delivered as lon/lat by default.

So let’s get your hands dirty and fetch a pbf of your preferred area from Geofabrik’s download servers [4] [5]. For a quick start, I recommend downloading a dataset covering a small area:

[CODE]wget https://download.geofabrik.de/europe/iceland-latest.osm.pbf[/CODE]

[/QUOTE]

 

here is another guide - the Part 1: [B]Loading OpenStreetMap data into PostGIS: An Almost Idiot's Guide[/B]
[url]https://www.bostongis.com/PrinterFriendly.aspx?content_name=loading_osm_postgis[/url]

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Weekend PC Game Deals: Automation fests, Civilization for free, charity specials, and more by Pulasthi Ariyasinghe Weekend PC Game Deals is where the hottest gaming deals from all over the internet are gathered into one place every week for your consumption. So kick back, relax, and hold on to your wallets. The Epic Games Store unlocked a big strategy game giveaway earlier this week: Civilization VI: Platinum Edition. Coming in from Firaxis Games, the turn-based 4X experience has you starting world-conquering campaigns to explore, expand, exploit, and exterminate everything in your empire's reach. PvP and co-op multiplayer are also options if the various forms of AI prove to be too easy or even too troublesome. The Sid Meier’s Civilization VI: Platinum Edition giveaway is live until July 24, and it comes with two massive expansions as well as six DLC packs with extra scenarios, leaders, and more. Next week, tower defense title Legion TD 2 will become the latest freebie on the Epic Games Store. The Humble Store brought a new bundle for action game fans this weekend, and it's all about the Devil May Cry franchise. The Devil Trigger Collection begins with DmC: Devil May Cry and Devil May Cry HD Collection for $10. If you want to complete the bundle, it will set you back $20, which gets you Devil May Cry 4 Special Edition as well as the most recent entry, Devil May Cry 5, as well as its Vergil DLC. This bundle has two weeks left on its counter before it goes away. Big Deals Most publishers are returning to their usual weekend specials after the massive summer sales, so there are plenty of discounts to go around. There's even a special Make a Wish charity sale running on Steam with some discounted viral hits. With all those and more, here's our hand-picked big deals list for the weekend: Satisfactory – $27.99 on Steam Captain of Industry – $24.49 on Steam No Man's Sky – $23.99 on Steam Persona 5 Royal – $23.99 on Steam No More Room in Hell 2 – $22.49 on Steam FOUNDRY – $20.99 on Steam Banishers: Ghosts of New Eden – $19.99 on Steam SULFUR – $19.99 on Steam Assassin's Creed Mirage – $19.99 on Steam Alan Wake 2 – $19.99 on Epic Store Grand Theft Auto V Enhanced – $19.79 on Steam Norland – $19.49 on Steam Stray – $17.99 on Steam V Rising – $17.49 on Steam Dyson Sphere Program – $15.99 on Steam The Outlast Trials – $15.99 on Steam Warhammer 40,000: Darktide – $15.99 on Steam The Outlast Trials – $15.99 on Steam Red Dead Redemption 2 – $14.99 on Steam Turing Complete – $13.99 on Steam Eden Crafters – $13.99 on Steam Core Keeper – $13.99 on Steam Thank Goodness You're Here! – $12.99 on Steam Opus Magnum – $9.99 on Steam Autonauts – $9.99 on Steam EXAPUNKS – $9.99 on Steam DRAGON BALL XENOVERSE 2 – $9.99 on Steam Superliminal – $9.99 on Steam Heaven's Vault – $9.99 on Steam RAILGRADE – $9.89 on Steam Goat Simulator 3 – $9.89 on Steam Tchia – $9.89 on Steam ACE COMBAT 7: SKIES UNKNOWN – $9.59 on Steam PAYDAY 3 – $8.99 on Steam Assassin's Creed Origins – $8.99 on Steam Viewfinder – $8.74 on Steam Escape Academy – $7.99 on Steam Pit People – $7.99 on Steam Skull and Bones – $7.99 on Steam Immortals Fenyx Rising – $7.99 on Steam Imperator: Rome – $7.59 on Steam SHENZHEN I/O – $7.49 on Steam Tom Clancy’s The Division 2 – $7.49 on Steam Bassmaster Fishing – $7.49 on Steam Let's Build a Zoo – $6.99 on Steam The Forgotten City – $6.24 on Steam Control Ultimate Edition – $5.99 on Steam Bramble: The Mountain King – $5.99 on Steam Assassin’s Creed Rogue – $5.99 on Steam RoboCop: Rogue City – $4.99 on Steam Kingdom Two Crowns – $4.99 on Steam Scott Pilgrim vs. The World: The Game – $4.94 on Steam Castle Crashers – $4.49 on Steam BattleBlock Theater – $4.49 on Steam TOEM: A Photo Adventure – $3.99 on Steam Supraland – $3.99 on Steam Vampire Survivors – $3.99 on Steam Darkwood – $3.74 on Steam Valiant Hearts: The Great War – $3.74 on Steam TIS-100 – $3.49 on Steam PAYDAY 2 – $3.29 on Steam Cake Bash – $2.99 on Steam Ragnarock – $1.99 on Steam Alan Wake – $1.49 on Steam Civilization VI Platinum Edition – $0 on Epic Store DRM-free Specials Lastly, here are some highlights from the DRM-free discounts available on the GOG store this weekend: Age of Wonders 4 - $29.99 on GOG Pathfinder: Wrath of the Righteous - Game of the Year Edition - $19.99 on GOG Tomb Raider IV-VI Remastered - $19.49 on GOG The Thaumaturge - $19.24 on GOG Chained Echoes - $13.74 on GOG Tyranny - Gold Edition - $12.49 on GOG Tomb Raider I-III Remastered Starring Lara Croft - $11.99 on GOG Baldur's Gate: Enhanced Edition - $9.99 on GOG Baldur's Gate II: Enhanced Edition - $9.99 on GOG Neverwinter Nights: Enhanced Edition - $9.99 on GOG Old World - $9.99 on GOG Icewind Dale: Enhanced Edition - $9.99 on GOG Neverwinter Nights: Doom of Icewind Dale - $7.99 on GOG Kingdom Come: Deliverance - $5.99 on GOG Might and Magic 6-pack Limited Edition - $4.99 on GOG Heroes of Might and Magic 3: Complete - $4.99 on GOG Blood Omen: Legacy of Kain - $3.49 on GOG Might and Magic 8: Day of the Destroyer™ - $2.99 on GOG Worms Armageddon - $2.99 on GOG ATOM RPG: Post-apocalyptic indie game - $2.99 on GOG Keep in mind that availability and pricing for some deals could vary depending on the region. That's it for our pick of this weekend's PC game deals, and hopefully, some of you have enough self-restraint not to keep adding to your ever-growing backlogs. As always, there are an enormous number of other deals ready and waiting all over the interwebs, as well as on services you may already subscribe to if you comb through them, so keep your eyes open for those, and have a great weekend.
    • Wild that this was even allowed from the jump
    • Microsoft stops using China-based engineers to support US defense clients by Hamid Ganji Microsoft announced on Friday that its China-based engineers can no longer provide technical support to the US military and other defence clients using the company's cloud services. Frank Shaw, Microsoft's Chief Communications Officer, wrote on X, "In response to concerns raised earlier this week about US-supervised foreign engineers, Microsoft has made changes to our support for US Government customers to assure that no China-based engineering teams are providing technical assistance for DoD Government cloud and related services." The issue came into the spotlight after a report by ProPublica detailed how Microsoft's Azure engineers in China are providing technical support to the US defense clients. Microsoft had apparently told ProPublica that its engineers and contractors complied with US government laws. These China-based engineers are reportedly supervised through so-called "digital escorts" in the US, who are allegedly less technically qualified than the engineers and can not determine whether the Chinese engineers under their supervision pose a cyber threat to the United States. On Friday, Senator Tom Cotton sent a letter to Defense Secretary Pete Hegseth, demanding explanations about how these "digital escorts" are trained to detect threats, as well as the list of contractors that use Chinese personnel. "The US government recognizes that China's cyber capabilities pose one of the most aggressive and dangerous threats to the United States, as evidenced by infiltration of our critical infrastructure, telecommunications networks, and supply chains," Cotton wrote. In a video posted on X, Hegseth said this is "obviously unacceptable" and that he's issuing a two-week review of Pentagon cloud deals to ensure that "China will no longer have any involvement whatsoever in our cloud services, effective immediately." The US Defense Secretary also said the current controversy is due to "A legacy system created over a decade ago, during the Obama administration." Microsoft, Amazon, Google, and Oracle jointly received a $9 billion Department of Defense cloud services contract in 2022.
    • This morning down to 17% after 8 days.
    • Cutcon 4.0 by Razvan Serea Cutcon is a free, open-source desktop app for cutting, converting, and previewing video, audio, and image files. Built with Kotlin and powered by FFmpeg, it's fast, lightweight, and easy to use. Ideal for content creators, editors, and developers, Cutcon supports a wide range of media formats and offers a clean user interface. Originally forked from the Clipper project, it enhances performance and usability while remaining fully cross-platform. Whether trimming videos or converting audio formats, Cutcon streamlines your workflow without compromising quality. Perfect for those seeking a simple yet powerful media processing tool. Cutcon offers three core functions that make working with media files fast and simple: Cut – Remove unwanted sections from video, audio, or image files without re-encoding. Keep only what you need. Convert – Change files from one format to another using FFmpeg. Supports a wide range of formats for video, audio, and images. Preview – Play media directly inside the app before cutting or converting. Quickly verify content without opening another program. Cutcon 4 release notes: This release rebases Cutcon on Clipper v1.20.0 bringing support for Linux and macOS and updating the app branding. Also, there is now only one variant of the app with support for most common media types. New features Add support for Linux operating system (common distros) Add support for macOS operating system (experimental) Add support for playing FLAC audio format Add support for playing Opus audio format Improvements Update app brand (logo, colors) Improve media player to hugely boost its performance Use a fake file for the app initial input source Make theme colors change with animation Update the app error window layout Misc Change log file directory to user home Several improvements in app code Download: Cutcon 4.0 | 89.8 MB (Open Source) View: Cutcon Website | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
  • Recent Achievements

    • First Post
      artistro08 earned a badge
      First Post
    • First Post
      paul29 earned a badge
      First Post
    • Dedicated
      Homeless Vagrant earned a badge
      Dedicated
    • One Month Later
      Coolray5432 earned a badge
      One Month Later
    • Week One Done
      Coolray5432 earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      498
    2. 2
      ATLien_0
      223
    3. 3
      Michael Scrip
      196
    4. 4
      Xenon
      161
    5. 5
      +FloatingFatMan
      138
  • Tell a friend

    Love Neowin? Tell a friend!