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?

3

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.

  • 4
    It is a pity that this is so complicated. Why is it not possible to choose the target schema as parameter? – coding_bird Jun 22 '17 at 7:24
0

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)

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.