Elijah Robison | GIS Blog

I wanted a more convenient place to read the osm2pgsql help output, so this seemed as good a place as any for it. /E

-----------------------------------------------------------------

osm2pgsql -h
osm2pgsql SVN version 0.80.0 (32bit id space)

Usage:
        osm2pgsql [options] planet.osm
        osm2pgsql [options] planet.osm.{gz,bz2}
        osm2pgsql [options] file1.osm file2.osm file3.osm

This will import the data from the OSM file(s) into a PostgreSQL database
suitable for use by the Mapnik renderer

Options:
   -a|--append          Add the OSM file into the database without removing
                        existing data.
   -b|--bbox            Apply a bounding box filter on the imported data
                        Must be specified as: minlon,minlat,maxlon,maxlat
                        e.g. --bbox -0.5,51.25,0.5,51.75
   -c|--create          Remove existing data from the database. This is the
                        default if --append is not specified.
   -d|--database        The name of the PostgreSQL database to connect
                        to (default: gis).
   -i|--tablespace-index        The name of the PostgreSQL tablespace where
                        all indexes will be created.
                        The following options allow more fine-grained control:
      --tablespace-main-data    tablespace for main tables
      --tablespace-main-index   tablespace for main table indexes
      --tablespace-slim-data    tablespace for slim mode tables
      --tablespace-slim-index   tablespace for slim mode indexes
                        (if unset, use db's default; -i is equivalent to setting
                        --tablespace-main-index and --tablespace-slim-index)
   -l|--latlong         Store data in degrees of latitude & longitude.
   -m|--merc            Store data in proper spherical mercator (default)
   -M|--oldmerc         Store data in the legacy OSM mercator format
   -E|--proj num        Use projection EPSG:num
   -u|--utf8-sanitize   Repair bad UTF8 input data (present in planet
                        dumps prior to August 2007). Adds about 10% overhead.
   -p|--prefix          Prefix for table names (default planet_osm)
   -s|--slim            Store temporary data in the database. This greatly
                        reduces the RAM usage but is much slower. This switch is
                        required if you want to update with --append later.
                        This program was compiled on a 32bit system, so at most
                        3GB of RAM will be used. If you encounter problems
                        during import, you should try this switch.
      --drop            only with --slim: drop temporary tables after import (no updates).
   -S|--style           Location of the style file. Defaults to /usr/local/share/osm2pgsql/default.style
   -C|--cache           Now required for slim and non-slim modes:
                        Use up to this many MB for caching nodes (default: 800)
   -U|--username        Postgresql user name
                        password can be given by prompt or PGPASS environment variable.
   -W|--password        Force password prompt.
   -H|--host            Database server hostname or socket location.
   -P|--port            Database server port.
   -e|--expire-tiles [min_zoom-]max_zoom        Create a tile expiry list.
   -o|--expire-output filename  Output filename for expired tiles list.
   -r|--input-reader    Input frontend.
                        libxml2   - Parse XML using libxml2. (default)
                        primitive - Primitive XML parsing.
                        pbf       - OSM binary format.
   -O|--output          Output backend.
                        pgsql - Output to a PostGIS database. (default)
                        gazetteer - Output to a PostGIS database suitable for gazetteer
                        null  - No output. Useful for testing.
   -x|--extra-attributes
                        Include attributes for each object in the database.
                        This includes the username, userid, timestamp and version.
                        Note: this option also requires additional entries in your style file.
   -k|--hstore          Add tags without column to an additional hstore (key/value) column to postgresql tables
      --hstore-match-only       Only keep objects that have a value in one of the columns
      -                         (normal action with --hstore is to keep all objects)
   -j|--hstore-all      Add all tags to an additional hstore (key/value) column in postgresql tables
   -z|--hstore-column   Add an additional hstore (key/value) column containing all tags
                        that start with the specified string, eg --hstore-column "name:" will
                        produce an extra hstore column that contains all name:xx tags
   -G|--multi-geometry  Generate multi-geometry features in postgresql tables.
   -K|--keep-coastlines Keep coastline data rather than filtering it out.
                        By default natural=coastline tagged data will be discarded based on the
                        assumption that post-processed Coastline Checker shapefiles will be used.
      --number-processes                Specifies the number of parallel processes used for certain operations
                        Default is 1
   -I|--disable-parallel-indexing       Disable indexing all tables concurrently.
      --unlogged        Use unlogged tables (lost on crash but faster). Requires PostgreSQL 9.1.
      --cache-strategy  Specifies the method used to cache nodes in ram.
                                Available options are:
                                dense: caching strategy optimised for full planet import
                                chunked: caching strategy optimised for non-contigouse memory allocation
                                sparse: caching strategy optimised for small extracts
                                optimized: automatically combines dense and sparse strategies for optimal storage efficiency.

                                           optimized may use twice as much virtual memory, but no more physical memory
                                   The default is "chunked"
   -h|--help            Help information.
   -v|--verbose         Verbose output.

