[gdal-dev] [gdal-users] Mapserver-OGR mysql and WFS Intersect

Tue Apr 5 11:01:31 EDT 2011

Hello,

I Have been playing around with mapserver OGR setting a layer of points
comming from two different sources, first a mysql spatial table, and then a
postgis table, and I have seen that the WFS operation Intersect behaves
weird when using mysql (it works ok with postgres). I am not sure if OGR is
involved in this, I already asked in the mapserver list with no answerd, so,
thanks for reading, and please forgive me if I am asking in the wrong place.

PROBLEM:

In 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:

<?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 diffrent to when a query shall not return any resultst:

<?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

Ubunut

fgs-mapserver_basic_5.6.3-fgs_9.5-linux-i386.bin

mysql 5.1

Best regards,

Jorge and Joaquin
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/gdal-dev/attachments/20110405/5fe04316/attachment-0001.html