Hi Benjamin,
just a note about indices: if you index e.g. "road class" which only has
5 classes the index may be counterproductive, because Postgres reads the
index and then uses itx to decide which datasets to read and has to read
most of them anyways. It might be faster to read all datasets and sort
out which suit the condition. Check with EXPLAIN on the server.
Bernhard
Am 24.05.2016 um 15:32 schrieb fuenfer-koenigstein.benja...@swm.de:
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
__________ Information from ESET Mail Security, version of virus signature
database 13537 (20160524) __________
The message was checked by ESET Mail Security.
http://www.eset.com
_______________________________________________
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
__________ Information from ESET Mail Security, version of virus signature
database 13537 (20160524) __________
The message was checked by ESET Mail Security.
http://www.eset.com
_______________________________________________
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
