3

What is the procedure to load OSM to a different schema than public?

The osm2pgsql documentation mention the parameter --prefix for specifying the tables prefix (planet_osm by default) but does not mention the schema name.
Running osm2pgsql -h -v to list all available commands, it seems there is no parameter for specifying the schema.

How can I load OSM data to a custom schema?

4

osm2pgsql creates the tables and indexes in the user default schema, i.e. the first one found in its search path, which is usually public. By changing this setting, in PostgreSql, one can load the data to a custom schema. It is important to keep the public schema in the path, as it is required for getting the hstore type.

--Create the new user
CREATE USER osm_loader WITH ENCRYPTED PASSWORD '*****';
--create the schema
CREATE SCHEMA osm AUTHORIZATION osm_loader;
--Change the search_path. 
ALTER ROLE osm_loader SET search_path TO osm, public;

and you can then launch the osm2pgsql tool with the new user

osm2pgsql.exe data.osm.pbf -U osm_loader [... other parameters as needed]

Alternatively, you could change the search path of the user you are currently using. Make sure to change it back once the data is loaded.

2
  • 5
    It is a pity that this is so complicated. Why is it not possible to choose the target schema as parameter? Jun 22 '17 at 7:24
  • Added an updated answer due to both osm2pgsql 1.4 change and additional flex support
    – ThomasG77
    Jul 19 at 15:03
1

Here's some Python code I'm using to import osm data to a certain schema -

# params
dbname = 'mydb'
username = 'postgres'
schema = 'austin'
osmfile = 'austin.osm'
style = 'all.style'

# change search path to new schema
# osm2pgsql will import the osm data to the first schema in the search path
sql = f"CREATE SCHEMA {metro}; ALTER ROLE {username} SET search_path TO {metro},public;"
cmd = f'psql --dbname {dbname} --command "{sql}"' # windows needs double quotes around sql
print(cmd)
os.system(cmd)

# import osm data
# uses slim mode - see https://wiki.openstreetmap.org/wiki/Osm2pgsql#Slim_mode
cmd = f"osm2pgsql --create --latlong --slim --hstore --style {style} --database {dbname} --username {username} {osmfile}"
print(cmd)
os.system(cmd)

# change search path back to public schema
sql = f"ALTER ROLE {username} SET search_path TO public;"
cmd = f'psql --dbname {dbname} --command "{sql}"'
print(cmd)
os.system(cmd)
1

Since osm2pgsql version 1.4, you can specify a destination schema with option --output-pgsql-schema when you use default behaviour (implicit -O pgsql default option). So, it would look like the following

osm2pgsql --create \
    -H localhost -U postgres -P 5432 -d mydb \
    --slim --drop \
    --output-pgsql-schema=myschema \
    extract.osm.pbf

If you use Flex output using -O flex, you may look at this sample https://github.com/openstreetmap/osm2pgsql/blob/master/flex-config/with-schema.lua#L12 to specify the output schema destination in the lua file.

Beware as you need to create the destination schema before specifying it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.