<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2900.2802" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>Hello
all,</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>I am confused and
could use a hand (or a slap on the head... whatever :-D ).</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>I have a MapServer
which is acting as a WMS server to my Web Map Application. This works
fine.</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>I have started using
PostGIS to serve the data for my WMS layers, instead of Shapefiles (which I was
previously using). This works fine.</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>I want to query my
layers using the WMS GetFeatureInfo request activated by mouse-click, and return
the results in GML. This works fine.</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2>...</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>BUT, now I want to
do all of the above on a data layer from PostGIS defined with a left outer join,
like so:</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>--- snip
---</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>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"</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>--- snip
---</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>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!</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>Attributes are
returned for other PostGIS layers. </FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>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').</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>Attributes are *not*
returned for this JOIN subquery layer, only.</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>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).</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>I have tried various
reworkings of the above subquery with no further success.</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>This all leads me to
the following question:</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>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>")?</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>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?</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>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?</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>I can't believe I am
the first person to try this...</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>Thanks for your
time, and thanks in advance for any replies.</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>Nick
Floersch</FONT></SPAN></DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=565085608-24022006><FONT face=Arial size=2>(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)</FONT></SPAN></DIV></BODY></HTML>