<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
h1
{mso-style-priority:9;
mso-style-link:"Überschrift 1 Zchn";
margin-top:24.0pt;
margin-right:0cm;
margin-bottom:0cm;
margin-left:0cm;
margin-bottom:.0001pt;
page-break-after:avoid;
font-size:14.0pt;
font-family:"Times New Roman","serif";
font-weight:bold;}
h2
{mso-style-priority:9;
mso-style-link:"Überschrift 2 Zchn";
margin-top:10.0pt;
margin-right:0cm;
margin-bottom:0cm;
margin-left:0cm;
margin-bottom:.0001pt;
page-break-after:avoid;
font-size:13.0pt;
font-family:"Times New Roman","serif";
font-weight:bold;}
h3
{mso-style-priority:9;
mso-style-link:"Überschrift 3 Zchn";
margin-top:10.0pt;
margin-right:0cm;
margin-bottom:0cm;
margin-left:0cm;
margin-bottom:.0001pt;
page-break-after:avoid;
font-size:12.0pt;
font-family:"Times New Roman","serif";
font-weight:bold;}
h4
{mso-style-priority:9;
mso-style-link:"Überschrift 4 Zchn";
margin-top:10.0pt;
margin-right:0cm;
margin-bottom:0cm;
margin-left:0cm;
margin-bottom:.0001pt;
page-break-after:avoid;
font-size:12.0pt;
font-family:"Times New Roman","serif";
font-weight:bold;
font-style:italic;}
h5
{mso-style-priority:9;
mso-style-link:"Überschrift 5 Zchn";
margin-top:10.0pt;
margin-right:0cm;
margin-bottom:0cm;
margin-left:0cm;
margin-bottom:.0001pt;
page-break-after:avoid;
font-size:12.0pt;
font-family:"Times New Roman","serif";
font-weight:normal;}
h6
{mso-style-priority:9;
mso-style-link:"Überschrift 6 Zchn";
margin-top:10.0pt;
margin-right:0cm;
margin-bottom:0cm;
margin-left:0cm;
margin-bottom:.0001pt;
page-break-after:avoid;
font-size:12.0pt;
font-family:"Times New Roman","serif";
font-weight:normal;
font-style:italic;}
p.MsoHeading7, li.MsoHeading7, div.MsoHeading7
{mso-style-priority:9;
mso-style-link:"Überschrift 7 Zchn";
margin-top:10.0pt;
margin-right:0cm;
margin-bottom:0cm;
margin-left:0cm;
margin-bottom:.0001pt;
page-break-after:avoid;
font-size:12.0pt;
font-family:"Times New Roman","serif";
font-style:italic;}
p.MsoHeading8, li.MsoHeading8, div.MsoHeading8
{mso-style-priority:9;
mso-style-link:"Überschrift 8 Zchn";
margin-top:10.0pt;
margin-right:0cm;
margin-bottom:0cm;
margin-left:0cm;
margin-bottom:.0001pt;
page-break-after:avoid;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
p.MsoHeading9, li.MsoHeading9, div.MsoHeading9
{mso-style-priority:9;
mso-style-link:"Überschrift 9 Zchn";
margin-top:10.0pt;
margin-right:0cm;
margin-bottom:0cm;
margin-left:0cm;
margin-bottom:.0001pt;
page-break-after:avoid;
font-size:12.0pt;
font-family:"Times New Roman","serif";
font-style:italic;}
p.MsoTitle, li.MsoTitle, div.MsoTitle
{mso-style-priority:10;
mso-style-link:"Titel Zchn";
margin-top:0cm;
margin-right:0cm;
margin-bottom:15.0pt;
margin-left:0cm;
mso-add-space:auto;
border:none;
padding:0cm;
font-size:26.0pt;
font-family:"Times New Roman","serif";
letter-spacing:.25pt;}
p.MsoTitleCxSpFirst, li.MsoTitleCxSpFirst, div.MsoTitleCxSpFirst
{mso-style-priority:10;
mso-style-link:"Titel Zchn";
mso-style-type:export-only;
margin:0cm;
margin-bottom:.0001pt;
mso-add-space:auto;
border:none;
padding:0cm;
font-size:26.0pt;
font-family:"Times New Roman","serif";
letter-spacing:.25pt;}
p.MsoTitleCxSpMiddle, li.MsoTitleCxSpMiddle, div.MsoTitleCxSpMiddle
{mso-style-priority:10;
mso-style-link:"Titel Zchn";
mso-style-type:export-only;
margin:0cm;
margin-bottom:.0001pt;
mso-add-space:auto;
border:none;
padding:0cm;
font-size:26.0pt;
font-family:"Times New Roman","serif";
letter-spacing:.25pt;}
p.MsoTitleCxSpLast, li.MsoTitleCxSpLast, div.MsoTitleCxSpLast
{mso-style-priority:10;
mso-style-link:"Titel Zchn";
mso-style-type:export-only;
margin-top:0cm;
margin-right:0cm;
margin-bottom:15.0pt;
margin-left:0cm;
mso-add-space:auto;
border:none;
padding:0cm;
font-size:26.0pt;
font-family:"Times New Roman","serif";
letter-spacing:.25pt;}
p.MsoSubtitle, li.MsoSubtitle, div.MsoSubtitle
{mso-style-priority:11;
mso-style-link:"Untertitel Zchn";
margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";
letter-spacing:.75pt;
font-style:italic;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
p.MsoNoSpacing, li.MsoNoSpacing, div.MsoNoSpacing
{mso-style-priority:1;
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Arial","sans-serif";
color:black;
mso-fareast-language:EN-US;}
p.MsoIntenseQuote, li.MsoIntenseQuote, div.MsoIntenseQuote
{mso-style-priority:30;
mso-style-link:"Intensives Zitat Zchn";
margin-top:10.0pt;
margin-right:46.8pt;
margin-bottom:14.0pt;
margin-left:46.8pt;
border:none;
padding:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";
font-weight:bold;
font-style:italic;}
span.MsoIntenseEmphasis
{mso-style-priority:21;
color:black;
font-weight:bold;
font-style:italic;}
span.berschrift1Zchn
{mso-style-name:"Überschrift 1 Zchn";
mso-style-priority:9;
mso-style-link:"Überschrift 1";
font-family:"Arial","sans-serif";
color:black;
font-weight:bold;}
span.berschrift2Zchn
{mso-style-name:"Überschrift 2 Zchn";
mso-style-priority:9;
mso-style-link:"Überschrift 2";
font-family:"Arial","sans-serif";
color:black;
font-weight:bold;}
span.TitelZchn
{mso-style-name:"Titel Zchn";
mso-style-priority:10;
mso-style-link:Titel;
font-family:"Arial","sans-serif";
color:black;
letter-spacing:.25pt;}
span.UntertitelZchn
{mso-style-name:"Untertitel Zchn";
mso-style-priority:11;
mso-style-link:Untertitel;
font-family:"Arial","sans-serif";
color:black;
letter-spacing:.75pt;
font-style:italic;}
span.IntensivesZitatZchn
{mso-style-name:"Intensives Zitat Zchn";
mso-style-priority:30;
mso-style-link:"Intensives Zitat";
color:black;
font-weight:bold;
font-style:italic;}
span.berschrift3Zchn
{mso-style-name:"Überschrift 3 Zchn";
mso-style-priority:9;
mso-style-link:"Überschrift 3";
font-family:"Arial","sans-serif";
color:black;
font-weight:bold;}
span.berschrift4Zchn
{mso-style-name:"Überschrift 4 Zchn";
mso-style-priority:9;
mso-style-link:"Überschrift 4";
font-family:"Arial","sans-serif";
color:black;
font-weight:bold;
font-style:italic;}
span.berschrift5Zchn
{mso-style-name:"Überschrift 5 Zchn";
mso-style-priority:9;
mso-style-link:"Überschrift 5";
font-family:"Arial","sans-serif";
color:black;}
span.berschrift6Zchn
{mso-style-name:"Überschrift 6 Zchn";
mso-style-priority:9;
mso-style-link:"Überschrift 6";
font-family:"Arial","sans-serif";
color:black;
font-style:italic;}
span.berschrift7Zchn
{mso-style-name:"Überschrift 7 Zchn";
mso-style-priority:9;
mso-style-link:"Überschrift 7";
font-family:"Arial","sans-serif";
color:black;
font-style:italic;}
span.berschrift8Zchn
{mso-style-name:"Überschrift 8 Zchn";
mso-style-priority:9;
mso-style-link:"Überschrift 8";
font-family:"Arial","sans-serif";
color:black;}
span.berschrift9Zchn
{mso-style-name:"Überschrift 9 Zchn";
mso-style-priority:9;
mso-style-link:"Überschrift 9";
font-family:"Arial","sans-serif";
color:black;
font-style:italic;}
span.E-MailFormatvorlage35
{mso-style-type:personal-reply;
font-family:"Arial","sans-serif";
color:#003285;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;
font-family:"Arial","sans-serif";
color:black;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="DE" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Arial","sans-serif";color:#003285">Hi,
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Arial","sans-serif";color:#003285">thanks for your quick answer! Sorry, I forgot to write that I have this option selected already.
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#003285"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#003285">Best wishes<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#003285"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#003285">Benjamin Fünfer-Königstein<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#003285">S-IP-AN-TG Techn. Geschäftsfelder</span><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#003285"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Arial","sans-serif";color:#003285"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">Von:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Stefan Ziegler [mailto:stefan.ziegler.de@gmail.com]
<br>
<b>Gesendet:</b> Dienstag, 24. Mai 2016 15:51<br>
<b>An:</b> Fuenfer-Koenigstein.Benjamin S-IP-AN-TG<br>
<b>Cc:</b> qgis-user<br>
<b>Betreff:</b> Re: [Qgis-user] Performance rendering PostGIS-Data<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<div>
<div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt">Hi <o:p></o:p></p>
</div>
<p class="MsoNormal" style="margin-bottom:12.0pt">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.<o:p></o:p></p>
</div>
<p class="MsoNormal" style="margin-bottom:12.0pt">Perhaps this helps.<o:p></o:p></p>
</div>
<p class="MsoNormal">regards<o:p></o:p></p>
</div>
<p class="MsoNormal">Stefan <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">On Tue, May 24, 2016 at 3:32 PM, <<a href="mailto:Fuenfer-Koenigstein.Benjamin@swm.de" target="_blank">Fuenfer-Koenigstein.Benjamin@swm.de</a>> wrote:<o:p></o:p></p>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:11.0pt">Hi all,</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt">I have a problem gaining better performance during rendering PostGIS Data in QIS. I hope the following example helps understanding the
issue:</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt">I use road-data (line-geometries) in a PostGIS database (localhost), the geometry-table has about 400,000 rows.</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt">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.</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt">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:
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt">SELECT … WHERE "the_geom" && st_makeenvelope(xmin,ymin,xmax,ymax)</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt">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.</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt">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).
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt">That’s what I tried already with no success:</span><o:p></o:p></p>
<p><span lang="EN-US" style="font-size:11.0pt">-</span><span lang="EN-US" style="font-size:7.0pt">
</span><span lang="EN-US" style="font-size:11.0pt">Trying different combinations of styling rules and min/max-scale</span><o:p></o:p></p>
<p><span lang="EN-US" style="font-size:11.0pt">-</span><span lang="EN-US" style="font-size:7.0pt">
</span><span lang="EN-US" style="font-size:11.0pt">Rendering-options in QGIS, simplify geometry, simplify on provider side if possible</span><o:p></o:p></p>
<p><span lang="EN-US" style="font-size:11.0pt">-</span><span lang="EN-US" style="font-size:7.0pt">
</span><span lang="EN-US" style="font-size:11.0pt">Generating indices on all the attributes I use for styling the road-data on the database</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt">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:
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt">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.
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt">Is there a way making QGIS passing SQL-statements generated from current canvas extend AND styling rules? Or another way gaining better
rendering performance?</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt">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!</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt">Best wishes</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Benjamin Fünfer-Königstein<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">S-IP-AN-TG Techn. Geschäftsfelder<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><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" target="_blank">
http://lists.osgeo.org/mailman/listinfo/qgis-user</a><br>
Unsubscribe: <a href="http://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">
http://lists.osgeo.org/mailman/listinfo/qgis-user</a><o:p></o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</div>
</body>
</html>