[Qgis-user] Performance rendering PostGIS-Data

[Stefan Ziegler]

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>
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
> 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/5ae56199/attachment.html>