WFS 2.0 paging problem with Postgis and BBOX

[mraffero]

MapServer ver 7.6.1

Mapserver requests features from Postgis with a query similar to this:

select "id"::text,ST_AsText("geometry") as geom,"id"::text from table
where 
"geometry" && ST_GeomFromText('POLYGON((415885.464144915 4936233.565163,415885.464144915 4938751.56294925,419080.0342134 4938751.56294925,419080.0342134 4936233.565163,415885.464144915 4936233.565163))',32632) 
limit 1001 offset 2000;

The use of && for better performace will only checking if the bounding boxes of geometry intersects.
docs/overlaps_geometry_box2df.html

In the file mapquery.c#L1252
a more restrictive check is then performed, for exmple msIntersectPolygons
which in same case can exclude some geometries returned from db breaking the paging mechanism.

In the images you can see a case of a geometry returned by Postgis but excluded from Mapserver check,
which breaks the feature count for the pagination calculation.

[sdlime]

@rouault, any comment on this one and #6230 from a WFS perspective? It seems like the MapServer check in mapquery.c is correct. I'm not sure how to address... --Steve

[geographika]

When playing with the SQL Server driver I tried using FILTER rather than STIntersects.

FILTER seems similar to &&:

..a fast, index-only intersection method to determine if a geometry instance intersects another geometry instance, assuming an index is available.

Returns 1 if a geometry instance potentially intersects another geometry instance. This method may produce a false positive return, and the exact result may be plan-dependent. Returns an accurate 0 value (true negative return) if there is no intersection of geometry instances found.

When getting shapes for display, extra features are fine as they simply get dropped when drawing. WFS however would return these false positives.

Possible options:

• Use ST_Intersects in PostGIS - however this would likely lead to a slowdown of all requests e.g. WMS
• Create two paths depending on the request type - && for WMS and ST_Intersects for WFS/queries - although would this have to be implemented in all drivers?
• Run the mapquery.c check at all stages of the request (WFS makes one for a paging count, and then another for the actual features requested - see Support WFS Paging at database level for MSSQL Driver #5842 for some details)

[geographika]

It would also be worth trying to implement wfs_use_default_extent_for_getfeature (see #5994) for the PostGIS driver to avoid spatial filters unless set.

[rouault]

* Use `ST_Intersects` in PostGIS - however this would likely lead to a slowdown of all requests e.g. WMS

ST_Intersects also uses the spatial index, so while there will be a slow-down, it should only be proportional to the number of features actually intersecting the BBOX

[jratike80]

Geoserver has a setting "Loose bbox" in PostGIS connection settings for controlling this behavior https://docs.geoserver.org/stable/en/user/data/database/postgis.html#postgis-loose-bbox. With the default setting BBOX filter is using the fast path. Clever enough WFS users can also make the selection by themselves: Intersects filter is always performing the accurate comparison while BBOX maybe not.

PostGIS used to be slow with ST_Intersects but nowadays it is really using the spatial index so perhaps it could use ST_Intersects directly. With some other data sources (GeoPackage, shapefile etc.) the accurate BBOX/Intersects can only be achieved by making the final filtering with Mapserver. That makes is hard to get the paging to work in a reliable way.

[sdlime]

If we knew that the data source was doing an accurate intersection test upstream then perhaps MapServer could skip that test in msQueryByRect().