[postgis-users] PostGIS Types Across Schemas

[Ralf Suhr]

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