[QGIS-Developer] Speedup of PostGIS Topology layers loading

[Sandro Santilli]

Hi all, I'd like to bring to your attention a pull request
I created that allows making effective use of spatial indexes
when showing PostGIS Topology layers in a QGIS map:

  https://github.com/qgis/QGIS/pull/50906

There's no way to make use of such indexes without changing
the SQL query used to retrieve the data because PostGIS Topology
based spatial objects (TopoGeometry) are just referces to other
tables and dereferencing those connections done on a row-by-row
basis would always be slower than building a proper query informed
of the data organization.

As I know there aren't many QGIS committers using PostGIS Topology
consider this as an invitation to take a look at it :)

"Quick" way to build a TopoGeometry layer in PostGIS:

  CREATE EXTENSION postgis_topology CASCADE;

  CREATE TABLE x(id serial primary key);

  SELECT topology.CreateTopology('test', '4326');
  SELECT AddTopoGeometryColumn('test','public','x','tg','POLYGON');

  INSERT INTO x(tg)
    SELECT toTopoGeom(
      ST_SetSRID(
        ST_Buffer(ST_MakePoint(x,y), 2),
        4326
      ),
      'test',
      1
    )
    FROM
      generate_series(-170, 170, 3) x,
      generate_series(-90, 90, 3) y;

Then use the QGIS browser to load the table public.x
and compare the performance with and w/out the patch,
if you want.


--strk; 

  Libre GIS consultant/developer
  https://strk.kbt.io/services.html