| Converting the osm2pgsql planet_osm_nodes table to the new flat-nodes file | Created: 02.09.2012 22:49 |
|
If you are using osm2pgsql to keep an up-to-date copy
of the relevant parts of the Openstreetmap database, e.g. because
you're running a tile-server, you will be happy to learn that it
has a new "flat-nodes-file" mode. There is a new parameter,
--flat-nodes=FILENAME, that makes osm2pgsql
store the nodes-data it needs to keep (to be able to make
updates from minutely/hourly/daily diffs) into a special
binary file instead of a database table. This is only
recommended for doing full-planet imports/updates, not when
you only have a small region in your database, but in the
full-planet-case the advantages are quite convincing:
Not only does it speed up the processing of diffs, in my
experience between 20 and 30 percent, but it also saves a lot
of disk space. Instead of a postgresql-table that takes up
100 GB on disk, you get a 17 GB file (at the time of writing
this). This also makes it easier to store the file on
a SSD, further speeding up the processing (but even when the
file is on normal spindles the speed-up is significant). However, there is a small problem: You cannot just switch to the flat-nodes mode if you did your initial planet import without it. You would have to start again from a fresh planet-dump import, which can take days, and then you'll have to wait another few days until your database has catched up all the changes that have happened since the last dump. This procedure seemed so undesirable to me that I decided to invest a few minutes of my time to create a tool that allows to convert the database-table to the file. This patch needs to be applied to your osm2pgsql sourcecode-directory. If all goes well, you will then have a new convertnodestabletofile binary at the end of the osm2pgsql buildprocess. This can then be used to convert the database by running something similiar to psql -d osm -q -c 'COPY (select id,lat,lon from planet_osm_nodes order by id asc) TO STDOUT WITH CSV;' | ./convertnodestabletofile /mnt/flatnodes/flatnodes.db Of course, you may need to adapt the database-name after the -d or authentication parameters. This command should take a while, and print some progress information while it goes. After it has run through successfully, you can then test updates with the --flat-nodes=FILENAME parameters. If everything is fine, the last step is to clean up the data in the old postgresql-table that is no longer needed. Note however that (at the time of writing this) osm2pgsql still requires the table to exist, even if it does not use any data from it in flat-nodes mode. The fastest way to clean up is probably to delete the table and create a new empty one, or you can do a delete from planet_osm_nodes followed by an vacuum planet_osm_nodes full. And just as a warning in case this is not obvious: You need to make sure that nothing tries to update the database-table while you convert it, and after you have converted it, you must make sure to NEVER run an update without flat-nodes parameter again, as this wil seriously mess up your database. |
|
|
no comments yet write a new comment:
|
|