Adventures in OSM

One day my curiosity pulled me to a very niche interest on the internet open sea: railway data. A barrage of questions hit my brain: is the data out there up to date? Is the data free? Is there any data at all?

So, first thing a person with experience (ehm) does is start googling and gathering info.

The non-existent low hanging fruit

I know there are faster ways nowadays, but we will get to that later, don’t sweat.
Oh, there is a website called Openrailwaymap, my journey has ended - I’ve naively thought. I have to say the website works great, the data is up to date, filterable, each type of rail is marked with a different color. Kudos to the team! Have a look below:

Although there is a feature I miss, that I wish was there:

DATA EXPORT

After a bit of research, I found out that it used to be there, but somehow removed. There is at least one API implementation to query the data, but it doesn’t give me the freedom I want. To be fair, the Openrailwaymap project is open-source so you can just self-host it, but running the software is not exactly what I want.

Mhmh, the project minus the software I don’t want to run, what is left? Data, just data. I wonder where do they get it, can I get it too? Well, yes, there is a tiny (kidding) community out there that powers it, called OpenStreetMap. This is where my naivete peaks: how hard can it be? I just want a tiny portion of the data queryable on my laptop. Enter…

Geospatial databases

First things first, where do I get the data? That’s easy, there are plenty of mirrors for OSM data. I got my regional data from Geofabrik.de. Just 2GB and you now have all Italy’s data at hand. Where to go next then? Good question. What you’ve just downloaded is a .osm.pbf file and you need a bit of work to turn into something useful.

This is are the tools I’ve used:

• osmosis: to filter only the data you want. It is strongly recommended to trim unnecessary data. It will make processing on your own laptop… feasible.
• osm2pgrouting: takes your .osm file and turn into a beautiful database structure.
• Postgres (Actually pgrouting). The real wizard here. Yes, Postgres really does it all.
• QGIS: this is kinda optional but this tool helped me analyze better the db structure and visualize my exports.

I should probably start with stating the fact that I knew absolutely nothing about Geospatial DBs. Even after all the fiddling, well, the situation hasn’t improved much.

Getting it to run

Let’s deal with the database first, we’ll refine the data later. So, here’s my docker-compose.yaml file.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

name: database
services:
  postgres:
    image: pgrouting/pgrouting:15-3.5-3.8
    ports: 
      - "5432:5432"
    command: postgres -c 'max_connections=200'
    restart: always
    environment:
      - POSTGRES_USER=<your_postgis_user>
      - POSTGRES_PASSWORD=<your_postgis_password>
      - POSTGRES_DB=<your_postgis_dbname>
    volumes:
      - postgres:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -d postgis -U postgis"]
      interval: 10s
      timeout: 10s
      retries: 5
      start_period: 10s

Now that you have your postgres running, we can import the data using osm2pgrouting as follow

1
2
3
4
5
6
7

osm2pgrouting \
  --f italy_railway_only.osm \
  --conf /usr/local/share/osm2pgrouting/mapconfig.xml \
  --schema railway \
  --dbname <your_postgis_dbname> \
  --username <your_postgis_user> \
  --clean --password <your_postgis_password>

if your laptop didn’t run out of memory, you know have a nice querable version of your OSM file. Probably in a different post I will go deeper on what can you do with it.

If you like what you’ve read, please consider donating to OpenStreetMap.