[postgis-users] PostGIS Types Across Schemas

[Steve Horn]

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120216/fe60a003/attachment.html>