<!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.2900.2802" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left> </DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> Nick Floersch <BR><B>Sent:</B> Friday,
February 24, 2006 5:03 AM<BR><B>To:</B> Nick Floersch<BR><B>Subject:</B> RE:
MapServer, PostGIS, Subquery with JOIN, WMS GetFeatureInfo / Followup
Question<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2><SPAN
class=657195209-24022006>After my just previous post to the list, I decided to
try my alternative solution...</SPAN></FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial><FONT size=2><SPAN
class=657195209-24022006><FONT color=#0000ff> "" </FONT></SPAN>Before
anyone suggests the solution of using a view to encapsulate my JOIN so it need
not be in the subquery, I'll just say that I am aware I could try that approach
(though I have not yet done so), but my<SPAN class=657195209-24022006><FONT
color=#0000ff> </FONT></SPAN>curiosity has been piqued by this problem as
it is, so I'm hoping to find out more about the JOINs in subqueries issue I
have. I would also really love to know how MapServer gets the attribute columns
for PostGIS layers.<SPAN class=657195209-24022006><FONT
color=#0000ff> ""</FONT></SPAN></FONT></FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial><FONT size=2><SPAN
class=657195209-24022006></SPAN></FONT></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2>So, I created a view in PostGreSQL which defines
the data as my Join... the View works fine when I execute, for example, "SELECT
* FROM myview" in PostGreSQL.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2>Yet, when I try to use it as a layer source in
MapServer... it just plain doesn't work. I am I really to believe that views
cannot be used by the PostGIS connector in MapServer? I sincerely hope
not.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2>DATA "the_geom from swmu_images" (swmu_images is
the view)</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2>gives me the following
output:</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2>---snip ---</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2><?xml version='1.0' encoding="ISO-8859-1"
standalone="no" ?><BR><!DOCTYPE ServiceExceptionReport SYSTEM "<A
href="http://schemas.opengeospatial.net/wms/1.1.1/exception_1_1_1.dtd">http://schemas.opengeospatial.net/wms/1.1.1/exception_1_1_1.dtd</A>"><BR><ServiceExceptionReport
version="1.1.1"><BR><ServiceException><BR>msDrawMap(): Image handling
error. Failed to draw layer named
&#39;ActiveSewerManholes&#39;.<BR>prepare_database(): Query error. Error
executing POSTGIS DECLARE (the actual query) statement: &#39;DECLARE
mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(force_2d(the_geom)),&#39;NDR&#39;),OID::text
from swmu_images WHERE the_geom &amp;&amp;
setSRID(&#39;BOX3D(1613612.963 660935.354999998,1734166.788
735581.834000003)&#39;::BOX3D,
find_srid(&#39;&#39;,&#39;swmu_images&#39;,&#39;the_geom&#39;)
)&#39; &lt;br&gt;&lt;br&gt;</FONT></SPAN></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2>Postgresql reports the error as
&#39;ERROR: column &quot;oid&quot; does not
exist<BR>&#39;&lt;br&gt;&lt;br&gt;</FONT></SPAN></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2>More
Help:&lt;br&gt;&lt;br&gt;</FONT></SPAN></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2>Error with POSTGIS data variable. You specified
&#39;&amp;lt;check your .map
file&amp;gt;&#39;.&lt;br&gt;<BR>Standard ways of specifiying are
: &lt;br&gt;<BR>(1) &#39;geometry_column from
geometry_table&#39; &lt;br&gt;<BR>(2) &#39;geometry_column from
(&amp;lt;sub query&amp;gt;) as foo using unique &amp;lt;column
name&amp;gt; using SRID=&amp;lt;srid#&amp;gt;&#39;
&lt;br&gt;&lt;br&gt;</FONT></SPAN></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2>Make sure you put in the &#39;using
unique &amp;lt;column name&amp;gt;&#39; and &#39;using
SRID=#&#39; clauses in.</FONT></SPAN></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2>&lt;br&gt;&lt;br&gt;For more
help, please see <A
href="http://postgis.refractions.net/documentation/">http://postgis.refractions.net/documentation/</A>
</FONT></SPAN></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2>&lt;br&gt;&lt;br&gt;Mappostgis.c
- version of Jan 23/2004.</FONT></SPAN></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff
size=2></ServiceException><BR></ServiceExceptionReport></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2>---snip---</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2>The only thing I can think of is... does the
PostGIS connector require the table to have OIDs? It looks that
way.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2>Nick
Floersch</FONT></SPAN></SPAN></DIV></BODY></HTML>