[postgis-users] PostGIS Types Across Schemas

[Robert_Clift at doh.state.fl.us]

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-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Steve Horn
Sent: Thursday, February 16, 2012 12:03 PM
To: postgis-users at 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.


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120216/b5f25fbc/attachment.html>