In this post, I'm going to extract Monaco's road network data from OpenStreetMap, import it into PostgreSQL and render it out using a minimalist tile server. I'll also show how the Formula 1 circuit can be highlighted using an open source geospatial desktop application.
PostGIS, Up & Running
PostGIS is a 21-year-old project that adds geospatial functionality to PostgreSQL. It's made up of 175K lines of C code and header files and 73K lines of SQL. It has received contributions from 56 contributors including fellow Canadian Paul Ramsey who is the co-founder of the project.
The following will install PostgreSQL 14 with version 3.2 of the PostGIS extension. It was executed on a fresh installation of Ubuntu 20.
$ wget -qO- \
https://www.postgresql.org/media/keys/ACCC4CF8.asc \
| sudo apt-key add -
$ echo "deb http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main 14" \
| sudo tee /etc/apt/sources.list.d/pgdg.list
$ sudo apt update
$ sudo apt install \
postgresql-14-postgis-3 \
postgresql-14-postgis-3-scripts \
postgresql-client-14 \
postgresql-server-dev-14 \
postgis \
unzip
I'll then set up a user account with PostgreSQL.
$ sudo -u postgres \
bash -c "psql -c \"CREATE USER mark
WITH PASSWORD 'test'
SUPERUSER;\""
The following will set up PostGIS and then the streets database I'll be using.
$ createdb template_postgis
$ psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis';"
$ psql template_postgis -c "create extension postgis"
$ psql template_postgis -c "create extension postgis_topology"
$ psql template_postgis -f /usr/share/postgresql/14/contrib/postgis-3.2/legacy.sql
$ psql -d template_postgis -c "GRANT ALL ON geometry_columns TO PUBLIC;"
$ psql -d template_postgis -c "GRANT ALL ON geography_columns TO PUBLIC;"
$ psql -d template_postgis -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"
$ createdb -T template_postgis streets
From OpenStreetMap to PostgreSQL
OpenStreetMap is a 17-year-old collaborative project that has built a geographic database of the world. The database includes a large number of layers and metadata. An export for the entire world is over 64 GB as of this writing. Luckily there are per-country and per-continent extracts produced by Geofabrik GmbH and made freely available. The following downloads a 460 KB extract for Monaco.
$ wget -c https://download.geofabrik.de/europe/monaco-latest.osm.pbf
In order to import the above into PostGIS, I'll use a tool called osm2pgsql. There is a pre-built package for Ubuntu 20 but it is only for version v1.2.1 whereas the latest release of osm2pgsql is 1.6.0. Below I'll build osm2pgsql from its 80K lines of C code so that I have an up-to-date version.
Compiling support for the PROJ cartographic projection library fell outside of the time box I had allocated for this post.
$ sudo apt install \
cmake \
g++ \
libboost-dev \
libboost-filesystem-dev \
libboost-system-dev \
libbz2-dev \
libexpat1-dev \
liblua5.3-dev \
libpq-dev \
libproj-dev \
lua5.3 \
make \
pandoc \
zlib1g-dev
$ git clone https://github.com/openstreetmap/osm2pgsql ~/osm2pgsql
$ mkdir -p ~/osm2pgsql/build
$ cd ~/osm2pgsql/build
$ cmake .. -DUSE_PROJ_LIB=off
$ make -j$(nproc)
$ sudo make install
I'll use a small Lua script to filter out any data that isn't related to the road network in Monaco.
$ vi filter.lua
local streets = osm2pgsql.define_way_table('streets', {
{column = 'type', type = 'text'},
{column = 'name', type = 'text'},
{column = 'tags', type = 'jsonb'},
{column = 'geom', type = 'linestring'},
})
local get_highway_value = osm2pgsql.make_check_values_func({
'pedestrian',
'primary',
'primary_link',
'residential',
'secondary',
'tertiary',
'tertiary_link',
'unclassified',
})
function osm2pgsql.process_way(object)
local highway_type = get_highway_value(object.tags.highway)
if not highway_type then
return
end
if object.tags.area == 'yes' then
return
end
streets:add_row({
type = highway_type,
tags = object.tags,
name = object.tags.name,
geom = {create='line'}
})
end
Running the following will produce a table called "streets" within the "streets" database in PostgreSQL. It has 750 records.
$ osm2pgsql -d streets \
-O flex \
-S filter.lua \
monaco-latest.osm.pbf
Below is an example record.
$ psql streets
\x on
SELECT way_id,
"type",
name,
jsonb_pretty(tags) tags,
REPLACE(
REPLACE(
ST_AsEWKT(
ST_GeomFromEWKB(geom)),
',', E',\n'),
'(', E',(\n') geom
FROM streets
LIMIT 1;
way_id | 257071645
type | residential
name | Avenue Jacques Abba
tags | { +
| "name": "Avenue Jacques Abba", +
| "source": "cadastre-dgi-fr source : Direction Générale des Impôts - Cadastre. Mise à jour : 2014",+
| "highway": "residential" +
| }
geom | SRID=3857;LINESTRING,( +
| 824897.887300565 5423604.430898497, +
| 824887.1894974997 5423599.5012846105, +
| 824877.5826254443 5423593.970877169, +
| 824869.3895109219 5423586.822945994, +
| 824862.8661887613 5423578.088304859, +
| 824825.1511452807 5423522.060477127, +
| 824816.4125652533 5423496.488361983, +
| 824814.9097521277 5423478.896020303, +
| 824812.1156329087 5423432.127050894, +
| 824807.6183254807 5423384.511061079, +
| 824804.4123241458 5423365.13199355, +
| 824801.5848090796 5423355.350047257, +
| 824797.0986336006 5423347.201000705, +
| 824791.8220897372 5423339.930023846, +
| 824785.3210314749 5423331.38047152, +
| 824728.0248895634 5423273.567220179, +
| 824714.2658005015 5423260.011274419, +
| 824706.9075821601 5423250.09079946, +
| 824701.8202814307 5423239.461730986, +
| 824693.0594375054 5423223.964879177, +
| 824685.2559412007 5423208.806949899, +
| 824681.3263631756 5423202.429535876, +
| 824677.2186739653 5423197.885245964, +
| 824675.2149231312 5423195.682421491, +
| 824656.0234429184 5423179.184361206, +
| 824642.7096318195 5423161.130495241, +
| 824637.5555393959 5423148.961100397, +
| 824631.7891897728 5423113.531308058)
A PostgreSQL Tile Server
Paul Ramsey is also the primary developer of pg_tileserv. Its made up of 7K lines of Go and is intended to be a minimalist web application that can display geospatial data from PostGIS in much the same way that Google Maps and OpenStreetMap render street and parcel data. Below I'll install the latest build.
$ cd ~
$ wget -c https://postgisftw.s3.amazonaws.com/pg_tileserv_latest_linux.zip
$ unzip pg_tileserv_latest_linux.zip -d pg_tileserv
$ chmod +x pg_tileserv/pg_tileserv
$ vi pg_tileserv/config/pg_tileserv.toml
DbConnection = "dbname=streets host=localhost user=mark password=test"
HttpHost = "127.0.0.1"
The following will launch the tile server.
$ cd ~/pg_tileserv
$ ./pg_tileserv --config config/pg_tileserv.toml
Navigating to http://127.0.0.1:7800/public.streets.html will display the road network for Monaco in your browser.
Highlighting the F1 Circuit
The data in PostgreSQL can be further enriched with external data sources. QGIS, an open source geospatial desktop application for Windows, macOS and Linux, is an excellent tool for blending geospatial data. It's made up of over one million lines of C++ code and has been put together by a large number of dedicated contributors over the past 12 years. Ten of whom have contributed over 1,000 commits to the project. It also supports a thriving Python-based plug-in ecosystem.
When you first launch QGIS, there is a browser panel to the left where you can connect to your PostGIS instance. Once connected, you should see Monaco's road networking rendering in the main panel.
Four years ago, there was a post made to the Formula 1 subreddit linking to shape files containing the outline of the Formula 1 circuit in Monaco. Once you've downloaded and unzipped them, drag the folder onto QGIS and you'll see a new "polylines3" layer listed in the bottom left of the UI.
Right-click the "polylines3" layer name and select "properties". Click the "Symbology" tab which is 3rd from the top and under the Favourites section select "effect neon". Click Apply and now you'll see the Formula 1 circuit highlighted in red.