Add -v to display supported projections.
Use -E to access any espg projections (usually in /usr/share/proj/epsg)

I’m following the BostonGIS tutorial(s) to learn how to setup an OpenStreetMap tile server on Windows (XP 32, cos’ that’s what’s on the desk), and I’m running into headache after headache. So this post notes the gotcha’s I’m encountering and how I’m fixing them (optimistically assuming I fix all of them).

“Thar be dragons..” 8)

—————————————————

Part I: “Failed to start MSVCR90.dll”

First, as recommended I tried using the HOTOSM installer, but when I launch it using the example command provided on the BostonGIS site, I got the error above. I’d logged out/in, rebooted, ad nauseam, to no avail. Also the problem can’t be my redist libs–this install of XP is only a couple months old, and I have all the redists libs I know of (2005, 2008, 2010—plus, when I try reinstalling a redist it says I have the latest). I consider uninstalling and reinstalling Visual Studio to be more trouble than just trying something else to get the OSM download into PostGIS, so..

Next I tried the latest “alpha” build of osm2pgsql (rather than the all-in-one HOTOSM installer), and that  got me around the MSVCR/redist issue. That is, it’s launching..

—————————————————

Part II: the BostonGIS osm2pgsql example command

Error: Connection to database failed: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket “/tmp/.s.PGSQL.5432”?

…and then…

Error: Connection to database failed: fe_sendauth: no password supplied

…and then…

Out of memory for node cache dense index, try using “–cache-strategy sparse” instead

…and then…

Out of memory for sparse node cache, reduce –cache size

I’ve benefitted greatly from the BostonGIS tutorials, and I’m very thankful for them. (Thank you Regina, Leo, et. al., sincerely!) Having said that, though, here’s a few notes discussing how I addressed the above, for which a couple items are conspicuously missing in the BostonGIS osm2pgsl example command.

So take a look at this:

osm2pgsql missouri.osm.bz2 -H localhost -d OSM -U postgres -P 5432 -W -S default.style -C 512 –slim –hstore –cache-strategy sparse

What’s of-note:

-H localhost (solves the first error)

-W (solves the second error, i.e. lets you enter your db password after ENTER)

–cache-strategy sparse (gets past the third, and into the fourth error)

–slim (from everything I read, you just need this on a 32-bit system)

-C 512 (wicked-small cache size)

—————————————————

21 Minutes. I’m not talking about an episode of Friends.

So those are the extra command parameters that ultimately got me to end-game:

Osm2pgsql took 1317s overall

Setting that wicked-small cache size finally did the trick (it actually finished while I was writing this). At first I started it larger (12000, look under “Parameters”), then I started backing off (4096, 2048 look under “Loading data into your server”), when all the examples I found didn’t work, I just tried reducing by the common multiples—again, 1024 didn’t work, but 512 did.

I need to do some research, but I guess that cache size suggests my hard disk must be ..wholly ..awful. :/ I’m not a gear-hound (clearly, I’m using XP), but that’s my best-guess.