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
'ActiveSewerManholes'.
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>
 
Postgresql reports the error as 'ERROR:  column "oid" does
not exist
'<br><br>
 
More Help:<br><br>
 
Error with POSTGIS data variable. You specified '&lt;check your
.map file&gt;'.<br>
Standard ways of specifiying are : <br>
(1) 'geometry_column from geometry_table' <br>
(2) 'geometry_column from (&lt;sub query&gt;) as foo using
unique &lt;column name&gt; using SRID=&lt;srid#&gt;'
<br><br>
 
Make sure you put in the 'using unique  &lt;column
name&gt;' and 'using SRID=#' clauses in.
 
<br><br>For more help, please see
http://postgis.refractions.net/documentation/ 
 
<br><br>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.htm>