[postgis-users] PostGIS Types Across Schemas

Steve Horn steve at stevehorn.cc
Thu Feb 16 09:03:16 PST 2012

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

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

More information about the postgis-users mailing list