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

Sun Feb 26 10:16:20 PST 2006

Nick Floersch wrote:
> Hi!
> 
> Thanks so much for looking into this. I was trying to dig through the
> code as well. I noticed the "FEATURE_COUNT" parameter, and also a
> different mode setting ... i.e. mode = MS_SINGLE vs MS_MULTIPLE, but I
> couldn't really grasp the details enough to know if that had anything to
> do with my situation. It sounds like you are saying it does.
> 
> Are you also suggesting that, in theory, if I use text/html as my output
> format, and have a one-to-many type relationship, as a JOIN might, the
> HTML generated from the templates will have one body entry per record
> (as a one-to-many JOIN might)?
>

> Assefa, are you the query maintainer? Or query guru? What is your role
> in all this? Who would I e-mail to discuss/propose changes to the GML
> output so that the records returned via GML are the same as returned via
> HTML or Text?
>

Nick

   From looking into the code, the template processing code seemd to 
kick in when those circumstances that I described are statisfied. I am 
not really familiar with the query/template precessing/join logic. I am 
assuming that if you manage to make things work with regular mapserver 
query by defining your join and templates properly, It should also work 
when doing a GetFeatureInfo.
  Steve Lime would be the autorite on the query stuff and also for the 
GML output.  I am more familiar with the wms code.  I think you should 
adress your propositions/changes to the list.

> Thank you again! I will let you know how this turns out.
> 
> Nick Floersch
> 
> -----Original Message-----
> From: Yewondwossen Assefa [mailto:assefa at dmsolutions.ca] 
> Sent: Sunday, February 26, 2006 12:56 PM
> To: Nick Floersch
> Cc: Paul Ramsey; MAPSERVER-USERS at LISTS.UMN.EDU
> Subject: Re: [UMN_MAPSERVER-USERS] MapServer, PostGIS, Subquery with
> JOIN, WMS GetFeatureInfo / Followup Question
> 
> 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
----------------------------------------------------------------