Arxius de postgis - Another dev

OpenStreetMap is a crowdsourced database which has editions every second, due this the database that you downloaded a month ago is outdated. To solve this problem I did this little tutorial.

Initial database load

The first step is to load the file to the Postgres. To do this we will use osm2pgsql tool. You can install it with this command

apt-get install osm2pgsql

After this, we can download the actual database from Geofabrik. In this example we will use the data from Andorra:

wget https://download.geofabrik.de/europe/andorra-latest.osm.pbf

Once we have the osm data we load it to the Postgres

createdb osm #Create the database on Postgres
psql -d osm -c "CREATE EXTENSION postgis" # Creates the postgis extension
psql -d osm -c "CREATE EXTENSION hstore" # Creates the hstore extension
osm2pgsql -d osm -s -C 4096 andorra-latest.osm.pbf --hstore # Loads the data

We need one more step to initialize the replication. In this case we will initialize the osmosis directory on $HOME/osmosis/

mkdir -p $HOME/osmosis/andorra

osmosis --rrii workingDirectory=$HOME/osmosis/andorra
sed -i 's!baseUrl=https://planet.openstreetmap.org/replication/minute!baseUrl=https://download.geofabrik.de/europe/andorra-updates/!' $HOME/osmosis/andorra/configuration.txt
wget https://download.geofabrik.de/europe/spain-updates/state.txt -O "$HOME/osmosis/andorra/state.txt"

Cron task

After we load the database we need to update the database periodically. To do this on Linux we use cron. First we create a scprit, for example on $HOME/update_db.sh with this commands:

osmosis --read-replication-interval workingDirectory="$HOME/osmosis/andorra" --simplify-change --write-xml-change $HOME/andorra_changes.osm
/usr/local/bin/osm2pgsql --append -s -C 3000 -G --hstore -d osm $HOME/andorra_changes.osm

Remember to add execution permisions to the script

chmod +x $HOME/update_db.sh

To configure the task once a day we can add this line on cron

0 7 * * * $HOME/update_db.sh

DBeaver (https://dbeaver.io/download/) is a great gui tool that allows us to connect and query multiple databases (Postgres,Mysql,Oracle…) from multiple operating systems. DBeaver is free but also has a paid version with extra functionalities

Configuring a connection to Postgres

You can create a new connection going to “File>New>DBeaver>Database connection” or clicking on the socket icon. After this we will see a screen like this:

After this we can fill the connection information

My recommendation is to test the connection before finishing

Trick
If you want to use this connection to access different databases you can specify this on the PostgreSQL tab, and enable the “Show all databases” option

You can configure more databases like MySQL or Oracle

Configuring a connection with a SSH tunnel

A cool feature of DBeaver is that it can connect to a database that is on a remote machine without having to expose the service to the whole Internet.

To do this you can go to the tab of “SSH” and configure the

Once you fill the host,user and password you can test the connection and finish

Other functionalities

Viewing the database schema

With DBeaver you can see the database diagram using this button or you can see the relations of a single table.

The result is something like this:

Viewing gis data

A cool feature of DBeaver is the integration with Postgis, so if you double click over a geometry field you will be able to preview the feature in a map

Etiqueta: postgis

How to have an updated OpenStreetMap database