<div dir="ltr"><div class="gmail_default" style="font-family:verdana,sans-serif;font-size:large">
<div dir="ltr"><div class="gmail_default" style="font-family:verdana,sans-serif;font-size:large">Hi,</div><div class="gmail_default" style="font-family:verdana,sans-serif;font-size:large">Reading yours preconisations here is what I did :</div><div class="gmail_default" style="font-family:verdana,sans-serif;font-size:large"><ul><li>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 <b>without</b>
</b> the LIMIT and OFFSET appearing at the end of the queries. The execution times are :</li><ul><li>about 1ms / 3s for layers with 22042 rows</li><li>about 300ms / 7s for layers with 62584 rows.</li><li>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; </li></ul><li>I followed steps 1 to 5 suggested by
<span lang="EN-US">Jukka Rahkonen</span> : 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>)</li></ul><div>If I have done the things correctly, I would say that the WFS can't get all the features.</div><div>Maybe because the layers features are very small polygons in large areas (?).</div><div>The
WFS was the easiest solution for basic QGIS users ; giving direct
access to Postgis layers through QGIS could be an alternative.</div><div>Unless you can suggest me something else ?</div><div>Thanks<br></div></div><div class="gmail_default" style="font-family:verdana,sans-serif;font-size:large"><br></div><div class="gmail_default" style="font-family:verdana,sans-serif;font-size:large"><br></div></div>
</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Le lun. 12 avr. 2021 à 17:09, Jeff McKenna <<a href="mailto:jmckenna@gatewaygeomatics.com">jmckenna@gatewaygeomatics.com</a>> a écrit :<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">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" rel="noreferrer" 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/" rel="noreferrer" 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" rel="noreferrer" 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" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a><br>
</blockquote></div><br clear="all"><br>-- <br><div dir="ltr" class="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div><div><i style="font-size:small">----------</i></div><div><i style="font-size:small">Mathias Cunault</i><br></div></div></div><div><font size="2"><i>référent SIG / Admin Caviar<br></i></font><font size="2"><i><font size="2"><i>Inrap Tours - </i></font>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></i></font></div><div><a href="http://www.inrap.fr" target="_blank">www.inrap.fr</a><br><font size="1">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></font><br></div></div></div></div></div></div></div></div></div></div></div>