Hi Ralf,<div>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. </div><div><br></div><div>Have a great day!<br><br><div class="gmail_quote">
On Fri, Feb 17, 2012 at 4:53 AM, Ralf Suhr <span dir="ltr"><<a href="mailto:Ralf.Suhr@itc-halle.de">Ralf.Suhr@itc-halle.de</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<u></u>
<div style="font-family:'Sans Serif';font-size:10pt;font-weight:400;font-style:normal">
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">Hi Steve,</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px"> </p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">you only need to enable postgis for public schema. In every other schema the geography typ will be used as public.geography.</p>

<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px"> </p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px"> </p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">Gr</p><span class="HOEnZb"><font color="#888888">
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">Ralf</p></font></span><div><div class="h5">
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px"> </p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">On Donnerstag 16 Februar 2012 18:03:16 Steve Horn wrote:</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> We are preparing our data for production, and have a few import procedures</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> to do so. To keep the database organized, we are creating 2 schemas:</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> "public", and "import".</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> </p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> CREATE TABLE import.geo_shapes</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> (</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">>   geo_shape_id serial NOT NULL,</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">>   geocode character varying(9),</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">>   geography import.geography(MultiPolygon,4326),</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">>   geo_type integer,</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">>   CONSTRAINT geo_shapes_geo_shape_id_pk PRIMARY KEY (geo_shape_id )</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> )</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> </p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> When the import.geo_shapes table is finished being loaded, I ALTER the</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> table's schema and change it to "public" so it becomes usable to the</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> application.</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> </p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> The problem I'm having is the geography column is still tied to the</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> "import" namespace, even though the table's schema is "public".</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> </p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> To work around the problem I'm creating a new column on geo_shapes as</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> "public.geography" and then using this UPDATE to get it to the correct</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> type:</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> </p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> ALTER TABLE geo_shapes</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> RENAME COLUMN geography TO geography_temp;</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> </p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> ALTER TABLE geo_shapes</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> ADD COLUMN geography geography(MultiPolygon,4326);</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> </p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> UPDATE geo_shapes</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> SET geography = public.ST_GeogFromWKB(import.ST_AsBinary(geography_temp))</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> </p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> ALTER TABLE geo_shapes</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> DROP COLUMN geography_temp;</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> </p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> Is there a better way to do this? Seems like the ideal thing would be to</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> have the types defined at the database level, but doesn't seem like that is</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px">> possible.</p>
<p style="margin-top:0px;margin-bottom:0px;margin-left:0px;margin-right:0px;text-indent:0px"> </p></div></div></div></blockquote></div><br><br clear="all"><div><br></div>-- <br>Steve Horn<br><a href="http://www.stevehorn.cc" target="_blank">http://www.stevehorn.cc</a><br>
steve@stevehorn.cc<br><a href="http://twitter.com/stevehorn" target="_blank">http://twitter.com/stevehorn</a><br>740-503-2300<br><br>
</div>