[Qgis-user] Handling of PostGIS TopoGeometry layers

[Sandro Santilli]

On Mon, Jul 25, 2016 at 07:21:16AM -0400, AW wrote:

> I was examining the SQL-statements which QGIS uses to deal with TopoGeometry layers. It essentialy looks somewhat like this:
> 
> ---
> SELECT st_asbinary("topo",'NDR'),"gid"
> FROM "public"."topo_test"
> WHERE "topo" && st_makeenvelope(138.48618410228871767,-35.03610634074148322,138.71434043049001161,-34.78702094367417885,4326)
> ---
> 
> Those kind of statements run really slow because the column "topo" of type TopoGeometry does not have any spatial index.
> In my case the table has a "normal" Geometry column AND a TopoGeometry column, which are pretty much in sync.

[...]

> So I am asking for your opinion if a possibly small addition to a TopoGeometry layer properties in form of e.g. a checkbox is thinkable/reasonable which enables the alteration of the statement to something like that (given the hint, that both geometry columns have to be more or less in sync):
> 
> ---
> SELECT st_asbinary("topo",'NDR'),"gid"
> FROM "public"."topo_test"
> WHERE "geom" && st_makeenvelope(138.48618410228871767,-35.03610634074148322,138.71434043049001161,-34.78702094367417885,4326)
> ---
> 
> Would that be a way to tackle this well known performance issue?

My feeling is that it would be very useful to be able to specify,
for the PostgreSQL provider, a custom dynamic filter, where the
pixel size and extent are available as substitutable queries.

I think there's also an hub ticket for this enhancement, waiting
for a contributed patch.

That, or being able to specify different columns for *rendering*
and *filtering*.

Note that another use case is the "face" layer of the TopologyViewer,
which would benefit from filtering on the "mbr" column rather than
on the result of the expensive ST_GetFaceGeometry call.

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   https://strk.kbt.io/services.html