<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.21310" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=045021719-16022012><FONT face=Arial
size=2>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:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=045021719-16022012><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=045021719-16022012><FONT face=Arial
size=2>UPDATE geometry_columns SET f_schema_name = 'public' WHERE f_table_name =
'geo_shapes';</FONT></SPAN></DIV><!-- Converted from text/rtf format -->
<P><SPAN lang=en-us><SPAN class=045021719-16022012><FONT face=Arial
size=2>-Rob</FONT></SPAN></SPAN></P><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Steve
Horn<BR><B>Sent:</B> Thursday, February 16, 2012 12:03 PM<BR><B>To:</B>
postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users] PostGIS
Types Across Schemas<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV style="TEXT-ALIGN: left"><FONT face="Verdana, Arial, Helvetica, sans-serif"
color=#555555><SPAN style="FONT-SIZE: 12px; LINE-HEIGHT: 18px">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".</SPAN></FONT></DIV>
<DIV style="TEXT-ALIGN: left"><FONT face="Verdana, Arial, Helvetica, sans-serif"
color=#555555><SPAN
style="FONT-SIZE: 12px; LINE-HEIGHT: 18px"><BR></SPAN></FONT></DIV>
<DIV style="TEXT-ALIGN: left"><FONT face="Verdana, Arial, Helvetica, sans-serif"
color=#555555>
<DIV style="FONT-SIZE: 12px; LINE-HEIGHT: 18px">CREATE TABLE
import.geo_shapes</DIV>
<DIV style="FONT-SIZE: 12px; LINE-HEIGHT: 18px">(</DIV>
<DIV style="FONT-SIZE: 12px; LINE-HEIGHT: 18px"> geo_shape_id serial NOT
NULL,</DIV>
<DIV style="FONT-SIZE: 12px; LINE-HEIGHT: 18px"> geocode character
varying(9),</DIV>
<DIV style="FONT-SIZE: 12px; LINE-HEIGHT: 18px"> geography
import.geography(MultiPolygon,4326),</DIV>
<DIV style="FONT-SIZE: 12px; LINE-HEIGHT: 18px"> geo_type integer,</DIV>
<DIV style="FONT-SIZE: 12px; LINE-HEIGHT: 18px"> CONSTRAINT
geo_shapes_geo_shape_id_pk PRIMARY KEY (geo_shape_id )</DIV>
<DIV style="FONT-SIZE: 12px; LINE-HEIGHT: 18px">)</DIV>
<DIV style="FONT-SIZE: 12px; LINE-HEIGHT: 18px"><BR></DIV>
<DIV style="FONT-SIZE: 12px; LINE-HEIGHT: 18px">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.</DIV>
<DIV style="FONT-SIZE: 12px; LINE-HEIGHT: 18px"><BR></DIV>
<DIV style="FONT-SIZE: 12px; LINE-HEIGHT: 18px">The problem I'm having is the
geography column is still tied to the "import" namespace, even though the
table's schema is "public".</DIV>
<DIV style="FONT-SIZE: 12px; LINE-HEIGHT: 18px"><BR></DIV>
<DIV style="FONT-SIZE: 12px; LINE-HEIGHT: 18px">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:</DIV>
<DIV style="FONT-SIZE: 12px; LINE-HEIGHT: 18px"><BR></DIV>
<DIV>
<DIV><SPAN style="FONT-SIZE: 12px; LINE-HEIGHT: 18px">ALTER TABLE
geo_shapes</SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 12px; LINE-HEIGHT: 18px">RENAME COLUMN geography TO
geography_temp;</SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 12px; LINE-HEIGHT: 18px"><BR></SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 12px; LINE-HEIGHT: 18px">ALTER TABLE
geo_shapes</SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 12px; LINE-HEIGHT: 18px">ADD COLUMN geography
geography(MultiPolygon,4326);</SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 12px; LINE-HEIGHT: 18px"><BR></SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 12px; LINE-HEIGHT: 18px">UPDATE
geo_shapes</SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 12px; LINE-HEIGHT: 18px">SET geography =
public.ST_GeogFromWKB(import.ST_AsBinary(geography_temp))</SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 12px; LINE-HEIGHT: 18px"><BR></SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 12px; LINE-HEIGHT: 18px">ALTER TABLE
geo_shapes</SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 12px; LINE-HEIGHT: 18px">DROP COLUMN
geography_temp; </SPAN></DIV></DIV>
<DIV><SPAN style="FONT-SIZE: 12px; LINE-HEIGHT: 18px"><BR></SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 12px; LINE-HEIGHT: 18px">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.</SPAN></DIV>
<DIV><SPAN
style="FONT-SIZE: 12px; LINE-HEIGHT: 18px"><BR></SPAN></DIV></FONT></DIV></BODY></HTML>