<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 15 (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:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
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;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;}
p.yiv5167837039msoacetate, li.yiv5167837039msoacetate, div.yiv5167837039msoacetate
{mso-style-name:yiv5167837039msoacetate;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.yiv5167837039msolistparagraph, li.yiv5167837039msolistparagraph, div.yiv5167837039msolistparagraph
{mso-style-name:yiv5167837039msolistparagraph;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.yiv5167837039msonormal, li.yiv5167837039msonormal, div.yiv5167837039msonormal
{mso-style-name:yiv5167837039msonormal;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.yiv5167837039msochpdefault, li.yiv5167837039msochpdefault, div.yiv5167837039msochpdefault
{mso-style-name:yiv5167837039msochpdefault;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.yiv5167837039msonormal1, li.yiv5167837039msonormal1, div.yiv5167837039msonormal1
{mso-style-name:yiv5167837039msonormal1;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.yiv5167837039msoacetate1, li.yiv5167837039msoacetate1, div.yiv5167837039msoacetate1
{mso-style-name:yiv5167837039msoacetate1;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.yiv5167837039msolistparagraph1, li.yiv5167837039msolistparagraph1, div.yiv5167837039msolistparagraph1
{mso-style-name:yiv5167837039msolistparagraph1;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.yiv5167837039msochpdefault1, li.yiv5167837039msochpdefault1, div.yiv5167837039msochpdefault1
{mso-style-name:yiv5167837039msochpdefault1;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.yiv5167837039msonormal2, li.yiv5167837039msonormal2, div.yiv5167837039msonormal2
{mso-style-name:yiv5167837039msonormal2;
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.yiv5167837039msoacetate2, li.yiv5167837039msoacetate2, div.yiv5167837039msoacetate2
{mso-style-name:yiv5167837039msoacetate2;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.yiv5167837039msolistparagraph2, li.yiv5167837039msolistparagraph2, div.yiv5167837039msolistparagraph2
{mso-style-name:yiv5167837039msolistparagraph2;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.yiv5167837039msonormal3, li.yiv5167837039msonormal3, div.yiv5167837039msonormal3
{mso-style-name:yiv5167837039msonormal3;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.yiv5167837039msochpdefault2, li.yiv5167837039msochpdefault2, div.yiv5167837039msochpdefault2
{mso-style-name:yiv5167837039msochpdefault2;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.yiv5167837039msonormal11, li.yiv5167837039msonormal11, div.yiv5167837039msonormal11
{mso-style-name:yiv5167837039msonormal11;
margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Times New Roman",serif;
color:black;}
p.yiv5167837039msoacetate11, li.yiv5167837039msoacetate11, div.yiv5167837039msoacetate11
{mso-style-name:yiv5167837039msoacetate11;
margin:0in;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Times New Roman",serif;
color:black;}
p.yiv5167837039msolistparagraph11, li.yiv5167837039msolistparagraph11, div.yiv5167837039msolistparagraph11
{mso-style-name:yiv5167837039msolistparagraph11;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Times New Roman",serif;
color:black;}
p.yiv5167837039msochpdefault11, li.yiv5167837039msochpdefault11, div.yiv5167837039msochpdefault11
{mso-style-name:yiv5167837039msochpdefault11;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:10.0pt;
font-family:"Times New Roman",serif;}
span.yiv5167837039msohyperlink
{mso-style-name:yiv5167837039msohyperlink;}
span.yiv5167837039msohyperlinkfollowed
{mso-style-name:yiv5167837039msohyperlinkfollowed;}
span.yiv5167837039htmlpreformattedchar
{mso-style-name:yiv5167837039htmlpreformattedchar;}
span.yiv5167837039msohyperlink1
{mso-style-name:yiv5167837039msohyperlink1;}
span.yiv5167837039msohyperlinkfollowed1
{mso-style-name:yiv5167837039msohyperlinkfollowed1;}
span.yiv5167837039htmlpreformattedchar1
{mso-style-name:yiv5167837039htmlpreformattedchar1;}
span.yiv5167837039emailstyle221
{mso-style-name:yiv5167837039emailstyle221;}
span.yiv5167837039emailstyle231
{mso-style-name:yiv5167837039emailstyle231;}
span.yiv5167837039emailstyle241
{mso-style-name:yiv5167837039emailstyle241;}
span.yiv5167837039emailstyle251
{mso-style-name:yiv5167837039emailstyle251;}
span.yiv5167837039emailstyle261
{mso-style-name:yiv5167837039emailstyle261;}
span.yiv5167837039emailstyle271
{mso-style-name:yiv5167837039emailstyle271;}
span.yiv5167837039emailstyle281
{mso-style-name:yiv5167837039emailstyle281;}
span.yiv5167837039emailstyle291
{mso-style-name:yiv5167837039emailstyle291;}
span.yiv5167837039emailstyle301
{mso-style-name:yiv5167837039emailstyle301;}
span.yiv5167837039emailstyle311
{mso-style-name:yiv5167837039emailstyle311;}
span.yiv5167837039emailstyle55
{mso-style-name:yiv5167837039emailstyle55;}
span.yiv5167837039msohyperlink2
{mso-style-name:yiv5167837039msohyperlink2;
color:blue;
text-decoration:underline;}
span.yiv5167837039msohyperlinkfollowed2
{mso-style-name:yiv5167837039msohyperlinkfollowed2;
color:purple;
text-decoration:underline;}
span.yiv5167837039htmlpreformattedchar2
{mso-style-name:yiv5167837039htmlpreformattedchar2;
font-family:Consolas;}
span.yiv5167837039msohyperlink11
{mso-style-name:yiv5167837039msohyperlink11;
color:#0563C1;
text-decoration:underline;}
span.yiv5167837039msohyperlinkfollowed11
{mso-style-name:yiv5167837039msohyperlinkfollowed11;
color:#954F72;
text-decoration:underline;}
span.yiv5167837039htmlpreformattedchar11
{mso-style-name:yiv5167837039htmlpreformattedchar11;
font-family:Consolas;
color:black;}
span.yiv5167837039emailstyle2211
{mso-style-name:yiv5167837039emailstyle2211;
color:windowtext;}
span.yiv5167837039emailstyle2311
{mso-style-name:yiv5167837039emailstyle2311;
color:#1F497D;}
span.yiv5167837039emailstyle2411
{mso-style-name:yiv5167837039emailstyle2411;
color:#1F497D;}
span.yiv5167837039emailstyle2511
{mso-style-name:yiv5167837039emailstyle2511;
color:windowtext;}
span.yiv5167837039emailstyle2611
{mso-style-name:yiv5167837039emailstyle2611;
color:#1F497D;}
span.yiv5167837039emailstyle2711
{mso-style-name:yiv5167837039emailstyle2711;
color:#1F497D;}
span.yiv5167837039emailstyle2811
{mso-style-name:yiv5167837039emailstyle2811;
color:#1F497D;}
span.yiv5167837039emailstyle2911
{mso-style-name:yiv5167837039emailstyle2911;
color:#1F497D;}
span.yiv5167837039emailstyle3011
{mso-style-name:yiv5167837039emailstyle3011;
color:#1F497D;}
span.yiv5167837039emailstyle3111
{mso-style-name:yiv5167837039emailstyle3111;
color:windowtext;}
span.yiv5167837039emailstyle551
{mso-style-name:yiv5167837039emailstyle551;
color:#1F497D;}
span.EmailStyle70
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle71
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle72
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle73
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle74
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle75
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle76
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle77
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle78
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
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=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>For completeness, your vector query would then look like<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>CREATE TABLE mymodel.network AS<o:p></o:p></span></p><pre><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT rid As gid, ST_Polygon(ST_Reclass(rast, 1, </span>'[0-600):0, [600-10000]:1',<span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>'1BB', 0) ) As geom</span><o:p></o:p></pre><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>FROM </span><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>mymodel.concentrated ;<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I think you can put in –number in there like - -1000-600. There was an issue way back with negatives but I thnk that was fixed in PostGIS 2.1 something so should work<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So what that basically does is create a new raster from original setting of pixel values from 0 to < 600 to 0 and from >= 600 to 10000 to 1 and making that a 1BB (1-bit booleanl raster), and then definiing 0 as no data so that when you convert it to a multipolygon you'll only vectorize the 600-10000 range.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I think ST_polygon against a 1BB is faster than larger band pixel types.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Hope that helps,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:.5in'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Paragon Corporation<br><b>Sent:</b> Friday, November 27, 2015 10:04 PM<br><b>To:</b> 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org><br><b>Subject:</b> Re: [postgis-users] Help with SQL query?<o:p></o:p></span></p></div></div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Darrel,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I think the equivalent in PostGIS terminology would be ST_Reclass - </span><a href="http://postgis.net/docs/manual-2.2/RT_ST_Reclass.html"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>http://postgis.net/docs/manual-2.2/RT_ST_Reclass.html</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>And that's a fairly fast operation as I recall.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Hope that helps,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:1.0in'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users [</span><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>mailto:postgis-users-bounces@lists.osgeo.org</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>] <b>On Behalf Of </b>Darrel Maddy<br><b>Sent:</b> Friday, November 27, 2015 4:09 PM<br><b>To:</b> PostGIS Users Discussion <</span><a href="mailto:postgis-users@lists.osgeo.org"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>postgis-users@lists.osgeo.org</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>><br><b>Subject:</b> Re: [postgis-users] Help with SQL query?<o:p></o:p></span></p></div></div><p class=MsoNormal style='margin-left:1.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Dear Regina,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Yes, I should have been clearer. To be honest I did not fully understand what I was trying to do at the outset </span><span lang=EN-GB style='font-size:11.0pt;font-family:Wingdings;color:#1F497D'>J</span><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The binary raster to point conversion was an afterthought as I had already been extracting data along short transects using a point shape file. For the record arc has a function in the raster calculator which works as a conditional such that <br>CON(raster, true, false, condition) which in my case was simply CON(FA, 1, 0 “value > 600”). This produces a raster which I then had to convert to points and eliminate the zeros. I’m sure there is a way to do this in QGIS but the current raster calculator does not allow that directly. FYI my rasters have only one band as they are being used to store numerical model matrix outputs so that I can readily visualise them in order to allow me to see structures I would not recognise hidden within the 5 million cell datasets.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Anyhow I will experiment with doing more of this in postgis (it would be great if I could script an end-to-end solution). Once I am happy with the numerical model I will have the model write the data directly into postgis. <o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I have made some progress this week thanks to your help. Hopefully I am beginning to see how best to use this tool for my intended purpose.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Thanks again<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Darrel<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:1.0in'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users [</span><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>mailto:postgis-users-bounces@lists.osgeo.org</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>] <b>On Behalf Of </b>Paragon Corporation<br><b>Sent:</b> 27 November 2015 20:02<br><b>To:</b> 'PostGIS Users Discussion' <</span><a href="mailto:postgis-users@lists.osgeo.org"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>postgis-users@lists.osgeo.org</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>><br><b>Subject:</b> Re: [postgis-users] Help with SQL query?<o:p></o:p></span></p></div></div><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Darrel,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Oh that's what you are trying to do sorry I didn't recognize that whole CASE thing as a binary check operation until you described the purpose.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>For the bit operation type stuff it is much faster to define that 0/1 as a geometry (which it looks like you've done, but I don't know if you just have one pixel cell per or what or details of how you do it in ArcGIS. I suspect that logic can be recreated easily in PostGIS with something like below:<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>If your network raster is just a set of 0s and 1s (is it a 1BB) or you just want to treat 0 as no-date (which is essentially what you case statement was trying to do I think) then you could just convert it to a geometry with these functions<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><a href="http://postgis.net/docs/manual-2.2/RT_ST_Polygon.html"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>http://postgis.net/docs/manual-2.2/RT_ST_Polygon.html</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>, </span><a href="http://postgis.net/docs/manual-2.2/RT_ST_SetBandNoDataValue.html"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>http://postgis.net/docs/manual-2.2/RT_ST_SetBandNoDataValue.html</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> <o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>and this SQL Statement<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>CREATE TABLE mymodel.network AS<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT rid As gid, ST_Polygon(ST_SetBandNoDataValue(rast,1, 0) ) As geom<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>FROM </span><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>mymodel.concentrated ;</span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Once you have that concentrated as a network channel as a geometry, then you can use ST_Clip and that should be pretty fast and give you the same results.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><a href="http://postgis.net/docs/manual-2.2/RT_ST_Clip.html"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>http://postgis.net/docs/manual-2.2/RT_ST_Clip.html</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So your query would look something like<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>WITH foo AS (<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT mymodel.deposition.rid, ST_SummaryStats( ST_Clip(rast, geom) ) As st<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> FROM mymodel.deposition INNER JOIN mymodel.network ON ( ST_Intersects(rast,geom) )<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>)<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT SUM( (st).sum )<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>FROM foo;<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Hope that helps,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><a href="http://www.postgis.us"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>http://www.postgis.us</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><a href="http://postgis.net"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>http://postgis.net</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:1.5in'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users [</span><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>mailto:postgis-users-bounces@lists.osgeo.org</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>] <b>On Behalf Of </b>Darrel Maddy<br><b>Sent:</b> Friday, November 27, 2015 12:42 PM<br><b>To:</b> PostGIS Users Discussion <</span><a href="mailto:postgis-users@lists.osgeo.org"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>postgis-users@lists.osgeo.org</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>><br><b>Subject:</b> Re: [postgis-users] Help with SQL query?<o:p></o:p></span></p></div></div><p class=MsoNormal style='margin-left:1.5in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Dear Regina,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Many thanks for this suggestion.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I ran the query in this form and for one raster it takes 3372s (~55 mins). I guess that is what I had anticipated from the single tile exercise I ran with the alternate algorithm.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>This still seems a little too long however and so I have started to explore ways to improve upon this by pre-processing some of the data. The cells of interest represent the ‘main channels’ in a drainage network. Although in future this network may change position from one output timestep to another (they are actually 1000l iterations of the model apart) in this particular variant the position of these cells is static. With that in mind I decided to create a binary raster where the 1’s represent the channel cells (I had to do this in arcgis as QGIS does not appear to have a Con function in the raster calculator!) and then exported this as a point layer. I then deleted the points coded zero and saved the shp file in QGIS. I imported the ntwork shp file into postgis (there are about 15500 15 points) and I am now running:<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>CREATE TABLE mymodel.networkdep AS<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT filename, gid, ST_Value(rast, geom) val<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>FROM mymodel.deposition, mymodel.network<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>WHERE ST_Intersects(rast, geom) <o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>ORDER BY gid, rid;<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I will sum by raster (i.e. filename) using the new table but will settle for just having the relevant data for now. This took 2057s(~35 minutes!) to complete! <o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>If this is the best way to do it I will explore the OGR library and try and hardcode the network point output directly into the model or, more realistically, write a short routine to extract this automatically from the flow accumulation output rasters without recourse to a GIS.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I am learning a lot through this exercise, so thanks once again to all of you who have made suggestions, they are very much appreciated.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Best wishes<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Darrel <o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:1.5in'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users [</span><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>mailto:postgis-users-bounces@lists.osgeo.org</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>] <b>On Behalf Of </b>Paragon Corporation<br><b>Sent:</b> 26 November 2015 05:08<br><b>To:</b> 'PostGIS Users Discussion' <</span><a href="mailto:postgis-users@lists.osgeo.org"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>postgis-users@lists.osgeo.org</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>><br><b>Subject:</b> Re: [postgis-users] Help with SQL query?<o:p></o:p></span></p></div></div><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>That timing seems much slower than I recall.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>FWIW expression based mapalgebra as I recall is slower than using the call back function approach. So you could try wrapping your CASE in a call back function.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>However I think something else might be going on here and postgres might be repeating work. I forgot under what conditions it decides to reevaluate a function call, I just remember being really surprised by it.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>To avoid that, you can try using a CTE, also you don't need that ST_Union call which for larger number of rasters is expensive, and you might even generate a raster that is too big to compute.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I'm also guessing your rasts are all tiled the same, so you really don't need ST_Intersects, just use the same box operator<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So try this:<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>WITH foo AS (<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT ST_SummaryStats( ST_MapAlgebra(deposition.rast, concentrated.rast, 'CASE WHEN [rast2] > 0.0 THEN [rast1] ELSE NULL END' ) ) As st<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> FROM mymodel.deposition INNER JOIN mymodel.concentrated ON ( deposition.rast ~= concentrated.rast )<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> WHERE deposition.rid=1</span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>)<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT SUM( (st).sum )<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>FROM foo;<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Hope that helps,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><a href="http://www.postgis.us"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>http://www.postgis.us</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><a href="http://postgis.net"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>http://postgis.net</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:2.0in'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users [</span><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>mailto:postgis-users-bounces@lists.osgeo.org</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>] <b>On Behalf Of </b>Darrel Maddy<br><b>Sent:</b> Wednesday, November 25, 2015 5:06 PM<br><b>To:</b> PostGIS Users Discussion <</span><a href="mailto:postgis-users@lists.osgeo.org"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>postgis-users@lists.osgeo.org</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>>; Brent Wood <</span><a href="mailto:pcreso@yahoo.com"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>pcreso@yahoo.com</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>><br><b>Subject:</b> Re: [postgis-users] Help with SQL query?<o:p></o:p></span></p></div></div><p class=MsoNormal style='margin-left:2.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Dear Brent,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I must confess that my attempts to do this are so far proving very unsuccessful<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>If I run the following query:<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT (ST_SummaryStats(ST_Union(rast))).sum AS sum<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>FROM (SELECT ST_MapAlgebra(deposition.rast, concentrated.rast, 'CASE WHEN [rast2] > 0.0 THEN [rast1] ELSE NULL END' ) As rast<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> FROM mymodel.deposition, mymodel.concentrated<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> WHERE ST_Intersects(deposition.rast, concentrated.rast) AND deposition.rid=1 ) foo ;<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>It takes around 30 seconds to complete as I assume it is only looking at one tile(they are 256x256 pixels) i.e. rid 1. It is not easy to check the sum – for that I need one complete raster.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>For the record this was marginally faster than<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT (ST_SummaryStats(ST_Union(rast))).sum AS sum<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>FROM (SELECT ST_MapAlgebra(deposition.rast, concentrated.rast, 'CASE WHEN [rast2] > 0.0 THEN [rast1] ELSE NULL END' ) As rast<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> FROM mymodel.deposition, mymodel.concentrated<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> WHERE mymodel.deposition.filename='10_depo.tif' AND ST_UpperleftX(mymodel.deposition.rast) = ST_UpperleftX(mymodel.concentrated.rast) AND <o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> ST_UpperleftY(mymodel.deposition.rast) = ST_UpperleftY(mymodel.deposition.rast) ) foo ;<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Even after I built indexes for the clauses after the WHERE.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Now there are 144 tiles in each of the rasters I want to perform this operation on. Logic would therefore suggest this should take ~4500s<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>However when I perform the following query<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT (ST_SummaryStats(ST_Union(rast))).sum AS sum<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>FROM (SELECT ST_MapAlgebra(deposition.rast, concentrated.rast, 'CASE WHEN [rast2] > 0.0 THEN [rast1] ELSE NULL END' ) As rast<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> FROM mymodel.deposition, mymodel.concentrated<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> WHERE ST_Intersects(deposition.rast, concentrated.rast) AND deposition.filename='10_depo.tif' ) foo ;<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The query is still running after 18000s! I must therefore assume I have done something wrong but as you may have guessed the answer eludes me.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Any further suggestions would be welcome but I will continue to try and find a solution as I have 135 rasters to perform this operations on now and potentially many thousands more in the future.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Darrel<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:2.0in'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users [</span><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>mailto:postgis-users-bounces@lists.osgeo.org</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>] <b>On Behalf Of </b>Darrel Maddy<br><b>Sent:</b> 24 November 2015 19:52<br><b>To:</b> Brent Wood <</span><a href="mailto:pcreso@yahoo.com"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>pcreso@yahoo.com</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>>; </span><a href="mailto:postgis-users@lists.osgeo.org"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>postgis-users@lists.osgeo.org</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'><br><b>Subject:</b> Re: [postgis-users] Help with SQL query?<o:p></o:p></span></p></div></div><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Dear Brent,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Many thanks. The data are tiled (256x256) hence the large number of rows from the original 135 tifs. I did not build any indexes however, so I will do some reading and see how best to approach that (the threads you listed look useful so thanks for that).<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I will run some additional mini queries limited to just one comparison and check using QGIS as you suggest – I probably should have done that first!<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>My workstation has 64GB Ram and I would be surprised if it was significantly caching to disk. I also have a hexacore intel extreme processor so I would not expect this to be hardware limited. I must confess I expected it to finish within a couple of hours.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Anyhow very many thanks. I will continue to explore and report back hopefully with positive news.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Darrel<o:p></o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:2.0in'><a name="_MailEndCompose"></a><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:2.0in'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> Brent Wood [</span><a href="mailto:pcreso@yahoo.com"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>mailto:pcreso@yahoo.com</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>] <br><b>Sent:</b> 24 November, 2015 7:36 PM<br><b>To:</b> Darrel Maddy <</span><a href="mailto:darrel.maddy@newcastle.ac.uk"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>darrel.maddy@newcastle.ac.uk</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>>; </span><a href="mailto:postgis-users@lists.osgeo.org"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>postgis-users@lists.osgeo.org</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'><br><b>Subject:</b> Re: [postgis-users] Help with SQL query?<o:p></o:p></span></p></div></div><p class=MsoNormal style='margin-left:2.0in'><span lang=EN-GB><o:p> </o:p></span></p><div><div id="yui_3_16_0_1_1448337433794_89794"><p class=MsoNormal style='margin-left:2.0in;background:white'><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'>Indexing can improve performance by 100s of x, without them things can be slow. Also, did you tile the images when you imported them? If not, then each iteration is working through all the pixels in the image, rather than a small subset. Essentially with tiles, you have a deep (long) table rather than a wide one. RDBMSs work better with lots of small records than a few wide ones, especially when indexes are used.<o:p></o:p></span></p></div><div id="yui_3_16_0_1_1448337433794_93227"><p class=MsoNormal style='margin-left:2.0in;background:white'><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'><o:p> </o:p></span></p></div><div id="yui_3_16_0_1_1448337433794_93228"><p class=MsoNormal style='margin-left:2.0in;background:white'><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'>This might help:<o:p></o:p></span></p></div><div id="yui_3_16_0_1_1448337433794_93229"><p class=MsoNormal style='margin-left:2.0in;background:white'><a href="http://gis.stackexchange.com/questions/43053/how-to-speed-up-queries-for-raster-databases"><span lang=EN-GB style='font-family:"Verdana",sans-serif'>http://gis.stackexchange.com/questions/43053/how-to-speed-up-queries-for-raster-databases</span></a><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'><o:p></o:p></span></p></div><div id="yui_3_16_0_1_1448337433794_94765"><p class=MsoNormal style='margin-left:2.0in;background:white'><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'><o:p> </o:p></span></p></div><div id="yui_3_16_0_1_1448337433794_94766"><p class=MsoNormal style='margin-left:2.0in;background:white'><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'>and see the raster tutorial they mention for the SRTM data, as to how that is loaded into Postgis:<o:p></o:p></span></p></div><div id="yui_3_16_0_1_1448337433794_94767"><p class=MsoNormal style='margin-left:2.0in;background:white'><a href="https://trac.osgeo.org/postgis/wiki/WKTRasterTutorial01"><span lang=EN-GB style='font-family:"Verdana",sans-serif'>https://trac.osgeo.org/postgis/wiki/WKTRasterTutorial01</span></a><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'><o:p></o:p></span></p></div><div id="yui_3_16_0_1_1448337433794_89859"><p class=MsoNormal style='margin-left:2.0in;background:white'><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'><o:p> </o:p></span></p></div><div id="yui_3_16_0_1_1448337433794_90054"><p class=MsoNormal style='margin-left:2.0in;background:white'><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'>To test the logic (the syntax is correct or it wouldn't be working) you could add to the "where" clause an extra filter so that only a small subset of the entire dataset is included (like just one QGIS operation) then compare this with the QGIS result.<o:p></o:p></span></p></div><div id="yui_3_16_0_1_1448337433794_91095"><p class=MsoNormal style='margin-left:2.0in;background:white'><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'><o:p> </o:p></span></p></div><div id="yui_3_16_0_1_1448337433794_91168"><p class=MsoNormal style='margin-left:2.0in;background:white'><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'>That would be much faster that testing on the entire dataset. Once you know it is correct for the test case(s), then you can run it on the complete set.<o:p></o:p></span></p></div><div id="yui_3_16_0_1_1448337433794_92010"><p class=MsoNormal style='margin-left:2.0in;background:white'><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'><o:p> </o:p></span></p></div><div id="yui_3_16_0_1_1448337433794_92011"><p class=MsoNormal style='margin-left:2.0in;background:white'><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'>Note that some queries can build up large in-memory objects, so make sure your system is not swapping to disk, as that will also slow things down (hugely).<o:p></o:p></span></p></div><div id="yui_3_16_0_1_1448337433794_92207"><p class=MsoNormal style='margin-left:2.0in;background:white'><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'><o:p> </o:p></span></p></div><div id="yui_3_16_0_1_1448337433794_92208"><p class=MsoNormal style='margin-left:2.0in;background:white'><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'>Cheers<o:p></o:p></span></p></div><div id="yui_3_16_0_1_1448337433794_92210"><p class=MsoNormal style='margin-left:2.0in;background:white'><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'><o:p> </o:p></span></p></div><div id="yui_3_16_0_1_1448337433794_92211"><p class=MsoNormal style='margin-left:2.0in;background:white'><span lang=EN-GB style='font-family:"Verdana",sans-serif;color:black'>Brent<o:p></o:p></span></p></div></div></div></body></html>