Some WFS Queries return unexpected rusults when using MySQL layer

[ykcorse]

In a wfs layer where the data is retrieved from a mysql table, the intersect filter only returns a result list when the first feature(the first inserted record in the table) is part of the result set. If the filter shall return results, but the first feature is not among them, then we get not list of features, just this:

<?xml version='1.0' encoding="ISO-8859-1" ?> <wfs:FeatureCollection...>

<gml:boundedBy>

<gml:Box srsName="EPSG:4326">

<gml:coordinates>-1.000000,-1.000000 -1.000000,-1.000000</gml:coordinates>

</gml:Box>

</gml:boundedBy>

</wfs:FeatureCollection>

Which is different to when a query shall really not return any results:

<?xml version='1.0' encoding="ISO-8859-1" ?> <wfs:FeatureCollection...">

<gml:boundedBy>

<gml:null>missing</gml:null>

</gml:boundedBy>

</wfs:FeatureCollection>

TEST:

1. Create Spatial Data

CREATE DATABASE mmi_spatial_db; USE mmi_spatial_db; CREATE TABLE positionReal (oid LONG, point POINT); INSERT INTO positionReal(oid, point) VALUES (3, GeomFromText('POINT(-10 -10)',4326)); INSERT INTO positionReal(oid, point) VALUES (2, GeomFromText('POINT(10 10)',4326)); INSERT INTO positionReal(oid, point) VALUES (1, GeomFromText('POINT(0 20)',4326));

2. Map file:

MAP

NAME MMI_WFS UNITS METERS TRANSPARENT ON IMAGECOLOR 0 0 0 IMAGETYPE PNG SHAPEPATH "." SYMBOLSET "../symbols.sym" STATUS ON

WEB

IMAGEPATH "/opt/fsg/tmp/" IMAGEURL "/opt/fsg/" METADATA

"wfs_title" "WFS MMI Server for MapServer" ## REQUIRED "wfs_onlineresource" "​http://localhost:8080/cgi-bin/mywfs?" ## Recommended "wfs_srs" "EPSG:4326"## Recommended "wfs_abstract" "This text describes my WFS service." ## Recommended "wfs_schemas_location" "​http://schemas.opengeospatial.net"

END

END

PROJECTION

"init=epsg:4326"

END

LAYER

NAME "PositionReal" STATUS ON TYPE POINT

CONNECTIONTYPE OGR CONNECTION "MySQL:mmi_spatial_db,user=mmi,password=*****,host=127.0.0.1,port=3306" DATA "SELECT point from positionReal" #CONNECTIONTYPE POSTGIS #CONNECTION "host=127.0.0.1 port=5432 dbname=mmi_db user=postgres password=postgres" #DATA "point from positionreal"

DUMP TRUE ## REQUIRED #TOLERANCE 200 #TOLERANCEUNITS PIXELS

METADATA

#"wfs_featureid" "oid" "wfs_srs" "epsg:4326" "wfs_onlineresource" "​http://localhost:8080/cgi-bin/mywfs?" ## Recommended "wfs_title" "PositionReal" "gml_include_items" "all" ## Optional (serves all attributes for lay "gml_surface_occurances" "0,UNBOUNDED" "wfs_extent" "-180 -90 180 90" "gml_include_items" "all"

END

END

END # Map File

3. Intersect Filter:

• This one works because point (-10,-10) is part of the resultset.

​http://localhost:8080/cgi-bin/mywfs?SERVICE=WFS&VERSION=1.0.0&request=getfeature&typename=PositionReal&Filter= <Filter xmlns="​http://www.opengis.net/ogc" xmlns:gml="http://www.opengis.net/gml"><Intersects><PropertyName>point</PropertyName> <gml:Polygon><gml:outerBoundaryIs><gml:LinearRing><gml:coordinates>-180,-90 -180,90 180,90 180,-90 -180,-90</gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Polygon></Intersects></Filter>

• This one works, point (-10,-10) is part of the resultset

​http://localhost:8080/cgi-bin/mywfs?SERVICE=WFS&VERSION=1.0.0&request=getfeature&typename=PositionReal&Filter= <Filter xmlns="​http://www.opengis.net/ogc" xmlns:gml="http://www.opengis.net/gml"><Intersects><PropertyName>point</PropertyName> <gml:Polygon><gml:outerBoundaryIs><gml:LinearRing><gml:coordinates>-11,-11 -11,-9 -9,-9 -9,-11 -11,-11</gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Polygon></Intersects></Filter>

