<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=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@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;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
span.Shkpostityyli18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 2.0cm 70.85pt 2.0cm;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:1842041781;
mso-list-template-ids:712941716;}
@list l0:level1
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:36.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:72.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;
mso-ansi-font-size:10.0pt;
font-family:"Courier New";
mso-bidi-font-family:"Times New Roman";}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:108.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:144.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:180.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:216.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:252.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:288.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:324.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
--></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="FI" link="blue" vlink="purple" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">The results show that there is something wrong in your system but it is hard to say what. If Mapserver gets the 62584 features from the biggest table in 7 seconds it should not really
need many seconds more to write everything out as GML. Unfortunately I can’t say what to debug next.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">-Jukka Rahkonen-<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b>Lähettäjä:</b> mapserver-users <mapserver-users-bounces@lists.osgeo.org>
<b>Puolesta </b>mathias cunault via mapserver-users<br>
<b>Lähetetty:</b> keskiviikko 14. huhtikuuta 2021 12.29<br>
<b>Vastaanottaja:</b> Jeff McKenna <jmckenna@gatewaygeomatics.com><br>
<b>Kopio:</b> mapserver-users@lists.osgeo.org<br>
<b>Aihe:</b> Re: [mapserver-users] Create a tileindex for WFS<o:p></o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<div>
<div>
<p class="MsoNormal"><span style="font-size:18.0pt;font-family:"Verdana",sans-serif">Hi,<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:18.0pt;font-family:"Verdana",sans-serif">Reading yours preconisations here is what I did :<o:p></o:p></span></p>
</div>
<div>
<ul type="disc">
<li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo1">
<span style="font-size:18.0pt;font-family:"Verdana",sans-serif">server side : I ran EXPLAIN ANALYZE with the two queries I get in the mapserver log file as suggested. These queries concerned the two layers partially loaded in QGIS. I ran the queries
<b>with and without </b>the LIMIT and OFFSET appearing at the end of the queries. The execution times are :<o:p></o:p></span></li></ul>
<ul type="disc">
<ul type="circle">
<li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level2 lfo1">
<span style="font-size:18.0pt;font-family:"Verdana",sans-serif">about 1ms / 3s for layers with 22042 rows<o:p></o:p></span></li><li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level2 lfo1">
<span style="font-size:18.0pt;font-family:"Verdana",sans-serif">about 300ms / 7s for layers with 62584 rows.<o:p></o:p></span></li><li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level2 lfo1">
<span style="font-size:18.0pt;font-family:"Verdana",sans-serif">example : EXPLAIN ANALYZE select "gid"::text,"numouvert"::text,"typouvert"::text,"gidoperef"::text,"contributor"::text,"creator"::text,"publisher"::text,"description_ouv"::text,"resolution_ouv"::text,"dates"::text,"source"::text,"identifier"::text,"rights"::text,"title"::text,"type_donnees"::text,"format"::text,"language"::text,"coverage"::text,ST_AsBinary(("geom"),'NDR')
as geom,"gid"::text from (select gid, geom, numouvert, typouvert, gidoperef, contributor, creator,publisher,
<br>
description_ouv, resolution_ouv, dates, source, identifier, rights, title, type_donnees, format, language, coverage FROM cd45.ouverture) as foo where "geom" && ST_GeomFromText('POLYGON((529963.906053403 6606654.86508109,529963.906053403 6880827.26326984,760155.299537431
6880827.26326984,760155.299537431 6606654.86508109,529963.906053403 6606654.86508109))',find_srid('','cd45.ouverture','geom'))<br>
limit 2 offset 0; <o:p></o:p></span></li></ul>
</ul>
<ul type="disc">
<li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo1">
<span style="font-size:18.0pt;font-family:"Verdana",sans-serif">I followed steps 1 to 5 suggested by
</span><span lang="EN-US" style="font-size:18.0pt;font-family:"Verdana",sans-serif">Jukka Rahkonen</span><span style="font-size:18.0pt;font-family:"Verdana",sans-serif"> : step 4 without MAXFEATURES did not return any result after several minutes (<a href="http://blabla.fr/cgi-bin/mapserv.exe?MAP=C:/ms4w/Apache/site/mapfile/fond_cd45.map&SERVICE=WFS&REQUEST=getfeature&VERSION=2.0.0&TYPENAME=vestige" target="_blank">http://blabla.fr/cgi-bin/mapserv.exe?MAP=C:/ms4w/Apache/site/mapfile/fond_cd45.map&SERVICE=WFS&REQUEST=getfeature&VERSION=2.0.0&TYPENAME=vestige</a>)<o:p></o:p></span></li></ul>
<div>
<p class="MsoNormal"><span style="font-size:18.0pt;font-family:"Verdana",sans-serif">If I have done the things correctly, I would say that the WFS can't get all the features.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:18.0pt;font-family:"Verdana",sans-serif">Maybe because the layers features are very small polygons in large areas (?).<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:18.0pt;font-family:"Verdana",sans-serif">The WFS was the easiest solution for basic QGIS users ; giving direct access to Postgis layers through QGIS could be an alternative.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:18.0pt;font-family:"Verdana",sans-serif">Unless you can suggest me something else ?<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:18.0pt;font-family:"Verdana",sans-serif">Thanks<o:p></o:p></span></p>
</div>
</div>
<div>
<p class="MsoNormal"><span style="font-size:18.0pt;font-family:"Verdana",sans-serif"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:18.0pt;font-family:"Verdana",sans-serif"><o:p> </o:p></span></p>
</div>
</div>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">Le lun. 12 avr. 2021 à 17:09, Jeff McKenna <<a href="mailto:jmckenna@gatewaygeomatics.com">jmckenna@gatewaygeomatics.com</a>> a écrit :<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm">
<p class="MsoNormal">Since you are using MS4W here are some specific steps for your PostGIS
<br>
query/speed testing (I am adding this now to the documentation on <br>
<a href="http://mapserver.org" target="_blank">mapserver.org</a> also) :<br>
<br>
- add into your MAP-level of your mapfile:<br>
<br>
CONFIG "CPL_DEBUG" "ON"<br>
CONFIG "MS_ERRORFILE" "/ms4w/tmp/ms_error.txt"<br>
DEBUG 5<br>
<br>
- add into your PostGIS LAYER of your mapfile:<br>
<br>
DEBUG 5<br>
<br>
- now use a WFS client such as QGIS and add your WFS PostGIS layer<br>
<br>
- open "/ms4w/tmp/ms_error.txt" in Notepad++<br>
<br>
- search for "msPostGISLayerWhichShapes query:"<br>
<br>
- that line should list the long exact query sent from MapServer to the <br>
PostreSQL instance, it may look like:<br>
<br>
[Mon Apr 12 11:27:34 2021].207000 msPostGISLayerWhichShapes query: <br>
SELECT <br>
"gid"::text,"area"::text,"perimeter"::text,"province_"::text,"province_i"::text,"status"::text,"name"::text,"name_e"::text,"name_f"::text,"reg_code"::text,"poly_featu"::text,"island"::text,"island_e"::text,"island_f"::text,ST_AsBinary(("geom"),'NDR')
<br>
as geom,"gid"::text FROM province WHERE "geom" && <br>
ST_GeomFromText('POLYGON((-5814679.36987815 <br>
-1504714.04276694,-5814679.36987815 4439806.52253364,5943763.33635122 <br>
4439806.52253364,5943763.33635122 -1504714.04276694,-5814679.36987815 <br>
-1504714.04276694))',3978) LIMIT 2 OFFSET 0<br>
<br>
- now connect to that database through psql.exe<br>
<br>
psql -U postgres -p 5432 -d mydb<br>
<br>
- using that error file line, grab everything from "SELECT", and inside <br>
your database prompt, start the command with "EXPLAIN ANALYZE" and then <br>
paste your full query, such as:<br>
<br>
mydb=# EXPLAIN ANALYZE SELECT <br>
"gid"::text,"area"::text,"perimeter"::text,"province_"::text,"province_i"::text,"status"::text,"name"::text,"name_e"::text,"name_f"::text,"reg_code"::text,"poly_featu"::text,"island"::text,"island_e"::text,"island_f"::text,ST_AsBinary(("geom"),'NDR')
<br>
as geom,"gid"::text FROM province WHERE "geom" && <br>
ST_GeomFromText('POLYGON((-5814679.36987815 <br>
-1504714.04276694,-5814679.36987815 4439806.52253364,5943763.33635122 <br>
4439806.52253364,5943763.33635122 -1504714.04276694,-5814679.36987815 <br>
-1504714.04276694))',3978) LIMIT 2 OFFSET 0;<br>
<br>
- the response will tell you how long that query took, such as:<br>
<br>
Execution time: 0.293 ms<br>
<br>
Hope that helps you a little more,<br>
<br>
<br>
Thank-you for using MS4W.<br>
"MS4W: open doors as well as windows"<br>
<br>
-jeff<br>
<br>
<br>
<br>
<br>
-- <br>
Jeff McKenna<br>
GatewayGeo: Developers of MS4W, MapServer Consulting and Training<br>
co-founder of FOSS4G<br>
<a href="http://gatewaygeo.com/" target="_blank">http://gatewaygeo.com/</a><br>
<br>
<br>
<br>
<br>
<br>
On 2021-04-12 9:00 a.m., mathias cunault via mapserver-users wrote:<br>
> Hello,<br>
> In a PostgreSQL 13 database, I have 3 materialized views with 2578, <br>
> 22013 and 62500 rows.<br>
> I am using MS4W 4.0.3 to generate a WFS based on these 3 views.<br>
> The aim is for users to display these WFS in QGIS.<br>
> I have other lightweight views in PostgreSQL that can be easily <br>
> displayed, but these 3 materialized views seem to be too heavy to <br>
> display because some messages say that Downloading the layer entities <br>
> has failed or partially failed.<br>
> I wonder if using a tileindex was a good idea to speed up the display. <br>
> Before going further in the developments, I would like your opinion <br>
> about two things :<br>
> <br>
> * I have to cut my views in "slices" before create a tileIndex for<br>
> each one.<br>
> * The tileindex can be stored in postgis database.<br>
> <br>
> Am I right?<br>
> If not is there a better solution ?<br>
> Thanks for your help<br>
> <br>
> _______________________________________________<br>
> mapserver-users mailing list<br>
> <a href="mailto:mapserver-users@lists.osgeo.org" target="_blank">mapserver-users@lists.osgeo.org</a><br>
> <a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users" target="_blank">
https://lists.osgeo.org/mailman/listinfo/mapserver-users</a><br>
> <br>
<br>
_______________________________________________<br>
mapserver-users mailing list<br>
<a href="mailto:mapserver-users@lists.osgeo.org" target="_blank">mapserver-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a><o:p></o:p></p>
</blockquote>
</div>
<p class="MsoNormal"><br clear="all">
<br>
-- <o:p></o:p></p>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<p class="MsoNormal"><i><span style="font-size:12.0pt">----------</span></i><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><i><span style="font-size:12.0pt">Mathias Cunault</span></i><o:p></o:p></p>
</div>
</div>
</div>
<div>
<p class="MsoNormal"><i><span style="font-size:10.0pt">référent SIG / Admin Caviar<br>
Inrap Tours - 148 av. Maginot<br>
37000 TOURS<br>
06 32 05 98 96<br>
<u><a href="mailto:mathias.cunault@inrap.fr" target="_blank">mathias.cunault@inrap.fr</a></u></span></i><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><a href="http://www.inrap.fr" target="_blank">www.inrap.fr</a><br>
<span style="font-size:7.5pt">abonnez-vous à la lettre d'information de l'Inrap :
<a href="http://www.inrap.fr/newsletter.php" target="_blank">http://www.inrap.fr/newsletter.php</a></span><o:p></o:p></p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>