<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=iso-8859-1">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Arial","sans-serif";
color:black;
mso-fareast-language:EN-US;}
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:"Arial","sans-serif";
color:black;
mso-fareast-language:EN-US;}
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:"Arial","sans-serif";
color:black;
mso-fareast-language:EN-US;}
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:10.0pt;
font-family:"Arial","sans-serif";
color:black;
mso-fareast-language:EN-US;}
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:10.0pt;
font-family:"Arial","sans-serif";
color:black;
mso-fareast-language:EN-US;
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:10.0pt;
font-family:"Arial","sans-serif";
color:black;
mso-fareast-language:EN-US;
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:10.0pt;
font-family:"Arial","sans-serif";
color:black;
mso-fareast-language:EN-US;
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:10.0pt;
font-family:"Arial","sans-serif";
color:black;
mso-fareast-language:EN-US;
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:10.0pt;
font-family:"Arial","sans-serif";
color:black;
mso-fareast-language:EN-US;}
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:10.0pt;
font-family:"Arial","sans-serif";
color:black;
mso-fareast-language:EN-US;
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:"Arial","sans-serif";
color:black;
letter-spacing:.25pt;
mso-fareast-language:EN-US;}
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:"Arial","sans-serif";
color:black;
letter-spacing:.25pt;
mso-fareast-language:EN-US;}
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:"Arial","sans-serif";
color:black;
letter-spacing:.25pt;
mso-fareast-language:EN-US;}
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:"Arial","sans-serif";
color:black;
letter-spacing:.25pt;
mso-fareast-language:EN-US;}
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:"Arial","sans-serif";
color:black;
letter-spacing:.75pt;
mso-fareast-language:EN-US;
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.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.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
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:10.0pt;
font-family:"Arial","sans-serif";
color:black;
mso-fareast-language:EN-US;
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-MailFormatvorlage34
{mso-style-type:personal-compose;
font-family:"Arial","sans-serif";
color:windowtext;}
.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;}
/* List Definitions */
@list l0
{mso-list-id:2101441102;
mso-list-type:hybrid;
mso-list-template-ids:-541807110 535325336 67567619 67567621 67567617 67567619 67567621 67567617 67567619 67567621;}
@list l0:level1
{mso-level-start-at:0;
mso-level-number-format:bullet;
mso-level-text:-;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Arial","sans-serif";
mso-fareast-font-family:Arial;}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
--></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;color:windowtext">Hi all,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:windowtext"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext">I have a problem gaining better performance during rendering PostGIS Data in QIS. I hope the following example helps understanding the issue:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext">I use road-data (line-geometries) in a PostGIS database (localhost), the geometry-table has about 400,000 rows.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext">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:
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext">SELECT … WHERE "the_geom" && st_makeenvelope(xmin,ymin,xmax,ymax)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext">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).
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext">That’s what I tried already with no success:<o:p></o:p></span></p>
<p class="MsoListParagraph" style="text-indent:-18.0pt;mso-list:l0 level1 lfo1"><![if !supportLists]><span lang="EN-US" style="font-size:11.0pt;color:windowtext"><span style="mso-list:Ignore">-<span style="font:7.0pt "Times New Roman"">
</span></span></span><![endif]><span lang="EN-US" style="font-size:11.0pt;color:windowtext">Trying different combinations of styling rules and min/max-scale<o:p></o:p></span></p>
<p class="MsoListParagraph" style="text-indent:-18.0pt;mso-list:l0 level1 lfo1"><![if !supportLists]><span lang="EN-US" style="font-size:11.0pt;color:windowtext"><span style="mso-list:Ignore">-<span style="font:7.0pt "Times New Roman"">
</span></span></span><![endif]><span lang="EN-US" style="font-size:11.0pt;color:windowtext">Rendering-options in QGIS, simplify geometry, simplify on provider side if possible<o:p></o:p></span></p>
<p class="MsoListParagraph" style="text-indent:-18.0pt;mso-list:l0 level1 lfo1"><![if !supportLists]><span lang="EN-US" style="font-size:11.0pt;color:windowtext"><span style="mso-list:Ignore">-<span style="font:7.0pt "Times New Roman"">
</span></span></span><![endif]><span lang="EN-US" style="font-size:11.0pt;color:windowtext">Generating indices on all the attributes I use for styling the road-data on the database<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext">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:
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext">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.
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext">Is there a way making QGIS passing SQL-statements generated from current canvas extend AND styling rules? Or another way gaining better rendering performance?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext">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!<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:windowtext">Best wishes<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:windowtext;mso-fareast-language:DE"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext;mso-fareast-language:DE">Benjamin Fünfer-Königstein<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext;mso-fareast-language:DE">S-IP-AN-TG Techn. Geschäftsfelder</span><span style="color:windowtext;mso-fareast-language:DE"><o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>