<!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
'ActiveSewerManholes'.<BR>prepare_database(): Query error. Error
executing POSTGIS DECLARE (the actual query) statement: 'DECLARE
mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text
from swmu_images WHERE the_geom &&
setSRID('BOX3D(1613612.963 660935.354999998,1734166.788
735581.834000003)'::BOX3D,
find_srid('','swmu_images','the_geom')
)' <br><br></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
'ERROR: column "oid" does not
exist<BR>'<br><br></FONT></SPAN></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2>More
Help:<br><br></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
'&lt;check your .map
file&gt;'.<br><BR>Standard ways of specifiying are
: <br><BR>(1) 'geometry_column from
geometry_table' <br><BR>(2) 'geometry_column from
(&lt;sub query&gt;) as foo using unique &lt;column
name&gt; using SRID=&lt;srid#&gt;'
<br><br></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 'using
unique &lt;column name&gt;' and 'using
SRID=#' clauses in.</FONT></SPAN></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=565085608-24022006><SPAN class=657195209-24022006><FONT
face=Arial color=#0000ff size=2><br><br>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><br><br>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>