[GIS] osm2pgsql –hstore

openstreetmaposm2pgsqlpostgispostgresql

I'm working with PostgreSQL 8.$ and PostGIS and I need to import an OSM file to the database. I tried it with the utility osm2pgsql, but I have problem with the hstore extension.

Does anyone know how to solve this problem?

error while opening file --hstore

Best Answer

I needed to write --hstore (or -k) before I write path to the osm file:

osm2pgsql "C:\Program Files\PostgreSQL\8.4\osm2pgsql\x64\map.osm" -d postgis20 -U postgres -P 5432 -S --hstore "C:\Users\Ludva\Desktop\osm2pgsql\default.style"

Related Solutions

[GIS] osm2pgsql: database “gis” does not exist

There seems to be something wrong with the style file option. Try:

F:\randomGISstuff\ethiopia>osm2pgsql -c -G -U postgres --hstore -S F:\randomGISstuff\ethiopia\default.style  -U postgres -d ethiopia ethiopia-latest.osm.pbf

If the user needs to give a password, you might have to put it on the command line as well.

It might help to always give the full path to the pbf file.

[GIS] Alternative approach for loading osm2pgsql data

The osm2pgsql documentation suggests osm2pgsql -c -d gis --slim -C <cache size> --flat-nodes <flat nodes> planet-latest.osm.pbf.

where

<cache size> is 24000 on machines with 32GiB or more RAM or about 75% of memory in MiB on machines with less
<flat nodes> is a location where a 24GiB file can be saved.

This works, and on a fast machine will take under a day.

Because you have a lot of RAM, I'd go with 28000 cache. There is no point in a cache of 35000, as that is more than is needed to store every node.

You should

use PBF, not bzipped XML

Given your previous problem, also download the md5 file and checksum the file with md5sum -c, e.g. md5sum -c planet-latest.osm.pbf.md5. This will ensure there wasn't corruption downloading or saving to disk
use slim mode

As the earlier linked page states, in non-slim there is also a way cache that takes up about as much RAM as the node cache, so you cannot do a non-slim import of the planet with 48GB of RAM.
decide on an update strategy

At some point, you will want to update your database. If you plan to do it infrequently, you'll probably just drop the database and reload the latest data. If this is your plan, add the --drop flag to your command line and the slim tables will not be indexed and instead dropped, saving over 100GB and hours of time. If using drop, you can also delete the flat nodes file.

If you plan on updating using minutely, hourly, or daily diffs, then you can't use --drop.
Specify the number of CPU cores to be used, with --number-processes.

This will help the "pending ways" section of the import. Recent (0.88.x) versions of osm2pgsql default to the number of hardware threads, but older versions default to 1.

You've mentioned using a VM. Many VMs have extremely slow disks when doing random IO, which may limit your speed.

Best Answer

Related Solutions

[GIS] osm2pgsql: database “gis” does not exist

[GIS] Alternative approach for loading osm2pgsql data

Related Question