<div dir="ltr"><div><div><div><div>Hi <br><br></div>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.<br><br></div>Perhaps this helps.<br><br></div>regards<br></div>Stefan <br></div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, May 24, 2016 at 3:32 PM, <span dir="ltr"><<a href="mailto:Fuenfer-Koenigstein.Benjamin@swm.de" target="_blank">Fuenfer-Koenigstein.Benjamin@swm.de</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div link="blue" vlink="purple" lang="DE">
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext">Hi all,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US">I have a problem gaining better performance during rendering PostGIS Data in QIS. I hope the following example helps understanding the issue:<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US">I use road-data (line-geometries) in a PostGIS database (localhost), the geometry-table has about 400,000 rows.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US">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.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US">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:
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US">SELECT … WHERE "the_geom" && st_makeenvelope(xmin,ymin,xmax,ymax)<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US">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.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US">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).
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US">That’s what I tried already with no success:<u></u><u></u></span></p>
<p><u></u><span style="font-size:11.0pt;color:windowtext" lang="EN-US"><span>-<span style="font:7.0pt "Times New Roman"">
</span></span></span><u></u><span style="font-size:11.0pt;color:windowtext" lang="EN-US">Trying different combinations of styling rules and min/max-scale<u></u><u></u></span></p>
<p><u></u><span style="font-size:11.0pt;color:windowtext" lang="EN-US"><span>-<span style="font:7.0pt "Times New Roman"">
</span></span></span><u></u><span style="font-size:11.0pt;color:windowtext" lang="EN-US">Rendering-options in QGIS, simplify geometry, simplify on provider side if possible<u></u><u></u></span></p>
<p><u></u><span style="font-size:11.0pt;color:windowtext" lang="EN-US"><span>-<span style="font:7.0pt "Times New Roman"">
</span></span></span><u></u><span style="font-size:11.0pt;color:windowtext" lang="EN-US">Generating indices on all the attributes I use for styling the road-data on the database<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US">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:
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US">SELECT … WHERE ("the_geom" && st_makeenvelope(xmin,ymin,xmax,ymax) AND (((road_class IN ('major1','major2',))))<br>
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.
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US">Is there a way making QGIS passing SQL-statements generated from current canvas extend AND styling rules? Or another way gaining better rendering performance?<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US">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!<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext" lang="EN-US">Best wishes<u></u><u></u></span></p>
<p class="MsoNormal"><span style="color:windowtext" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="color:windowtext">Benjamin Fünfer-Königstein<u></u><u></u></span></p>
<p class="MsoNormal"><span style="color:windowtext">S-IP-AN-TG Techn. Geschäftsfelder</span><span style="color:windowtext"><u></u><u></u></span></p>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
</div>
<br>_______________________________________________<br>
Qgis-user mailing list<br>
<a href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a><br>
List info: <a href="http://lists.osgeo.org/mailman/listinfo/qgis-user" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><br>
Unsubscribe: <a href="http://lists.osgeo.org/mailman/listinfo/qgis-user" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><br></blockquote></div><br></div>