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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 -----------------------------------------------------------
