[Qgis-user] Performance rendering PostGIS-Data

[Fuenfer-Koenigstein.Benjamin at swm.de]

Hi,
thanks for your quick answer! Sorry, I forgot to write that I have this option selected already.

Best wishes

Benjamin Fünfer-Königstein
S-IP-AN-TG Techn. Geschäftsfelder

Von: Stefan Ziegler [mailto:stefan.ziegler.de at gmail.com]
Gesendet: Dienstag, 24. Mai 2016 15:51
An: Fuenfer-Koenigstein.Benjamin S-IP-AN-TG
Cc: qgis-user
Betreff: Re: [Qgis-user] Performance rendering PostGIS-Data

Hi
In newer QGIS versions (not sure if it is in 2.14) there is an option in Settings > Data Sources > Execute expressions on server-side if possible.
Perhaps this helps.
regards
Stefan

On Tue, May 24, 2016 at 3:32 PM, <Fuenfer-Koenigstein.Benjamin at swm.de<mailto:Fuenfer-Koenigstein.Benjamin at swm.de>> wrote:
Hi all,

I have a problem gaining better performance during rendering PostGIS Data in QIS. I hope the following example helps understanding the issue:

I use road-data (line-geometries) in a PostGIS database (localhost), the geometry-table has about 400,000 rows.
Roads have 6 different classes I want to style with different line-symbols. Depending on scale I only want to show some of the major road classes (scale up to 1:50,000) or all the roads (scale higher 1:50,000). I use a rule-based styling with road type attributes and min/max-scale, in some cases I use subclasses for bridges, tunnels and normal lanes.

If I refresh the view in QGIS and check pg_log, I see that QGIS sends a SQL-statement with a bounding-box created from the current canvas:
SELECT … WHERE "the_geom" && st_makeenvelope(xmin,ymin,xmax,ymax)

As long as the bounding-box is small enough (scale is high enough), PostGIS uses a spatial index and the performance while rendering in QGIS is quite good.
From a certain scale on (smaller than 1:250,000), spatial index isn’t used anymore (seems that PostGIS decides that it’s needles as nearly the whole data-extend is within bounding-box). From that point on, rendering  is very slow, even though only the major-roads are shown in QGIS (about 200 geometries of the total 400,000).

That’s what I tried already with no success:

-       Trying different combinations of styling rules and min/max-scale

-       Rendering-options in QGIS, simplify geometry, simplify on provider side if possible

-       Generating indices on all the attributes I use for styling the road-data on the database

Only thing that helps is to duplicate the layer in QGIS for each scale-level (above/under 1:50,000) and setting provider feature filter to select only features that are actually shown on the scale-level under 50.000. Than the SQL-statement logged changes to:
SELECT … WHERE ("the_geom" && st_makeenvelope(xmin,ymin,xmax,ymax) AND (((road_class IN ('major1','major2',))))
Performance is much better but I’m not really glad about that solution as it makes the QGIS project unhandy and confusing for others using it because of the extra layers.

Is there a way making QGIS passing SQL-statements generated from current canvas extend AND styling rules? Or another way gaining better rendering performance?
If anyone had similar issues or has any idea, what I can do to make QGIS passing styling-rule based data selection to provider side, I’m glad to hear!

Best wishes

Benjamin Fünfer-Königstein
S-IP-AN-TG Techn. Geschäftsfelder


_______________________________________________
Qgis-user mailing list
Qgis-user at lists.osgeo.org<mailto:Qgis-user at lists.osgeo.org>
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20160524/f23894f2/attachment.html>