0

Does anyone know how to use osmosis to save to a different schema?

I used the pgsnapshot_schema_0.6.sql to create a blank data table, but it always creates it in the public schema. I need to create these table in the 'gis' schema.

Can this be done?

4
  • 1
    Maybe here is a mix of diff. definitions of schema. Can you please say what you understand as schema and how they look in your example? wiki.openstreetmap.org/wiki/Databases#Database_Schemas
    – Mapper
    Jan 21, 2014 at 16:37
  • In post gres, you can store table in schemas. By default, everything goes to public. I need to store the OSM data in the GIS schema. Jan 21, 2014 at 17:23
  • 1
    Does a ALTER TABLE work to reassign a new schema? postgresql.org/message-id/…
    – Mapper
    Jan 21, 2014 at 21:26
  • 2
    Seems you reposted this here: dba.stackexchange.com/q/57213/7788 without a link. Doing that wastes everyone's time; I didn't see these comments, they don't see my answer, etc. If you decide to "move" a question, flag it and ask a mod to move it please, or at least add a link between the two! Jan 22, 2014 at 0:39

4 Answers 4

2

Well you can let it be created on the "public" schema and after: - ALTER TABLE IF EXISTS public.actions SET SCHEMA gis; - ALTER TABLE IF EXISTS public.schema_info SET SCHEMA gis; - ALTER TABLE IF EXISTS public.users SET SCHEMA gis; - ALTER TABLE IF EXISTS public.way_nodes SET SCHEMA gis; - ALTER TABLE IF EXISTS public.relations SET SCHEMA gis; - ALTER TABLE IF EXISTS public.relation_members SET SCHEMA gis; - ALTER TABLE IF EXISTS public.ways SET SCHEMA gis; - ALTER TABLE IF EXISTS public.nodes SET SCHEMA gis; OR you can: ALTER ROLE ${user} SET search_path TO ${osm_schema}, public;

OR you can even go with what @craig-ringer, previous fellow.

1
  • Note that if your going to use osmosis to update/etc. the newly changed OSM schema, you have to, prior to your osmosis use, change the "default" PostgreSQL schema like this: ALTER ROLE ${user} SET search_path TO ${osm_schema}, public; Jul 17, 2014 at 17:07
1

I have no idea what "osmosis" is, but in general you can deal with this by:

  • SETting search_path before running your CREATE statements
  • Explicitly schema-qualifying your objects, eg gis.mytable, not mytable
  • ALTER USER ... SET search_path = 'gis, public' on the user ID in use
  • ALTER DATABASE ... SET search_path 'gis, public' on the database in use
1
  • osmosis is a software for processing OSM data, either from/to a file or database.
    – scai
    Jan 22, 2014 at 8:35
0

Somewhat related issue I faced recently, needed two pbfs in the same database so the way I did it is:

  • load your "first pbf" as you normally would into postgres
createdb test-U user
psql -U user -d test-c 'CREATE EXTENSION hstore;'
psql -U user -d test-c 'CREATE EXTENSION postgis;'
psql -U user -d test-c 'CREATE SCHEMA osm_public;'
psql -U user -d test-f /usr/share/doc/osmosis/examples/pgsnapshot_schema_0.6.sql
psql -U user -d test-f /usr/share/doc/osmosis/examples/pgsnapshot_schema_0.6_linestring.sql
  • load your pbf with

osmosis -v --rbf myarea.osm.pbf --wp host=localhost database=test user=user

  • alter existing schema
psql -U user -d test -c 'ALTER TABLE nodes SET SCHEMA osm_public;'
psql -U user -d test -c 'ALTER TABLE relation_members SET SCHEMA osm_public;'
psql -U user -d test -c 'ALTER TABLE relations SET SCHEMA osm_public;'
psql -U user -d test -c 'ALTER TABLE users SET SCHEMA osm_public;'
psql -U user -d test -c 'ALTER TABLE way_nodes SET SCHEMA osm_public;'
psql -U user -d test -c 'ALTER TABLE ways SET SCHEMA osm_public;'
psql -U user -d test -f /usr/share/doc/osmosis/examples/pgsnapshot_schema_0.6.sql
psql -U user -d test -f /usr/share/doc/osmosis/examples/pgsnapshot_schema_0.6_linestring.sql
  • and load your other pbf

osmosis -v --rbf latest.osm.pbf --wp host=localhost database=test user=user

You now have a pbf in public, and one in osm_public.

0

We can modify the osmosis script files, i.e. the pgsnapshot_schema_0.6.sql file. Add the first line in this as "set schema 'your_desired_schema_name';" Example - set schema 'gis';

And we would also need to make this schema the owner of hstore extension while setting up the osmosis database. It could be moved to public once the data loading part is finished.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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