Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Allow wfs_use_default_extent_for_getfeature for MSSQL Driver #5994

Closed
geographika opened this issue Feb 20, 2020 · 2 comments
Closed

Allow wfs_use_default_extent_for_getfeature for MSSQL Driver #5994

geographika opened this issue Feb 20, 2020 · 2 comments

Comments

@geographika
Copy link
Member

geographika commented Feb 20, 2020

I've been looking to optimise WFS 2.0 MapServer queries using the MSSQL driver and one change would greatly improve query speeds would be to disable the default spatial query. This probably applies to other database drivers such as Postgres and Oracle.

The "wfs_use_default_extent_for_getfeature" was implemented with 45037eb for OGR data sources as documented at https://mapserver.org/el/ogc/wfs_server.html#layer-object

If no spatial filter is passed to a WFS GetFeature the bbox of the map is added to the WHERE clause in SQL Server:

GEOM.STIntersects(geometry::STGeomFromText('POLYGON((-826053.00001 7143520.99999,-719272.99999 7143520.99999,-719272.99999 7257149.00001,-826053.00001 7257149.00001,-826053.00001 7143520.99999))',3857)) = 1 )

This can greatly slow down the query, and as the bbox is set to the extent of all data in the MAP serves no purpose (in this case).

In addition, if "wfs_compute_number_matched" is set to "true" to get the feature count the default spatial bbox is added to get the count, which has a further impact on performance. Queries such as the following are created:

SELECT count(*) FROM (select * from cities) as foo WHERE ogr_geometry.MakeValid().STIntersects(geometry::STGeomFromText('POLYGON((-826053.00001 7143520.99999,-719272.99999 7143520.99999,-719272.99999 7257149.00001,-826053.00001 7257149.00001,-826053.00001 7143520.99999))',3857)) = 1 )

This SQL is generated in mapmssql2008.c in the msMSSQL2008LayerGetShapeCount function.

Again the spatial filter will not filter out any records, but greatly increases the running time of a query.

@jratike80
Copy link

jratike80 commented Feb 21, 2020

I think that "wfs_use_default_extent_for_getfeature" could well affect all data sources and not only OGR sources. In addition it could be "false" by default . Or can anybody say when it makes something good with WFS? If WFS user does not include BBOX or any other spatial filter then they want to get data from the whole table so adding a BBOX filter that selects everything feels stupid.

It is possible that uses defines a layer that gets data from just a subset of a table, restricted by LAYER - EXTENT. In that case adding the default extent could make sense because it prevents user from getting data outside the area that is published. But even then it does not work right if user sends a spatial filter that is outside the extent. The default filter is removed and user may get data from outside of the layer area, if I have understood right.

I believe that PostGIS and Oracle does not suffer as much as MSSQL seems to do.

@geographika
Copy link
Member Author

geographika commented Feb 21, 2020

I've just tested with Postgres/PosGIS and can confirm it works in the same way as the MSSQL driver.

An intersection is tested for (using &&) with the extent of the layer:

.. where "the_geom" && ST_GeomFromText('POLYGON((1761280.77823537..

If "wfs_compute_number_matched" is set to "true" then the following is added to the COUNT(*) SQL:

..AND NOT ST_Disjoint(the_geom,ST_GeomFromText(..

In addition if the layer EXTENT is not set then SELECT ST_Extent(the_geom) is used run twice to calculate the extent of the data.

@jratike80 I agree I can't think of a good use case for adding the spatial filter beyond your download example, and the additional spatial filters can be very costly in terms of performance if simply looking to retrieve data based on an attribute filter.

rouault added a commit that referenced this issue Feb 26, 2020
mapmssql2008.c: honour "wfs_use_default_extent_for_getfeature" "false" LAYER.METADATA (fixes #5994)
geographika pushed a commit to geographika/mapserver that referenced this issue Feb 27, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants