WMS GetFeatureInfo on joined PostGIS layer

Thu Oct 26 00:59:41 PDT 2006

Hi,

This has got me stumped

mapserv481 (or 4.10)

Windows XP / IIS

PostGIS / PostGres 8.1

http://127.0.0.1/scripts/mapserv481.exe?map=C:/Inetpub/wwwroot/WMSServices/marine.map&SERVICE=WMS&SRS=EPSG:4326&VERSION=1.1.1&REQUEST=GetFeatureInfo&X=145&Y=84&QUERY_LAYERS=SurveyPoints&LAYERS=SurveyPoints&BBOX=-5.480804443359375,51.163844261348245,-4.519500732421875,51.76274043738508&WIDTH=700&HEIGHT=700

The above feature Info request works on a PostGIS single data layer
(full layer definition appended at end for info)

(e.g. DATA "the_geom from sample")

But the same request fails when I change the DATA to a joined table

(e.g. DATA "the_geom from (select sample.sample_key as id,
sample.the_geom, species.name as name from sample inner join species
on species.sample_key = sample.sample_key where species.name= 'Mytilus
edulis' ) as foo USING UNIQUE id using srid=4326")

Both data layers display data on the map OK – its just the
GetFeatureInfo that fails on the joined PostGIS layer with the
following error:

GetFeatureInfo results:

Layer 'SurveyPoints'
Content-type: application/vnd.ogc.se_xml

<?xml version='1.0' encoding="ISO-8859-1" standalone="no" ?>
<!DOCTYPE ServiceExceptionReport SYSTEM
"http://schemas.opengeospatial.net/wms/1.1.1/exception_1_1_1.dtd">
<ServiceExceptionReport version="1.1.1">
<ServiceException>
</ServiceException>
</ServiceExceptionReport>

If no data is returned then it works OK (GetFeatureInfo results:
Search returned no results.)
–               so it must be something to do with how either I /
mapserver is failing to interpret / match the joined fields from the
data statement – but I just can't see it

I've also tried using a view in PostGreSQL:
SELECT foo.id, foo.sample_key, foo.the_geom, foo.name
   FROM ( SELECT species.taxon_key AS id, sample.sample_key,
sample.the_geom, species.name
           FROM sample
      JOIN species ON species.sample_key::text = sample.sample_key::text
     WHERE species.name::text = 'Mytilus edulis'::text) foo;

And - DATA "the_geom from mytilus USING UNIQUE id using srid=4326"
or DATA "the_geom from mytilus"

Both these display the data on a map OK – but produce the same error
with GetFeatureInfo

Anyone any ideas ?

Many Thanks

James

LAYER

   NAME "SurveyPoints"

            CONNECTION "user=gisdb password=gisdb dbname=gisdb
host=127.0.0.1 port=5432"

            CONNECTIONTYPE postgis

            DATA "the_geom from sample"

            #DATA "the_geom from mytilus USING UNIQUE id using srid=4326"

            #DATA "the_geom from (select sample.sample_key as id,
sample.the_geom, species.name as name from sample inner join species
on species.sample_key = sample.sample_key where species.name=
'%speciesname%' ) as foo USING UNIQUE id using srid=4326"

            #DATA "the_geom from (select species.taxon_key as id,
sample.sample_key, sample.the_geom, species.name as name from sample
inner join species on species.sample_key = sample.sample_key where
species.name= 'Mytilus edulis' ) as foo USING UNIQUE id using
srid=4326"

   #for WMS attributes

   TEMPLATE "marinetemplate.htm"

   DUMP true

   METADATA

            "wms_title"                    "SurveyPoints"

            "wms_srs"                     "EPSG:27700 EPSG:4326 EPSG:54004"

            "gml_include_items"      "all"

   END

   TYPE POINT

   PROJECTION

        "init=epsg:4326"

   END

   STATUS ON

   LABELITEM "id"

   LABELMAXSCALE 50000

   LABELMINSCALE 0

   LABELCACHE ON

   CLASS

    LABEL

       ANTIALIAS TRUE

       SIZE small

       POSITION AUTO

       OFFSET 1 1

       BUFFER 1

       COLOR 255 0 0

       OUTLINECOLOR 255 255 255

    END

    SYMBOL 'circle'

    SIZE 4

    OUTLINECOLOR 255 0 0

   END # end of class object

   TOLERANCE 100

