[postgis-users] PostGIS Layer mapfile def issue

[Kralidis,Tom [Burlington]]

(I posted this to mapserver-users as well, however I can't figure out
whether this is a MapServer or PostGIS issue).


Hi,

I have a PostGIS layer with which I'd like to serve via WMS/WFS.  The
PostGIS instance has two tables as LAYER defs.  For one of the tables
(as def'd below in mapfile) as a LAYER object, I'd like to include
fields from another table.  I have a one-to-many relationship which MAY
occur from one table to another (in the case below, service_endpoints is
the one vs. service_resources is the many).

Here's the mapfile snippet:

 LAYER
  NAME "service_resources"
  STATUS ON
  CONNECTIONTYPE postgis
  CONNECTION "user=postgres dbname=devgeodb"
  DATA "the_geom from (select service_endpoints.organization as
organization,service_endpoints.endpoint_getresource as
endpoint_getresource from service_resources left join service_endpoints
on service_resources.service_id = service_endpoints.service_id) as t
using unique resource_id"
  TYPE POLYGON
  DUMP TRUE
  HEADER "service_resources_query_header.html"
  TEMPLATE "service_resources_query_body.html"
  TOLERANCE 30
  METADATA
   "ows_title"    "Service Resources"
   "ows_abstract" "Service Resources"
   "ows_keywordlist" "environment,ogc,cgdi"
   "wms_opaque" "0"
   "ows_extent" "-141.089000 36.392987 -52.089000 89.784987"
   "ows_metadataurl_type" "FGDC"
   "wms_metadataurl_format" "text/html"
   "ows_metadataurl_href" "http://devgeo.cciw.ca/index.html"
   "wms_dataurl_format" "text/html"
   "wms_dataurl_href" "http://devgeo.cciw.ca/index.html"
   "wfs_metadataurl_format" "TXT"
   "gml_include_items" "all"
  END
  PROJECTION
    "init=epsg:4326"
  END
  CLASS
   NAME "Service Resources"
   COLOR 0 0 255
   OUTLINECOLOR 0 0 0
   SYMBOL "upwarddiagonalfill"
   SIZE 1
  END
 END

When I run a WFS GetFeature request, the only features returned are
those which have one-to-one relationships.  The one-to-many related
values are not returned.  My service_resources.resource_id is unique.
Which leads me to believe that my DATA declaration is off somewhere.

Any advice?

Thanks

..Tom