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

[Paul Ramsey]

Nick,

And once you've done all that, add some notes to the documentation on  
the mapserver site, for the next intrepid soul :)
Thanks Assefa!

Paul

On 26-Feb-06, at 9:55 AM, Yewondwossen Assefa wrote:

> Hi There,
>
>  From what I can see in the code, here is what you should do :
>
>    * when doing the GetFeatureInfo use the request parametetr  
> FEATURE_COUNT and set it to be above 1. The default is 1. This  
> would trigger the nquery.
>    * define on your server FEATURE_INFO_MIME_TYPE metadata to  
> something like text/html
>    * do your wms request with info_format=test/html (same mime type  
> that the one defined in the map)
>
>   This conditions should normally trigger all the logic used when  
> doing regular query with the mapserver cgi (with the template  
> processing).
>
>  Give it a try and let me know. If It does not work, I can dig more  
> into it.
>
> Later,
>
>
> Paul Ramsey wrote:
>> Nick,
>> The answer to this takes either Assefa's input, or a browse of  
>> the  source code to find out what the GetFeatureInfo is going.  It  
>> is  probably doing a 'query' rather than an 'nquery', so having a  
>> multi- item template may make no difference at all.  The WMS spec  
>> is silent  on what the actual behavior of the GetFeatureInfo  
>> should be, so it is  very much an implementation question.
>> Paul
>> On 24-Feb-06, at 12:59 PM, Nick Floersch wrote:
>>> I thought of a way to rephrase/reask the question I am now stuck  
>>> with.
>>>
>>> I was reading through my MapServer book ('Beginning MapServer')  
>>> on  Queries and Joins, to see if mr. Kropla had any suggestions.
>>>
>>> He wrote that, in doing regular MapServer JOINs, if you want to   
>>> have a JOIN produce one-to-many results, you need to specify a   
>>> template for the JOIN to format each record beyond the first one   
>>> that is returned into the final HTML result. Otherwise, only the   
>>> first record will be returned.
>>>
>>> How does this principal apply to the GML generated by   
>>> GetFeatureInfo requests? Obviously we don't need to define a  
>>> valid  HTML template to output the data... but how do  
>>> GetFeatureInfo  requests deal with one-to-many situations, and  
>>> does the output  format make any difference on how it is handled?  
>>> If I have my  GetFeatureInfo request return HTML rather than GML,  
>>> can I use  templates and have it handle a one-to-many arrangement  
>>> succesfully?
>>>
>>> Thoughts, ideas?
>>>
>>> Thanks!
>>> Nick Floersch
>>>
>>> From: Nick Floersch
>>>
>>> Hello Paul, Steve, Jeff, and other MapServer users,
>>>
>>> Thanks for the replies.
>>>
>>> By adding an appropriate 'using unique' clause to my DATA entry  
>>> in  the mapfile, I got my layer based on a view to draw. I am  
>>> glad that  views can be used.
>>>
>>>
>>> There is a trick that had to be realized. At first, I put in a   
>>> 'using unique the_geom' clause which of course assumed that my   
>>> geometry field was unique. But, because the source of my layer is  
>>> a  view (or a subquery in its previous life) which has a left  
>>> outer  join in it, the geometry column is far from unique. This  
>>> is what I  thought I wanted - to generate a layer which had  
>>> multiple points at  the same locations with different  
>>> attributes... a series of  attributes for a given point location.  
>>> In my case, the idea is that  the feature point can have images  
>>> associated with it from a table  of images. So, a left outer join  
>>> on that table gives me a layer  with duplicate points that have  
>>> different values for the image name  attribute.
>>>
>>> Anyway, initially, after I realized that 'the_geom' is not a  
>>> unique  field for me, I switched to using a field that is unique  
>>> in my  view, and things came to life. My GetFeatureInfo requests  
>>> suddenly  started returning attributes, and life looked good.
>>>
>>> But no. Not perfect. What I had in mind has not worked quite  
>>> right  - my GetFeatureInfo tool clicks on a point feature, and I  
>>> get a  list of attributes, by layer, for each feature under the  
>>> pointer.  Except, not all the duplicate point features are  
>>> returned. The best  I can think to describe it is this: I have a  
>>> stack of points all  defined in the same layer, and I click on  
>>> the stack, and only the  top point in that stack is returned. I  
>>> was hoping it would return  data for each of those points in the  
>>> stack.
>>>
>>> So, is there some way I can make this work better? Am I totally   
>>> barking up the wrong tree? I have one GetFeatureInfo request  
>>> that  needs to return multiple values for the same field of a  
>>> given  feature, based on a join...
>>>
>>>
>>> Thanks for any thoughts and help!
>>>
>>> Nick Floersch
>>>
>>> -------
>>>
>>> If you have the option, please don't use oid, use a primary key   
>>> (like the 'gid' created by shp2pgsql) as your unique key.  
>>> Primary  keys already have indexes, oids do not. Primary keys  
>>> show up  automatically in a "select *" query, oids do not. oids are
>>>
>>> deprecated in pgsql and not available by default in pgsql 8.1.
>>>
>>> Basically oid is now a deadend, and we need to start erasing all   
>>> uses of them.
>>>
>>> P
>>>
>>> -------
>>>
>>> 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.
>>>
>>> --------
>>>
>>> This is on my "figure out some day myself" list, too. I'm doing  
>>> the  view thing right now, but I'd like to not have to create  
>>> views for  everything.
>>>
>>> > The only thing I can think of is... does the PostGIS connector   
>>> require
>>>
>>> > the table to have OIDs? It looks that way.
>>>
>>> Yes. It needs some unique field in order to randomly access an   
>>> individual rows, it just so happens that OID is a convenient way  
>>> to  get that in most cases. You can also specify your own unique  
>>> column  name with "using unique <column name>" if your view  
>>> doesn't have an  OID column but you have some other key you can  
>>> use. I just pull in  the OID from the main geometry-containing  
>>> table when I define the  view.
>>>
>>> -- 
>>>
>>> Jeff Hoffmann
>
>
> -- 
> ----------------------------------------------------------------
> Assefa Yewondwossen
> Software Analyst
>
> Email: assefa at dmsolutions.ca
> http://www.dmsolutions.ca/
>
> Phone: (613) 565-5056 (ext 14)
> Fax:   (613) 565-0925
> ----------------------------------------------------------------
>
>