• This query fails, it should return one feature, but we get the weird behavior because point (-10,-10) is not part of the resultset

​http://localhost:8080/cgi-bin/mywfs?SERVICE=WFS&VERSION=1.0.0&request=getfeature&typename=PositionReal&Filter= <Filter xmlns="​http://www.opengis.net/ogc" xmlns:gml="http://www.opengis.net/gml"><Intersects><PropertyName>point</PropertyName> <gml:Polygon><gml:outerBoundaryIs><gml:LinearRing><gml:coordinates>9,9 9,11 11,11 11,9 9,9</gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Polygon></Intersects></Filter>

Notice that I use gml:polygon instead of gml:box to be able to test this with mapserver in trunk (which works with gml:box) but also with mapserver 5.6.3 (fgs-installer, which dont work with intersect and gml:box)

The problem does not ocurr if the database in use is postgres

./createdb -U postgres -T template_postgis mmi_db; CREATE TABLE positionReal (oid int); SELECT AddGeometryColumn('positionreal', 'point', 4326,'POINT',2); INSERT INTO positionReal(oid, point) VALUES (3, GeomFromText('POINT(-10 -10)',4326)); INSERT INTO positionReal(oid, point) VALUES (2, GeomFromText('POINT(10 10)',4326)); INSERT INTO positionReal(oid, point) VALUES (1, GeomFromText('POINT(0 20)',4326));

SYSTEMS:

I tested this in the following systems:

OpenSuse 11 fgs-mapserver_basic_5.6.3-fgs_9.5-linux-i386.bin and building from the sources (branch 5-6) mysql Ver 14.12 Distrib 5.0.67, for suse-linux-gnu (i686) using readline 5.2 and mysql 5.5 too

SLES 11 fgs-mapserver_basic_5.6.3-fgs_9.5-linux-i386.bin mysql Ver 14.14 Distrib 5.1.43, for unknown-linux-gnu (x86_64) using readline 5.1

Ubuntu fgs-mapserver_basic_5.6.3-fgs_9.5-linux-i386.bin mysql 5.1

[ykcorse]

I have found out that composed bounding box queries using AND return unexpected results too (when using mysql, they work correctoy using postgres):

Test Queries.

1.Returns only the first feature (-10,-10) and that feature is out of the boundinx boxes

​http://localhost:8080/cgi-bin/mywfs?SERVICE=WFS&VERSION=1.0.0&request=getfeature&typename=PositionReal&Filter= <Filter>

<And>

<BBOX>

<PropertyName>msGeometry</PropertyName> <Box>

<coordinates decimal="." cs="," ts=" ">-7.874614621788295,14.799588929907502 15.519013391777994,28.776978474820144</coordinates>

</Box>

</BBOX> <BBOX>

<PropertyName>msGeometry</PropertyName> <Box>

<coordinates decimal="." cs="," ts=" ">-100.82219958499486,-57.14285725714286 100.82219958499486,57.14285725714286</coordinates>

</Box>

</BBOX>

</And>

</Filter>

2. Returns the 3 features, but feature 1 (-10,-10) is out of the bounding box

​http://localhost:8080/cgi-bin/mywfs?SERVICE=WFS&VERSION=1.0.0&request=getfeature&typename=PositionReal&Filter= <Filter>

<And>

<BBOX>

<PropertyName>msGeometry</PropertyName> <Box>

<coordinates decimal="." cs="," ts=" ">-7.874614621788295,14.799588929907502 15.519013391777994,28.776978474820144</coordinates>

</Box>

</BBOX>

</And>

</Filter>

3. This one works :)

​http://localhost:8080/cgi-bin/mywfs?SERVICE=WFS&VERSION=1.0.0&request=getfeature&typename=PositionReal&Filter= <Filter>

<BBOX>

<PropertyName>msGeometry</PropertyName> <Box>

<coordinates decimal="." cs="," ts=" ">-7.874614621788295,14.799588929907502 15.519013391777994,28.776978474820144</coordinates>

</Box>

</BBOX>

</Filter>

[ykcorse]

This problem was reported using mapserver installed with fgs-mapserver_basic_5.6.3-fgs_9.5-linux-i386.bin . Building mapserver from the sources against dependecies contained in that package (proj, gdal, geos,...) did not solve anything. But building mapserver against binary packages downloaded from opensuse repositories (libgeos-3.1.0-0.pm.1, gdal, ...) did solve the problem.