[postgis-users] PostGIS Types Across Schemas
We are preparing our data for production, and have a few import procedures to do so. To keep the database organized, we are creating 2 schemas: "public", and "import". CREATE TABLE import.geo_shapes ( geo_shape_id serial NOT NULL, geocode character varying(9), geography import.geography(MultiPolygon,4326), geo_type integer, CONSTRAINT geo_shapes_geo_shape_id_pk PRIMARY KEY (geo_shape_id ) ) When the import.geo_shapes table is finished being loaded, I ALTER the table's schema and change it to "public" so it becomes usable to the application. The problem I'm having is the geography column is still tied to the "import" namespace, even though the table's schema is "public". To work around the problem I'm creating a new column on geo_shapes as "public.geography" and then using this UPDATE to get it to the correct type: ALTER TABLE geo_shapes RENAME COLUMN geography TO geography_temp; ALTER TABLE geo_shapes ADD COLUMN geography geography(MultiPolygon,4326); UPDATE geo_shapes SET geography = public.ST_GeogFromWKB(import.ST_AsBinary(geography_temp)) ALTER TABLE geo_shapes DROP COLUMN geography_temp; Is there a better way to do this? Seems like the ideal thing would be to have the types defined at the database level, but doesn't seem like that is possible. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS Types Across Schemas
You can run Probe_Geometry_Columns() after altering the schema of your imported table. Another option is to update the geometry columns table manually with something like: UPDATE geometry_columns SET f_schema_name = 'public' WHERE f_table_name = 'geo_shapes'; -Rob From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Steve Horn Sent: Thursday, February 16, 2012 12:03 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] PostGIS Types Across Schemas We are preparing our data for production, and have a few import procedures to do so. To keep the database organized, we are creating 2 schemas: "public", and "import". CREATE TABLE import.geo_shapes ( geo_shape_id serial NOT NULL, geocode character varying(9), geography import.geography(MultiPolygon,4326), geo_type integer, CONSTRAINT geo_shapes_geo_shape_id_pk PRIMARY KEY (geo_shape_id ) ) When the import.geo_shapes table is finished being loaded, I ALTER the table's schema and change it to "public" so it becomes usable to the application. The problem I'm having is the geography column is still tied to the "import" namespace, even though the table's schema is "public". To work around the problem I'm creating a new column on geo_shapes as "public.geography" and then using this UPDATE to get it to the correct type: ALTER TABLE geo_shapes RENAME COLUMN geography TO geography_temp; ALTER TABLE geo_shapes ADD COLUMN geography geography(MultiPolygon,4326); UPDATE geo_shapes SET geography = public.ST_GeogFromWKB(import.ST_AsBinary(geography_temp)) ALTER TABLE geo_shapes DROP COLUMN geography_temp; Is there a better way to do this? Seems like the ideal thing would be to have the types defined at the database level, but doesn't seem like that is possible. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS Types Across Schemas
Hi Steve, you only need to enable postgis for public schema. In every other schema the geography typ will be used as public.geography. Gr Ralf On Donnerstag 16 Februar 2012 18:03:16 Steve Horn wrote: > We are preparing our data for production, and have a few import procedures > to do so. To keep the database organized, we are creating 2 schemas: > "public", and "import". > > CREATE TABLE import.geo_shapes > ( > geo_shape_id serial NOT NULL, > geocode character varying(9), > geography import.geography(MultiPolygon,4326), > geo_type integer, > CONSTRAINT geo_shapes_geo_shape_id_pk PRIMARY KEY (geo_shape_id ) > ) > > When the import.geo_shapes table is finished being loaded, I ALTER the > table's schema and change it to "public" so it becomes usable to the > application. > > The problem I'm having is the geography column is still tied to the > "import" namespace, even though the table's schema is "public". > > To work around the problem I'm creating a new column on geo_shapes as > "public.geography" and then using this UPDATE to get it to the correct > type: > > ALTER TABLE geo_shapes > RENAME COLUMN geography TO geography_temp; > > ALTER TABLE geo_shapes > ADD COLUMN geography geography(MultiPolygon,4326); > > UPDATE geo_shapes > SET geography = public.ST_GeogFromWKB(import.ST_AsBinary(geography_temp)) > > ALTER TABLE geo_shapes > DROP COLUMN geography_temp; > > Is there a better way to do this? Seems like the ideal thing would be to > have the types defined at the database level, but doesn't seem like that is > possible. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS Types Across Schemas
Hi Ralf, Thanks for the direction. I realized sometime yesterday that I should be able to use the functions/types from the public schema inside my second schema. Have a great day! On Fri, Feb 17, 2012 at 4:53 AM, Ralf Suhr wrote: > ** > > Hi Steve, > > > > you only need to enable postgis for public schema. In every other schema > the geography typ will be used as public.geography. > > > > > > Gr > > Ralf > > > > On Donnerstag 16 Februar 2012 18:03:16 Steve Horn wrote: > > > We are preparing our data for production, and have a few import > procedures > > > to do so. To keep the database organized, we are creating 2 schemas: > > > "public", and "import". > > > > > > CREATE TABLE import.geo_shapes > > > ( > > > geo_shape_id serial NOT NULL, > > > geocode character varying(9), > > > geography import.geography(MultiPolygon,4326), > > > geo_type integer, > > > CONSTRAINT geo_shapes_geo_shape_id_pk PRIMARY KEY (geo_shape_id ) > > > ) > > > > > > When the import.geo_shapes table is finished being loaded, I ALTER the > > > table's schema and change it to "public" so it becomes usable to the > > > application. > > > > > > The problem I'm having is the geography column is still tied to the > > > "import" namespace, even though the table's schema is "public". > > > > > > To work around the problem I'm creating a new column on geo_shapes as > > > "public.geography" and then using this UPDATE to get it to the correct > > > type: > > > > > > ALTER TABLE geo_shapes > > > RENAME COLUMN geography TO geography_temp; > > > > > > ALTER TABLE geo_shapes > > > ADD COLUMN geography geography(MultiPolygon,4326); > > > > > > UPDATE geo_shapes > > > SET geography = public.ST_GeogFromWKB(import.ST_AsBinary(geography_temp)) > > > > > > ALTER TABLE geo_shapes > > > DROP COLUMN geography_temp; > > > > > > Is there a better way to do this? Seems like the ideal thing would be to > > > have the types defined at the database level, but doesn't seem like that > is > > > possible. > > > -- Steve Horn http://www.stevehorn.cc st...@stevehorn.cc http://twitter.com/stevehorn 740-503-2300 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
