[mapserver-users] Create a tileindex for WFS

Wed Apr 14 03:01:29 PDT 2021

Hi,

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.

-Jukka Rahkonen-

Lähettäjä: mapserver-users <mapserver-users-bounces at lists.osgeo.org> Puolesta mathias cunault via mapserver-users
Lähetetty: keskiviikko 14. huhtikuuta 2021 12.29
Vastaanottaja: Jeff McKenna <jmckenna at gatewaygeomatics.com>
Kopio: mapserver-users at lists.osgeo.org
Aihe: Re: [mapserver-users] Create a tileindex for WFS

Hi,
Reading yours preconisations here is what I did :

  *   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 with and without the LIMIT and OFFSET appearing at the end of the queries. The execution times are :

     *   about 1ms / 3s for layers with 22042 rows
     *   about 300ms / 7s for layers with 62584 rows.
     *   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,
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'))
limit 2 offset 0;

  *   I followed steps 1 to 5 suggested by Jukka Rahkonen : step 4 without MAXFEATURES did not return any result after several minutes (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)
If I have done the things correctly, I would say that the WFS can't get all the features.
Maybe because the layers features are very small polygons in large areas (?).
The WFS was the easiest solution for basic QGIS users ; giving direct access to Postgis layers through QGIS could be an alternative.
Unless you can suggest me something else ?
Thanks

Le lun. 12 avr. 2021 à 17:09, Jeff McKenna <jmckenna at gatewaygeomatics.com<mailto:jmckenna at gatewaygeomatics.com>> a écrit :
Since you are using MS4W here are some specific steps for your PostGIS
query/speed testing (I am adding this now to the documentation on
mapserver.org<http://mapserver.org> also) :

- add into your MAP-level of your mapfile:

   CONFIG "CPL_DEBUG" "ON"
   CONFIG "MS_ERRORFILE" "/ms4w/tmp/ms_error.txt"
   DEBUG 5

- add into your PostGIS LAYER of your mapfile:

   DEBUG 5

- now use a WFS client such as QGIS and add your WFS PostGIS layer

- open  "/ms4w/tmp/ms_error.txt" in Notepad++

- search for "msPostGISLayerWhichShapes query:"

- that line should list the long exact query sent from MapServer to the
PostreSQL instance, it may look like:

   [Mon Apr 12 11:27:34 2021].207000 msPostGISLayerWhichShapes query:
SELECT
"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')
as geom,"gid"::text FROM province WHERE "geom" &&
ST_GeomFromText('POLYGON((-5814679.36987815
-1504714.04276694,-5814679.36987815 4439806.52253364,5943763.33635122
4439806.52253364,5943763.33635122 -1504714.04276694,-5814679.36987815
-1504714.04276694))',3978) LIMIT 2 OFFSET 0

- now connect to that database through psql.exe

   psql -U postgres -p 5432 -d mydb

- using that error file line, grab everything from "SELECT", and inside
your database prompt, start the command with "EXPLAIN ANALYZE" and then
paste your full query, such as:

mydb=# EXPLAIN ANALYZE SELECT
"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')
as geom,"gid"::text FROM province WHERE "geom" &&
ST_GeomFromText('POLYGON((-5814679.36987815
-1504714.04276694,-5814679.36987815 4439806.52253364,5943763.33635122
4439806.52253364,5943763.33635122 -1504714.04276694,-5814679.36987815
-1504714.04276694))',3978) LIMIT 2 OFFSET 0;

- the response will tell you how long that query took, such as:

   Execution time: 0.293 ms

Hope that helps you a little more,

Thank-you for using MS4W.
"MS4W: open doors as well as windows"

-jeff

--
Jeff McKenna
GatewayGeo: Developers of MS4W, MapServer Consulting and Training
co-founder of FOSS4G
http://gatewaygeo.com/

On 2021-04-12 9:00 a.m., mathias cunault via mapserver-users wrote:
> Hello,
> In a PostgreSQL 13 database, I have 3 materialized views with 2578,
> 22013 and 62500 rows.
> I am using MS4W 4.0.3 to generate a WFS based on these 3 views.
> The aim is for users to display these WFS in QGIS.
> I have other lightweight views in PostgreSQL that can be easily
> displayed, but these 3 materialized views seem to be too heavy to
> display because some messages say that Downloading the layer entities
> has failed or partially failed.
> I wonder if using a tileindex was a good idea to speed up the display.
> Before going further in the developments, I would like your opinion
> about two things :
>
>   * I have to cut my views in "slices" before create a tileIndex for
>     each one.
>   * The tileindex can be stored in postgis database.
>
> Am I right?
> If not is there a better solution ?
> Thanks for your help
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
>

_______________________________________________
mapserver-users mailing list
mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/mapserver-users

--
----------
Mathias Cunault
référent SIG / Admin Caviar
Inrap Tours - 148 av. Maginot
37000 TOURS
06 32 05 98 96
mathias.cunault at inrap.fr<mailto:mathias.cunault at inrap.fr>
www.inrap.fr<http://www.inrap.fr>
abonnez-vous à la lettre d'information de l'Inrap : http://www.inrap.fr/newsletter.php
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20210414/09e343cf/attachment.htm>