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

[Stephen Woodbridge]

Nick,

I seem to remember a post from one of the postGIS guys awile back the 
you needed to add an entry in the geometry_columns table for the view.

-Steve W.

Nick Floersch wrote:
>  
> 
> ------------------------------------------------------------------------
> *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