A Guide to Updating OpenStreetMap (OSM) Data in PostgreSQL/PostGIS Database Using osm2pgsql

Introduction

OpenStreetMap (OSM) is a collaborative project that provides free and editable map data for the world. This data can be used to build various applications, such as web and mobile map applications, routing software, and location-based services. This article will discuss how to update OpenStreetMap data in a PostgreSQL/PostGIS database using the osm2pgsql tool.

Getting the OSM Data

You can download updated data from OpenStreetMap (OSM) through the following options:

1. The OSM website: You can visit the OSM website (https://www.openstreetmap.org/) and use the “Export” feature to download data for a specific area.
2. Overpass API: You can use the Overpass API (https://overpass-api.de/) to query the OSM database and download data in a specific format (e.g., XML or JSON).
3. Geofabrik: You can visit the Geofabrik website (https://www.geofabrik.de/) and download pre-processed OSM data in a variety of formats (e.g., Shapefile, KML).
4. OSM mirrors: There are also a number of OSM mirrors that allow you to download OSM data, such as Geofabrik and Cloudmade.
5. OSM2World: You can use OSM2World (https://osm2world.org/) to convert OSM data into 3D models.

It’s important to note that the OSM database is constantly being updated by thousands of users around the world, so the data you download will not be “real-time.” However, most of these options will allow you to download relatively recent data.

There are several ways to get OpenStreetMap data, but the most common method is to download a PBF file from the OSM website. The PBF file is a binary format that is more compact and efficient to process than the XML format.

To download the PBF file, go to the following URL: Index of / . The PBF file is compressed, so you will need to use a decompression tool such as 7-Zip to extract the file.

Creating a PostgreSQL/PostGIS Database: To update the OpenStreetMap data, you will need to set up a PostgreSQL/PostGIS database. You can use the following steps to create a database:

1. Install PostgreSQL and PostGIS on your server.
2. Create a new database using the following command:createdb osm_planet
3. Enable PostGIS extensions in the database using the following command:psql -d osm_planet -c "CREATE EXTENSION postgis;"psql -d osm_planet -c "CREATE EXTENSION hstore;"

Importing OSM Data Using osm2pgsql

Once you have the PBF file and the PostgreSQL/PostGIS database set up, you can use the osm2pgsql tool to import the data into the database. Here are the steps to follow:

1. Download the osm2pgsql tool from the following URL: Installation – osm2pgsql .
2. Run the following command to import the data:osm2pgsql -s -H <host> -U <user> -d <database> --hstore -E 4326 <path-to-pbf-file>Here are the parameters:
   1. -s: slim mode, which creates a smaller database by removing some data that is not commonly used.
   2. -H: the database host.
   3. -U: the database user.
   4. -d: the database name.
   5. –hstore: enables the hstore extension for storing additional key-value pairs in the database.
   6. -E: the SRID (Spatial Reference ID) for the database. In this case, we are using WGS84 (EPSG:4326). <path-to-pbf-file>: the full path to the PBF file.
3. Depending on the size of the PBF file, the import process may take several hours. Once the process is complete, you can use the following command to test the database:psql -d <database> -c "SELECT COUNT(*) FROM planet_osm_point;"This command counts the number of points in the database. You can replace “planet_osm_point” with other tables, such as “planet_osm_line” and “planet_osm_polygon,” to count the number of lines and polygons, respectively.

Updating OSM Data

To update the OpenStreetMap data in the database, you will need to download the latest PBF file from the OSM website and re-import the data using osm2pgsql.

Import OSM Data Using Script:

• Prerequisites

Before running this script, make sure you have the following software installed on your local machine/server:

• PostgreSQL/PostGIS database (You can follow the instructions in [this tutorial](https://www.sqlshack.com/setting-up-a-postgresql-database-on-mac/) to install PostgreSQL/PostGIS on a Mac.)
• osm2pgsql (Download the osm2pgsql tool from the following URL: Installation – osm2pgsql .)

wget (brew install wget (for MAC) or sudo apt-get install wget (linux server))

Unzip this file and read the README file and follow all the steps.

osm_import_data.zip

20 Feb 2023, 03:53 PM

Reference Link

Installation – osm2pgsql

Exporting OSM Data – Examples – osm2pgsql

Download planet

OpenStreetMap