WMS GetFeatureInfo on joined PostGIS layer
Michael Schulz
mschulz at WEBGIS.DE
Thu Nov 2 15:04:59 EST 2006
Hi,
oops, I was just again reading through the manuals, and for my problem
i'm pretty sure, that the missing oid column/index, when using a
special select clause, prevents mapserver/postgis from returning
results.
Could that be the problem with your postgis-joined-tables problem, James?
Excerpt from postgis docs:
"Mapserver requires unique identifiers for each spatial record when
doing queries, and the PostGIS module of Mapserver uses the PostgreSQL
oid value to provide these unique identifiers."
We'll see. Cheers, Michael
On 11/2/06, Michael Schulz <mschulz at webgis.de> wrote:
> Hi,
>
> unfortunately i have only to report the same problem.
>
> A getfeatureinfo request on a postgis layer with data coming from a
> select that uses a join returns always "no results". Changing the data
> to a simple "select *" gives wonderful results.
>
> Cheers, Michael
>
> P.S.: details, if needed, coming shortly
>
>
> On 10/26/06, James Perrins <james.perrins at gmail.com> wrote:
> > 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
> >
>
>
> --
> -----------------------------------------------------------
> Michael Schulz
> mschulz at webgis.de
>
> in medias res
> Gesellschaft für Informationstechnologie mbH
>
> In den Weihermatten 66
> 79108 Freiburg
>
> Tel +49 (0)761 556959-5
> Fax +49 (0)761 556959-6
>
> http://www.webgis.de / http://www.zopecms.de
> -----------------------------------------------------------
>
--
-----------------------------------------------------------
Michael Schulz
mschulz at webgis.de
in medias res
Gesellschaft für Informationstechnologie mbH
In den Weihermatten 66
79108 Freiburg
Tel +49 (0)761 556959-5
Fax +49 (0)761 556959-6
http://www.webgis.de / http://www.zopecms.de
-----------------------------------------------------------
More information about the mapserver-users
mailing list