MapServer, PostGIS, Subquery with JOIN,
WMS GetFeatureInfo / Followup Question
Nick at STONE-ENV.COM
Fri Feb 24 15:12:37 EST 2006
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
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
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
Thanks for any thoughts and help!
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
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.
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
> 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.
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the mapserver-users