Skip to content

Instantly share code, notes, and snippets.

@tomkralidis

tomkralidis/MapServer-filter-issue.md

Last active August 29, 2015 14:23
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save tomkralidis/637241066b4719c04dec to your computer and use it in GitHub Desktop.
Save tomkralidis/637241066b4719c04dec to your computer and use it in GitHub Desktop.
Download ZIP
Raw
MapServer-filter-issue.md

MapServer WFS Filter Issue

Thread: http://osgeo-org.1560.x6.nabble.com/WFS-1-1-0-filter-behaviour-issues-td5211672.html

Environment

  • MapServer: 6.4.1 via UbuntuGIS

The issue occurs on a LAYER with PostGIS connection against point data.

LAYER.DATA snippet:

CONNECTION "host=localhost dbname=mydb user=foo password=bar"
CONNECTIONTYPE POSTGIS
PROCESSING "CLOSE_CONNECTION=DEFER"
DATA "the_geom from (select * from lidar order by instance_datetime) as subquery using unique data_payload_id using srid=4326"
DEBUG 1

Test Cases:

1: BBOX And PropertyIsBetween And PropertyIsEqualTo

Request: mapserv QUERY_STRING="map=/path/to/foo.map&service=WFS&version=1.1.0&request=GetFeature&outputformat=GeoJSON&typename=lidar&filter=<ogc:Filter><ogc:And><ogc:PropertyIsBetween><ogc:PropertyName>instance_datetime</ogc:PropertyName><ogc:LowerBoundary>1979-01-01 00:00:00</ogc:LowerBoundary><ogc:UpperBoundary>1991-12-31 23:59:59</ogc:UpperBoundary></ogc:PropertyIsBetween><ogc:BBOX><PropertyName>msGeometry</PropertyName><Box srsName="EPSG:4326"><coordinates>-86.8359375,40.97989806962013 -70.81787109374999,46.52863469527167</coordinates></Box></ogc:BBOX><PropertyIsEqualTo matchCase="false"><PropertyName>instrument_name</PropertyName><Literal>DIAL</Literal></PropertyIsEqualTo></ogc:And></ogc:Filter>&startindex=0&maxfeatures=100"

MapServer log: msPostGISLayerWhichShapes query: select "data_payload_id","data_payload_uri","instance_datetime","url","agency","platform_type","platform_id","platform_name","gaw_id","instrument_name","instrument_model","instrument_number","ozone_summary_altitudes","ozone_summary_minaltitude","ozone_summary_maxaltitude","ozone_summary_startdate","ozone_summary_starttime","ozone_summary_enddate","ozone_summary_endtime","ozone_summary_pulsesaveraged","latest_observation",encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR'),'hex') as geom,"data_payload_id" from (select * from lidar order by instance_datetime) as subquery where the_geom && ST_GeomFromText('POLYGON((-180 -90,-180 90,180 90,180 -90,-180 -90))',4326)

Result: where clause not applied in SQL query, limit / offset not applied to SQL query

2: BBOX And PropertyIsBetween

Request: mapserv QUERY_STRING="map=/path/to/foo.map&service=WFS&version=1.1.0&request=GetFeature&outputformat=GeoJSON&typename=lidar&filter=<ogc:Filter><ogc:And><ogc:PropertyIsBetween><ogc:PropertyName>instance_datetime</ogc:PropertyName><ogc:LowerBoundary>1979-01-01 00:00:00</ogc:LowerBoundary><ogc:UpperBoundary>1991-12-31 23:59:59</ogc:UpperBoundary></ogc:PropertyIsBetween><ogc:BBOX><PropertyName>msGeometry</PropertyName><Box srsName="EPSG:4326"><coordinates>-86.8359375,40.97989806962013 -70.81787109374999,46.52863469527167</coordinates></Box></ogc:BBOX></ogc:And></ogc:Filter>&startindex=0&maxfeatures=100"

MapServer log: msPostGISLayerWhichShapes query: select "data_payload_id","data_payload_uri","instance_datetime","url","agency","platform_type","platform_id","platform_name","gaw_id","instrument_name","instrument_model","instrument_number","ozone_summary_altitudes","ozone_summary_minaltitude","ozone_summary_maxaltitude","ozone_summary_startdate","ozone_summary_starttime","ozone_summary_enddate","ozone_summary_endtime","ozone_summary_pulsesaveraged","latest_observation",encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR'),'hex') as geom,"data_payload_id" from (select * from lidar order by instance_datetime) as subquery where the_geom && ST_GeomFromText('POLYGON((-86.8359375 40.9798980696201,-86.8359375 46.5286346952717,-70.81787109375 46.5286346952717,-70.81787109375 40.9798980696201,-86.8359375 40.9798980696201))',4326) and ( ("instance_datetime" BETWEEN '1979-01-01 00:00:00' AND '1991-12-31 23:59:59')) limit 100 offset 0

