MapServer, PostGIS, Subquery with JOIN, WMS GetFeatureInfo request

[Nick Floersch]

Hello all,
 
I am confused and could use a hand (or a slap on the head... whatever
:-D ).
 
I have a MapServer which is acting as a WMS server to my Web Map
Application. This works fine.
 
I have started using PostGIS to serve the data for my WMS layers,
instead of Shapefiles (which I was previously using). This works fine.
 
I want to query my layers using the WMS GetFeatureInfo request activated
by mouse-click, and return the results in GML. This works fine.
 
...
 
BUT, now I want to do all of the above on a data layer from PostGIS
defined with a left outer join, like so:
--- snip ---
DATA "the_geom from (select * from layer_swmus s left outer join (select
*, ('SWMU '||object_oid) as swmu_id from objects where object_type =
'SWMU') q on q.swmu_id = s.swmu_id) as myquery using unique the_geom
using srid = 2258"
--- snip ---
 
When I execute this query by hand in PostGIS/PostGreSQL, I get a big ol'
table o' results, like I expect. In other words, the SQL runs fine.
 
Yet, when I execute the GetFeatureInfo request on one of the features
generated by this subquery, no attributes are returned. The layer itself
renders properly and shows me a bunch of points like I would expect it
to do - points with no attributes!
 
Attributes are returned for other PostGIS layers. 
Attributes are returned for other PostGIS layers upon which the join
query above is based (i.e. a GetFeatureInfo query on the data layer for
'layer_swmus' returns all attributes for the the table 'layer_swmus').
Attributes are *not* returned for this JOIN subquery layer, only.
Not only are no attributes returned, but the GML returned contains no
GML, really... when the GML is viewed in Firefox, I get an error about
there being no elements in the XML file. Normally, if no data was
returned, I would just get Firefox to display an empty XML tree (one
node with no children).
 
I have tried various reworkings of the above subquery with no further
success.
 
This all leads me to the following question:
 
Given that the 'DATA' label in MapServer mapfile, when used with
PostGIS, is only supposed to look at one column (the geometry column),
how does MapServer know anything about the attributes for the layer at
all. It *seems* to me (and I know I don't know everything here), that if
the definition for the DATA label is "<geometry column name> from <table
name or subquery>" that this translates into a SQL statement like
"SELECT <geometry column name> FROM <table name or subquery>" upon with
MapServer renders the layer. But if that were so, then where would any
of the attributes for the layer come from? Does MapServer execute the
"SELECT <geometry column name> FROM <table name or subquery>" query to
get the geometry for the map layer, and then re-execute the query with
the <geometry column name> item replaced by '*' (i.e. "SELECT <geometry
column name> FROM <table name or subquery>" becomes "SELECT * FROM
<table name or subquery>")?
 
Am I doing something wrong with my JOIN subquery such that I've broken
my layer, or does the JOIN throw MapServer's PostGIS connector for a
loop?
Do any of the wise gurus who frequent the list have any suggestions on
how I can get my attributes from the JOIN subquery to show up in my
GetFeatureInfo request's returned GML?
 
I can't believe I am the first person to try this...
 
Before anyone suggests the solution of using a view to encapsulate my
JOIN so it need not be in the subquery, I'll just say that I am aware I
could try that approach (though I have not yet done so), but my
curiosity has been piqued by this problem as it is, so I'm hoping to
find out more about the JOINs in subqueries issue I have. I would also
really love to know how MapServer gets the attribute columns for PostGIS
layers.
 
Thanks for your time, and thanks in advance for any replies.
 
Nick Floersch
 
(System: Debian Linux SID, PostGreSQL 8.1.3, PostGIS 1.1.1, MapServer
4.8.1, Apache 2.0.55, PHP 5.1.2, MapBuilder v1.0rc1, Firefox 1.5.1)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20060224/69455a41/attachment.html