WFS 1.1.0 filters not being passed to PostGIS provider

[tomkralidis]

Using 6.4.1 via UbuntuGIS, I'm getting unexpected behaviour when working with WFS 1.1.0 and some Filter combinations.

For case 1, neither the where clause or limit/offset is not being applied to the underlying SQL query.

I've confirmed the same behaviour with @sebastic against a different 6.4.1-based WFS 1.1.0 endpoint.

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

[tomkralidis]

Test data: ogrinfo WFS:http://geo.woudc.org/ows lidar

[tomkralidis]

FYI as per @rouault's advice, moving <ogc:BBOX> as the first predicate within the <ogc:And> block yields the desired result as a workaround. I will try to reproduce on 7.0.0-beta1.

[tomkralidis]

FYI I am able to reproduce this on 7.0.0-beta1 as well.

[rouault]

I cannot replicate your issue, but in the process of trying I found issues : #5112

You should perhaps post your mapfile too.