OSM2PGSQL(1)

📦
version 2.3.1 · 6 versions
, in arch rolling, nixpkgs unstable, ubuntu stonking

💖 OSM2PGSQL(1) 💖

okay bestie, welcome to osm2pgsql(1), the manual page for the little program that takes OpenStreetMap data and yeets it into PostgreSQL. she's iconic, she's essential, and she's about to become your whole personality. let's go 🦋

✨ NAME ✨

osm2pgsql — OpenStreetMap data to PostgreSQL converter. literally what it says on the tin, no cap.

🌈 SYNOPSIS 🌈

osm2pgsql [OPTIONS] OSM-FILE...

💅 DESCRIPTION 💅

okay so osm2pgsql imports OpenStreetMap data into a PostgreSQL/PostGIS database. and like… she is not a side character. she's an essential part of so many rendering toolchains, the Nominatim geocoder, and a whole bunch of other applications out here processing OSM data. main character energy, fr fr. 👑

osm2pgsql can run in either “create” mode (the default) or in “append” mode (that's the -a, --append option, remember her).

In “create” mode, osm2pgsql will create the database tables required by the configuration and import the OSM file(s) you specified on the command line into those tables. > heads up bestie: you also have to use the -s, --slim option if you want your database to be updatable. no slim, no updates, that's the rule. 💀

In “append” mode, osm2pgsql will update the database tables with the data from OSM change files specified on the command line.

This man page can only cover some of the basics and describe the command line options. See the Osm2pgsql Manual for more information. (yes, there's more. she's got layers. 🎀)

🎀 OPTIONS 🎀

This program follows the usual GNU command line syntax, with long options starting with two dashes (--). Mandatory arguments to long options are mandatory for short options too. equality. no favorites. ✨

🔥 MAIN OPTIONS 🔥

  • -a, --append — Run in append mode. Adds the OSM change file into the database without removing existing data. she's adding, not deleting, we love a non-destructive queen. 🫶
  • -c, --create — Run in create mode. This is the default if -a, --append is not specified. > Removes existing data from the database tables! ⚠️ like, GONE gone. be so careful bestie, this one does not play. 💀

⭐ HELP/VERSION OPTIONS ⭐

  • -h, --help — Print help. she's telling you how to use her, take notes. 📝
  • -V, --version — Print osm2pgsql version. identification era. 👑

🌸 LOGGING OPTIONS 🌸

  • --log-level=LEVEL — Set log level (debug, info (default), warn, or error). pick your chatter level. 🤫
  • --log-progress=VALUE — Enable (true) or disable (false) progress logging. Setting this to auto will enable progress logging on the console and disable it if the output is redirected to a file. Default: true. cute AND smart. 🧚
  • --log-sql — Enable logging of SQL commands for debugging. nosy in a helpful way. 🔍
  • --log-sql-data — Enable logging of all data added to the database. > This will write out a huge amount of data! For debugging. like, your disk will feel it. proceed with intention. 😭
  • -v, --verbose — Same as --log-level=debug. short and loud. 💅

💜 DATABASE OPTIONS 💜

  • -d, --database=NAME — The name of the PostgreSQL database to connect to. If this parameter contains an = sign or starts with a valid URI prefix (postgresql:// or postgres://), it is treated as a conninfo string. See the PostgreSQL manual for details. versatile icon.
  • -U, --username=NAME, --user=NAME — Postgresql user name. that's who you are now. 🎀
  • -W, --password — Force password prompt. no sneaky autofill, she's asking you directly. 🔐
  • -H, --host=HOSTNAME — Database server hostname or unix domain socket location. where she lives. 🏠
  • -P, --port=PORT — Database server port. the door number. 🚪
  • --schema=SCHEMA — Default for various schema settings throughout osm2pgsql (default: public). The schema must exist in the database and be writable by the database user. no schema, no service. 🌈

🦋 INPUT OPTIONS 🦋

  • -r, --input-reader=FORMAT — Select format of the input file. Available choices are auto (default) for autodetecting the format, xml for OSM XML format files, o5m for o5m formatted files and pbf for OSM PBF binary format. she can read the room AND the file. ⭐
  • -b, --bbox=MINLON,MINLAT,MAXLON,MAXLAT — Apply a bounding box filter on the imported data. Example: --bbox -0.5,51.25,0.5,51.75. cropping era, we love a rectangle. 🔲

🍓 MIDDLE OPTIONS 🍓

  • -i, --tablespace-index=TABLESPC — Store all indexes in the PostgreSQL tablespace TABLESPC. This option also affects the tables created by the pgsql output. > This option is deprecated. Use the --tablespace-slim-index and/or --tablespace-main-index options instead. she's retired but still showing up to the party, yk? ⚠️
  • --tablespace-slim-data=TABLESPC — Store the slim mode tables in the given tablespace. putting things in their designated spot. 📦
  • --tablespace-slim-index=TABLESPC — Store the indexes of the slim mode tables in the given tablespace. and the indexes get their own spot too. organized queen. 💅
  • -p, --prefix=PREFIX — Prefix for table names (default: planet_osm). everything gets a little name tag. 🏷️
  • -s, --slim — Store temporary data in the database. Without this mode, all temporary data is stored in RAM, and if you do not have enough, the import will not work successfully. With slim mode, you should be able to import the data even on a system with limited RAM, although if you do not have enough RAM to cache at least all of the nodes, the time to import the data will likely be greatly increased. tl;dr: slim mode is the low-RAM bestie but she takes her time. 🐢
  • --drop — Drop the slim mode tables from the database and the flat node file once the import is complete. This can greatly reduce the size of the database, as the slim mode tables typically are the same size, if not slightly bigger than the main tables. It does not, however, reduce the maximum spike of disk usage during import. It can furthermore increase the import speed, as no indexes need to be created for the slim mode tables, which (depending on hardware) can nearly halve import time. > Slim mode tables however have to be persistent if you want to be able to update your database, as these tables are needed for diff processing. so basically: drop = smaller and faster, but no more updates. choose your fighter. 😭
  • -C, --cache=NUM — Only for slim mode: Use up to NUM MB of RAM for caching nodes. Giving osm2pgsql sufficient cache to store all imported nodes typically greatly increases the speed of the import. Each cached node requires 8 bytes of cache, plus about 10% - 30% overhead. As a rule of thumb, give a bit more than the size of the import file in PBF format. If the RAM is not big enough, use about 75% of memory. Make sure to leave enough RAM for PostgreSQL. It needs at least the amount of shared_buffers given in its configuration. Defaults to 800. math girlie mathing. 🔢
  • -x, --extra-attributes — Include attributes of each object in the middle tables and make them available to the outputs. Attributes are: user name, user id, changeset id, timestamp and version. the whole backstory, not just the plot. 📖
  • -F, --flat-nodes=FILENAME — Use a file on disk to store node locations instead of storing them in memory (in non-slim mode) or in the database (in slim mode). This is much more efficient than storing the data in the database. Storing the node information for the full planet requires more than 500GB in PostgreSQL, the same data is stored in “only” 90GB using the flat-nodes mode. This can also increase the speed of applying diff files. This option activates the flat-nodes mode and specifies the location of the database file. It is a single large file. This mode is only recommended for full planet imports as it doesn’t work well with small imports. The default is disabled. > The file will stay on disk after import, use --drop to remove it (but you can’t do updates then). efficiency queen but she leaves her stuff everywhere. 🗂️
  • --middle-schema=SCHEMA — Use PostgreSQL schema SCHEMA for all tables, indexes, and functions in the middle. The schema must exist in the database and be writable by the database user. By default the schema set with --schema is used, or public if that is not set. she'll take the hand-me-down schema, it's fine. 🫶
  • --middle-with-nodes — When a flat nodes file is used, nodes are not stored in the database. Use this option to force storing nodes with tags in the database, too. exceptions get made for the tagged ones. ✨

👑 OUTPUT OPTIONS 👑

  • -O, --output=OUTPUT — Specifies the output to use. Currently osm2pgsql supports pgsql, flex, and null. pgsql is the default output still available for backwards compatibility. New setups should use the flex output which allows for a much more flexible configuration. The null output does not write anything and is only useful for testing or with --slim for creating slim tables. flex is the glow-up, pgsql is the nostalgic one, null is doing absolutely nothing and that's her job. 💀
  • -S, --style=FILE — The style file. This specifies how the data is imported into the database, its format depends on the output. (For the pgsql output, the default is /usr/share/osm2pgsql/default.style, for other outputs there is no default.) the vibe guide for your import. 🎀

okay that's part one of the girlies' guide to osm2pgsql(1)… 🌸

🔥 PGSQL OUTPUT OPTIONS 🔥

okay bestie this is the BIG one, the section where the actual database tables get their lil personalities 💅

  • --tablespace-main-data=TABLESPC — drop the data tables into the PostgreSQL tablespace you name in TABLESPC. that's it, that's the whole job.
  • --tablespace-main-index=TABLESPC — same energy but for the indexes, parked in the PostgreSQL tablespace TABLESPC.
  • --latlong — store coordinates in degrees of latitude & longitude. simple, classic, retro 🦋
  • -m, --merc — store coordinates in Spherical Mercator (Web Mercator, EPSG:3857). and bestie, this one is the default ✨ so if you do nothing at all, this is what you get.
  • -E, --proj=SRID — use projection EPSG:SRID. you pick the SRID, it obeys 💅
  • -p, --prefix=PREFIX — sets the prefix for table names (default: planet_osm). and heads up, this is a double-dipper: it affects the middle tables and the pgsql output table names. one prefix, two jobs, iconic.
  • --tag-transform-script=SCRIPT — hand it a Lua SCRIPT and it'll do your tag filtering and normalisation for you 🧚 the script holds callback functions for nodes, ways and relations; each one takes a set of tags and gives you back a transformed, filtered set of tags which then get written to the database. glow up for your tags fr fr.
  • -x, --extra-attributes — include the attributes: user name, user id, changeset id, timestamp and version. ⚠️ this ALSO needs extra entries in your style file, so don't forget that part or you'll be sad.
  • -k, --hstore — adds tags that don't have their own column into an extra hstore (key/value) column in the database tables.
  • -j, --hstore-all — adds ALL the tags to an extra hstore (key/value) column in the database tables. every single one. no crumbs left behind 👑
  • -z, --hstore-column=PREFIX — adds an extra hstore (key/value) column named PREFIX that holds every tag whose key starts with that prefix. for example, --hstore-column "name:" will produce an extra hstore column containing all the name:xx tags. the way that little trick works… obsessed.
  • --hstore-match-only — only keep objects that have a value in at least one of the non-hstore columns. picky? yes. useful? also yes.
  • --hstore-add-index — builds indexes for all hstore columns after the import. post-import glow-up 🌸
  • -G, --multi-geometry — so normally osm2pgsql splits multi-part geometries into separate database rows per part, which means a single OSM object can end up using several rows in the output tables. with this option it instead generates multi-geometry features right in the PostgreSQL tables. one object, one feature, main character energy 💜
  • -K, --keep-coastlines — keep the coastline data instead of filtering it out. by default, objects tagged natural=coastline get discarded, based on the assumption that the Shapefiles generated by OSMCoastline (https://osmdata.openstreetmap.de/) will be used for coastline data. so like… if you're not using those shapefiles, flip this on, bestie 🫶
  • --reproject-area — compute the area column using spherical mercator coordinates even if you're using a different projection for the geometries. yes, you can mix, no, it's not a typo.
  • --output-pgsql-schema=SCHEMA — use PostgreSQL schema SCHEMA for all the tables, indexes, and functions in the pgsql output. 🚨 the schema has to already exist in the database and be writable by the database user. by default it uses whatever you set with --schema, or public if you never set that. so if you get an error about a missing schema… that's why 😭

⏰ EXPIRE OPTIONS ⏰

the "tell the renderers what to redraw" corner of the party 🌈

  • -e, --expire-tiles=[MIN_ZOOM-]MAX-ZOOM — create a tile expiry list. note the shape of the value: the MIN_ZOOM part is optional and the dash is only there if you use it.
  • -o, --expire-output=FILENAME — the output file name for the expired tiles list. name your file, bestie 💖
  • --expire-bbox-size=SIZE — max size for a polygon to expire the whole polygon rather than just the boundary. big shapes get the full treatment instead of an outline.

🧠 ADVANCED OPTIONS 🧠

for when you're feeling brave. no cap.

  • -I, --disable-parallel-indexing — disables parallel clustering and index building on all tables, so it builds one index after the other. slower, but one at a time, like a polite little queue 🐢
  • --number-processes=THREADS — specifies the number of parallel THREADS used for certain operations. turn it up if your machine can handle the drama 🔥

👀 SEE ALSO 👀

and that's the whole page, bestie!! 🎀 this was osm2pgsql version 2.3.1, and you were reading OSM2PGSQL(1) — go build some tiles, you absolute icon 💅✨👑

📦 where this page comes from