[postgis-users] PostGIS Types Across Schemas

[Steve Horn]

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 <Ralf.Suhr at itc-halle.de> 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
steve at stevehorn.cc
http://twitter.com/stevehorn
740-503-2300
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120217/b6ce234a/attachment.html>