Spatial filtering : use centroid and spatial index check

[Gustry]

• [x] When saving the CFG file, check for PG layer and if the index exists
• [x] the check can be done at different level, TBD
• [x] add a button to create it if possible

CC @mdouchin

[mdouchin]

SQL Filter would be like

ST_Intersects(FILTER_POLYGON, ST_Centroid(geom))

For performance, the plugin could indeed check that there is a proper index involving the geometry centroid in PostgreSQL:

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE TRUE
AND schemaname = 'SCHEMA_NAME' 
AND tablename = 'TABLE_NAME' 
AND indexdef ILIKE '%geom%' 
AND indexdef  ILIKE '%st_centroid%

which returns one line if there is an index, and no line otherwise.

   tablename   |            indexname            |                                                  indexdef
---------------+---------------------------------+------------------------------------------------------------------------------------------------------------
 parcelle_info | parcelle_info_centroid_geom_idx | CREATE INDEX parcelle_info_centroid_geom_idx ON "cadastre".parcelle_info USING gist (st_centroid(geom))
(1 row)

Index can be created WITH

CREATE INDEX ON "SCHEMA_NAME"."TABLE_NAME" USING GIST (ST_Centroid(geom));