MapServer, PostGIS, Subquery with JOIN,
WMS GetFeatureInfo / Followup Question
pramsey at REFRACTIONS.NET
Sun Feb 26 12:57:34 EST 2006
And once you've done all that, add some notes to the documentation on
the mapserver site, for the next intrepid soul :)
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
> Give it a try and let me know. If It does not work, I can dig more
> into it.
> Paul Ramsey wrote:
>> 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.
>> 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
>>> 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
>>> Thoughts, ideas?
>>> 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
>>> 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.
>>> 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
>>> > 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
> Phone: (613) 565-5056 (ext 14)
> Fax: (613) 565-0925
More information about the mapserver-users