<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.E-MailFormatvorlage17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.E-MailFormatvorlage18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.nt1
{mso-style-name:nt1;
color:#062873;
font-weight:bold;}
span.na1
{mso-style-name:na1;
color:#4070A0;}
span.s3
{mso-style-name:s3;
color:#4070A0;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 2.0cm 70.85pt 2.0cm;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="DE" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:#1F497D">Hi all,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D">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:<o:p></o:p></span></p>
<p class="MsoNormal"><span class="nt1"><span lang="EN" style="font-size:11.5pt"><Parameter</span></span><span lang="EN" style="font-size:11.5pt">
<span class="na1">name=</span><span class="s3">"longmin"</span> <span class="na1">
type=</span><span class="s3">"xs:double"</span><span class="nt1">/></span></span><span lang="EN-US" style="color:#1F497D"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D">Is it possible to provide restrictions such as ranges or enumerations as documented there:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"><a href="http://www.w3schools.com/xml/schema_facets.asp">http://www.w3schools.com/xml/schema_facets.asp</a><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D">And am I right that this would also be an client-side problem to evaluate those restrictions?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D;mso-fareast-language:DE">Mit freundlichen Grüßen<br>
Im Auftrag<br>
<br>
<b>Benjamin Schepers<br>
<br>
</b><br>
Luftbild und Geoinformationssysteme<br>
Kronprinzenstraße 6<br>
45128 Essen<br>
Fon: +49 201 2069-232<br>
Fax: +49 201 2069-500<br>
schepers@rvr-online.de<br>
<br>
</span><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D;mso-fareast-language:DE"><img border="0" width="132" height="107" id="_x0000_i1025" src="cid:image001.jpg@01D25AB6.936B34B0"></span><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D;mso-fareast-language:DE"><br>
<br>
</span><b><span style="font-size:7.5pt;font-family:"Arial","sans-serif";color:#1F497D;mso-fareast-language:DE">Die Regionaldirektorin<br>
</span></b><span style="font-size:7.5pt;font-family:"Arial","sans-serif";color:#1F497D;mso-fareast-language:DE">Kronprinzenstraße 35<br>
45128 Essen<br>
Zentrale: +49 (0) 201 2069-0<br>
Fax: +49 (0) 201 2069-500<br>
www.metropoleruhr.de<br>
<br>
Postfach 10 32 64<br>
45032 Essen<br>
<br>
Steuernummer: RVR 112/5797/0116<br>
USt.-ldNr.: DE 173867500<br>
<br>
</span><span style="font-size:7.5pt;font-family:"Arial","sans-serif";color:silver;mso-fareast-language:DE">Diese E-Mail koennte vertrauliche und/oder rechtlich geschuetzte Informationen enthalten.<br>
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.<br>
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser E-Mail sind nicht gestattet.<br>
</span><span lang="EN-GB" style="font-size:7.5pt;font-family:"Arial","sans-serif";color:silver;mso-fareast-language:DE">This e-mail may contain confidential and/or privileged information.<br>
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.<br>
Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.</span><span lang="EN-US" style="color:#1F497D"><o:p></o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span style="mso-fareast-language:DE">Von:</span></b><span style="mso-fareast-language:DE"> mapserver-users [mailto:mapserver-users-bounces@lists.osgeo.org]
<b>Im Auftrag von </b>Rahkonen Jukka (MML)<br>
<b>Gesendet:</b> Dienstag, 20. Dezember 2016 11:37<br>
<b>An:</b> Mapserver-Users (mapserver-users@lists.osgeo.org)<br>
<b>Betreff:</b> [mapserver-users] Speeding up WFS paging with Spatialite and GeoPackage<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="FI">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="FI"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">SELECT * FROM table WHERE rowid>startindex AND rowid<=(startindex+count)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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”.
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Unfortunately by reading <a href="http://mapserver.org/ogc/wfs_server.html#stored-queries-wfs-2-0">
http://mapserver.org/ogc/wfs_server.html#stored-queries-wfs-2-0</a> it is not obvious for me how to write such “GetPageByRowid” query and I would appreciate getting some help.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">-Jukka Rahkonen-<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
</body>
</html>