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?
-
1Maybe 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– MapperCommented 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.– code base 5000Commented Jan 21, 2014 at 17:23
-
1Does a ALTER TABLE work to reassign a new schema? postgresql.org/message-id/…– MapperCommented Jan 21, 2014 at 21:26
-
2Seems 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!– Craig RingerCommented Jan 22, 2014 at 0:39
Add a comment
|
4 Answers
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.
-
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;Commented Jul 17, 2014 at 17:07
I have no idea what "osmosis" is, but in general you can deal with this by:
SETtingsearch_pathbefore running yourCREATEstatements- Explicitly schema-qualifying your objects, eg
gis.mytable, notmytable ALTER USER ... SET search_path = 'gis, public'on the user ID in useALTER DATABASE ... SET search_path 'gis, public'on the database in use
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.
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.
