MapServer, PostGIS, Subquery with JOIN, WMS GetFeatureInfo / Followup Question

[Nick Floersch]

________________________________

From: Nick Floersch 
Sent: Friday, February 24, 2006 5:03 AM
To: Nick Floersch
Subject: RE: MapServer, PostGIS, Subquery with JOIN, WMS GetFeatureInfo
/ Followup Question


After my just previous post to the list, I decided to try my alternative
solution...
 
 "" 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
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. ""
 
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.
 
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.
 
DATA "the_geom from swmu_images" (swmu_images is the view)
 
gives me the following output:
---snip ---
<?xml version='1.0' encoding="ISO-8859-1" standalone="no" ?>
<!DOCTYPE ServiceExceptionReport SYSTEM
"http://schemas.opengeospatial.net/wms/1.1.1/exception_1_1_1.dtd">
<ServiceExceptionReport version="1.1.1">
<ServiceException>
msDrawMap(): Image handling error. Failed to draw layer named
&#39;ActiveSewerManholes&#39;.
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;
 
Postgresql reports the error as &#39;ERROR:  column &quot;oid&quot; does
not exist
&#39;&lt;br&gt;&lt;br&gt;
 
More Help:&lt;br&gt;&lt;br&gt;
 
Error with POSTGIS data variable. You specified &#39;&amp;lt;check your
.map file&amp;gt;&#39;.&lt;br&gt;
Standard ways of specifiying are : &lt;br&gt;
(1) &#39;geometry_column from geometry_table&#39; &lt;br&gt;
(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;
 
Make sure you put in the &#39;using unique  &amp;lt;column
name&amp;gt;&#39; and &#39;using SRID=#&#39; clauses in.
 
&lt;br&gt;&lt;br&gt;For more help, please see
http://postgis.refractions.net/documentation/ 
 
&lt;br&gt;&lt;br&gt;Mappostgis.c - version of Jan 23/2004.
 
</ServiceException>
</ServiceExceptionReport>
---snip---
 
The only thing I can think of is... does the PostGIS connector require
the table to have OIDs? It looks that way.
 
Nick Floersch
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20060224/01c9475b/attachment.html