[mapserver-users] Create a tileindex for WFS

mathias cunault mathias.cunault at inrap.fr
Wed Apr 14 02:29:11 PDT 2021 

 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>
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 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
> > https://lists.osgeo.org/mailman/listinfo/mapserver-users
> >
>
> _______________________________________________
> mapserver-users mailing list
> 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. Maginot37000 TOURS06 32 05 98
96mathias.cunault at inrap.fr <mathias.cunault at inrap.fr>*
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/b5d88f5a/attachment-0001.html>

More information about the mapserver-users mailing list