[mapserver-users] Speeding up WFS paging with Spatialite and GeoPackage

[Schepers, Benjamin]

Hi all,

to extend Jukkas Question I'd like to use predefined queries (WFS 2.0) to break large datasets into smaller pieces. A common usecase could be to deliver predefined queries, where users could select a value from a list (simple example:name of city where data belongs to...) which will be used for filtering. In the docs I didn't find a solution to provide value-lists, just variablename and datatype, for example:
<Parameter name="longmin" type="xs:double"/>

Is it possible to provide restrictions such as ranges or enumerations as documented there:
http://www.w3schools.com/xml/schema_facets.asp

And am I right that this would also be an client-side problem to evaluate those restrictions?


Mit freundlichen Grüßen
Im Auftrag

Benjamin Schepers


Luftbild und Geoinformationssysteme
Kronprinzenstraße 6
45128 Essen
Fon: +49 201 2069-232
Fax: +49 201 2069-500
schepers at rvr-online.de

[cid:image001.jpg at 01D25AB6.936B34B0]

Die Regionaldirektorin
Kronprinzenstraße 35
45128 Essen
Zentrale: +49 (0) 201 2069-0
Fax: +49 (0) 201 2069-500
www.metropoleruhr.de

Postfach 10 32 64
45032 Essen

Steuernummer: RVR 112/5797/0116
USt.-ldNr.: DE 173867500

Diese E-Mail koennte vertrauliche und/oder rechtlich geschuetzte Informationen enthalten.
Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtuemlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser E-Mail sind nicht gestattet.
This e-mail may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail.
Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
Von: mapserver-users [mailto:mapserver-users-bounces at lists.osgeo.org] Im Auftrag von Rahkonen Jukka (MML)
Gesendet: Dienstag, 20. Dezember 2016 11:37
An: Mapserver-Users (mapserver-users at lists.osgeo.org)
Betreff: [mapserver-users] Speeding up WFS paging with Spatialite and GeoPackage

Hi,

It seems that because of how SQLite works it is not possible to make fast paging for big tables by using simple SQL with LIMIT and OFFSET.  Making a query as "LIMIT 100 OFFSET 1000000" makes SQLite to read 1000100 rows and throw away the first million ones. The bigger the offset the slower the query.

I guess that Mapserver must still use LIMIT and OFFSET because they work even with combined attribute and spatial filters. But I wonder if it could be possible to make a WFS 2.0 stored query for a not so uncommon use case of loading pages without other filters from very big tables. This stored query would also take count and startindex as inputs but place them into a query like

SELECT * FROM table WHERE rowid>startindex AND rowid<=(startindex+count)

An optional parameter could be used for defining some other unique, numeric and indexed attribute for paging. This query should work very fine with a SQLite table that is freshly  created for example with ogr2ogr because then rowids start from one and they do not have gaps. If table has been edited there may be missing rowids but that is not critical because query would just return sometimes less rows than "count".

Unfortunately by reading http://mapserver.org/ogc/wfs_server.html#stored-queries-wfs-2-0 it is not obvious for me how to write such "GetPageByRowid" query and I would appreciate getting some help.

-Jukka Rahkonen-





-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20161220/84a9092a/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 8303 bytes
Desc: image001.jpg
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20161220/84a9092a/attachment-0001.jpg>