END # end of layer object

mapserv481

Windows XP / IIS

PostGIS / PostGres 8.1

http://127.0.0.1/scripts/mapserv481.exe?map=C:/Inetpub/wwwroot/WMSServices/marine.map&SERVICE=WMS&SRS=EPSG:4326&VERSION=1.1.1&REQUEST=GetFeatureInfo&X=145&Y=84&QUERY_LAYERS=SurveyPoints&LAYERS=SurveyPoints&BBOX=-5.480804443359375,51.163844261348245,-4.519500732421875,51.76274043738508&WIDTH=700&HEIGHT=700

The above feature Info request works on a PostGIS single data layer
(full layer definition appended at end for info)

(e.g. DATA "the_geom from sample")

But the same request fails when I change the DATA to a joined table

(e.g. DATA "the_geom from (select sample.sample_key as id,
sample.the_geom, species.name as name from sample inner join species
on species.sample_key = sample.sample_key where species.name= 'Mytilus
edulis' ) as foo USING UNIQUE id using srid=4326")

Both data layers display data on the map OK – its just the
GetFeatureInfo that fails on the joined PostGIS layer with the
following error:

GetFeatureInfo results:

Layer 'SurveyPoints'
Content-type: application/vnd.ogc.se_xml

<?xml version='1.0' encoding="ISO-8859-1" standalone="no" ?>
<!DOCTYPE ServiceExceptionReport SYSTEM
"http://schemas.opengeospatial.net/wms/1.1.1/exception_1_1_1.dtd">
<ServiceExceptionReport version="1.1.1">
<ServiceException>
</ServiceException>
</ServiceExceptionReport>

If no data is returned then it works OK (GetFeatureInfo results:
Search returned no results.)
–               so it must be something to do with how either I /
mapserver is failing to interpret / match the joined fields from the
data statement – but I just can't see it

I've also tried using a view in PostGreSQL:
SELECT foo.id, foo.sample_key, foo.the_geom, foo.name
   FROM ( SELECT species.taxon_key AS id, sample.sample_key,
sample.the_geom, species.name
           FROM sample
      JOIN species ON species.sample_key::text = sample.sample_key::text
     WHERE species.name::text = 'Mytilus edulis'::text) foo;

And - DATA "the_geom from mytilus USING UNIQUE id using srid=4326"
or DATA "the_geom from mytilus"

Both these display the data on a map OK – but produce the same error
with GetFeatureInfo

Anyone any ideas ?

Many Thanks

James

LAYER

   NAME "SurveyPoints"

            CONNECTION "user=gisdb password=gisdb dbname=gisdb
host=127.0.0.1 port=5432"

            CONNECTIONTYPE postgis

            DATA "the_geom from sample"

            #DATA "the_geom from mytilus USING UNIQUE id using srid=4326"

            #DATA "the_geom from (select sample.sample_key as id,
sample.the_geom, species.name as name from sample inner join species
on species.sample_key = sample.sample_key where species.name=
'%speciesname%' ) as foo USING UNIQUE id using srid=4326"

            #DATA "the_geom from (select species.taxon_key as id,
sample.sample_key, sample.the_geom, species.name as name from sample
inner join species on species.sample_key = sample.sample_key where
species.name= 'Mytilus edulis' ) as foo USING UNIQUE id using
srid=4326"

   #for WMS attributes

   TEMPLATE "marinetemplate.htm"

   DUMP true

   METADATA

            "wms_title"                    "SurveyPoints"

            "wms_srs"                     "EPSG:27700 EPSG:4326 EPSG:54004"

            "gml_include_items"      "all"

   END

   TYPE POINT

   PROJECTION

        "init=epsg:4326"

   END

   STATUS ON

   LABELITEM "id"

   LABELMAXSCALE 50000

   LABELMINSCALE 0

   LABELCACHE ON

   CLASS

    LABEL

       ANTIALIAS TRUE

       SIZE small

       POSITION AUTO

       OFFSET 1 1

       BUFFER 1

       COLOR 255 0 0

       OUTLINECOLOR 255 255 255

    END

    SYMBOL 'circle'

    SIZE 4

    OUTLINECOLOR 255 0 0

   END # end of class object

   TOLERANCE 100

END # end of layer object