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 
> &#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