Result: as expected

3: PropertyIsBetween And PropertyIsEqualTo (no BBOX)

Request: mapserv QUERY_STRING="map=/path/to/foo.map&service=WFS&version=1.1.0&request=GetFeature&outputformat=GeoJSON&typename=lidar&filter=<ogc:Filter><ogc:And><ogc:PropertyIsBetween><ogc:PropertyName>instance_datetime</ogc:PropertyName><ogc:LowerBoundary>1979-01-01 00:00:00</ogc:LowerBoundary><ogc:UpperBoundary>1991-12-31 23:59:59</ogc:UpperBoundary></ogc:PropertyIsBetween><PropertyIsEqualTo matchCase="false"><PropertyName>instrument_name</PropertyName><Literal>DIAL</Literal></PropertyIsEqualTo></ogc:And></ogc:Filter>&startindex=0&maxfeatures=100"

MapServer log: msPostGISLayerWhichShapes query: select "data_payload_id","data_payload_uri","instance_datetime","url","agency","platform_type","platform_id","platform_name","gaw_id","instrument_name","instrument_model","instrument_number","ozone_summary_altitudes","ozone_summary_minaltitude","ozone_summary_maxaltitude","ozone_summary_startdate","ozone_summary_starttime","ozone_summary_enddate","ozone_summary_endtime","ozone_summary_pulsesaveraged","latest_observation",encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR'),'hex') as geom,"data_payload_id" from (select * from lidar order by instance_datetime) as subquery where the_geom && ST_GeomFromText('POLYGON((-180 -90,-180 90,180 90,180 -90,-180 -90))',4326) and ( ( ("instance_datetime" BETWEEN '1979-01-01 00:00:00' AND '1991-12-31 23:59:59') And (lower("instrument_name") = lower('DIAL') ) ) ) limit 100 offset 0

Result: as expected

4: PropertyIsBetween And PropertyIsEqualTo And PropertyIsEqualTo (no BBOX)

Request: mapserv QUERY_STRING="map=/path/to/foo.map&service=WFS&version=1.1.0&request=GetFeature&outputformat=GeoJSON&typename=lidar&filter=<ogc:Filter><ogc:And><ogc:PropertyIsBetween><ogc:PropertyName>instance_datetime</ogc:PropertyName><ogc:LowerBoundary>1979-01-01 00:00:00</ogc:LowerBoundary><ogc:UpperBoundary>1991-12-31 23:59:59</ogc:UpperBoundary></ogc:PropertyIsBetween><PropertyIsEqualTo><PropertyName>platform_id</PropertyName><Literal>065</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>platform_type</PropertyName><Literal>STN</Literal></PropertyIsEqualTo><PropertyIsEqualTo matchCase="false"><PropertyName>instrument_name</PropertyName><Literal>DIAL</Literal></PropertyIsEqualTo></ogc:And></ogc:Filter>&startindex=0&maxfeatures=100"

MapServer log:

msPostGISLayerWhichShapes query: select "data_payload_id","data_payload_uri","instance_datetime","url","agency","platform_type","platform_id","platform_name","gaw_id","instrument_name","instrument_model","instrument_number","ozone_summary_altitudes","ozone_summary_minaltitude","ozone_summary_maxaltitude","ozone_summary_startdate","ozone_summary_starttime","ozone_summary_enddate","ozone_summary_endtime","ozone_summary_pulsesaveraged","latest_observation",encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR'),'hex') as geom,"data_payload_id" from (select * from lidar order by instance_datetime ASC) as subquery where the_geom && ST_GeomFromText('POLYGON((-180 -90,-180 90,180 90,180 -90,-180 -90))',4326) and ( ( ("instance_datetime" BETWEEN '1979-01-01 00:00:00' AND '1991-12-31 23:59:59') And ( ("platform_id"= '065') And ( ("platform_type"= 'STN') And (lower("instrument_name") = lower('DIAL') ) ) ) ) ) limit 100 offset 0

Result: as expected

@sdlime
Copy link

sdlime commented Jun 18, 2015

What happens in MS 7.0? Got any sample data?

@tomkralidis
Copy link
Author

Confirmed with @sebastic same behaviour.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment