Test database instances

OSM is a big database. Postgresql/Postgis is an excellent database management system, but for local tests on my notebook I have a problem: I have only one slow harddisk that is shared by postgresql tablespace, tomcat, linux swap partition and firefox.

Even with 4 GB RAM that leads to a problem, if the database tables become too big to fit into memory. Especially for the mapnik database schema some actions require full table scans on big tables (that’s one reason why I would prefer a different database schema).

A worldwide osm database is to big for my harddisk (or at least I don’t want to give that much disk space for testing currently here), so I first tried a germany extract database (built out of the Geofabrik’s pbf Germany extract), but even that’s too big for fast processing as at least one query of the exploration page needs a full sequential table scan through the ways table. As a result the calculation of any exploration page takes several minutes, which isn’t acceptable for debugging (as it would not be acceptable for a live environment, of course).

After all I had to restrict myself to an even smaller part of the world.

To apply that I first thought about cutting a boundingbox around Paderborn by osm2pgsql directly:

osm2pgsql -s -C 768 -c -p paderborn -d lalm -U lalm -W -H -P 5432 
          -b 8.6,51.65,8.9,51.8 --hstore --style ./default.style germany.osm.pbf

The problem here is, that osm2pgsql ignores the bounding box (parameter -b) to write the slim tables. These are written containing every node of the import file, which takes quite long and uses lot’s of disk space.

The second attempt is better: I first used osmosis to get the bounding box extract and then imported the whole resulting file using osm2pgsql. The corresponding osmosis command is

osmosis --read-pbf file="germany.osm.pbf" 
        --bounding-box top=51.8 left=8.6 bottom=51.65 right=8.9 
        --write-pbf file="paderborn.osm.pbf" compress=none

I decided not to compress the paderborn.osm.pbf file as that speeds up reading the file for osm2pgsql afterwards. The osm2pgsql import then is again the same as above except the -b parameter as now the bbox corresponds to the whole file already.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.