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

[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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20060224/6a0437f0/attachment.html