<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: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;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle79
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle80
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle81
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle82
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle83
{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'>Darrel,<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'>Try not summing. That might give you more information about what is wrong. So instead do this:<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 style='margin-left:.5in'><span lang=EN-GB 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:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> SELECT mymodel.concentrated.rid, mymodel.networkpoly.gid, ST_SummaryStats( ST_Clip(rast, geom) ) As st<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> FROM mymodel.concentrated INNER JOIN mymodel.networkpoly ON ( ST_Intersects(rast,geom) )<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> WHERE filename='10_inci.tif'<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.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:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT rid, gid, (st).*<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=EN-GB 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:.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:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>That might give you multiple records per rid which might be okay since a single tile might be clipped by more than one polygon. At anyrate, you'll get a lot more stats and be able to target the tile/poly combo that might be at issue.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.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:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So your query should have<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.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:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>rid, gid, count, sum, mean, stddev, min, max (for each tile/geom combo – which should hopefully give you a clue what went wrong).<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.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:.5in'><span lang=EN-GB 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 lang=EN-GB 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 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:.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><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>Darrel Maddy<br><b>Sent:</b> Saturday, November 28, 2015 6:43 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 lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Oh dear I spoke too soon!<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.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:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I created the polygon as directed and this is fine when I visualise it in QGIS. <o:p></o:p></span></p><p class=MsoNormal style='margin-left:.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:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Now when I did the intersects and sum yesterday using the point data for one of the rasters I got a total of 0.946094531.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.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:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>When I do the extract in QGIS using the polygon created in postgis I get the same total.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.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:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Alas when I do this (note this is the same raster/polygon intersection):<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.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:.5in'><span lang=EN-GB 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:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> SELECT mymodel.concentrated.rid, ST_SummaryStats( ST_Clip(rast, geom) ) As st<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> FROM mymodel.concentrated INNER JOIN mymodel.networkpoly ON ( ST_Intersects(rast,geom) )<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> WHERE filename='10_inci.tif'<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.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:.5in'><span lang=EN-GB 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:.5in'><span lang=EN-GB 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:.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:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I get this 21.261754843969<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.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:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I believe this answer is wrong – so what did I miss?<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.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:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Thanks<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.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:.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:.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:.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:.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:.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:.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:.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:.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 [<a href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Darrel Maddy<br><b>Sent:</b> 28 November 2015 22:51<br><b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><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'><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.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:.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:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Wow that makes a tremendous difference. I used this polygon to extract the values using a modified form of the query you suggested in an earlier post and the whole thing took under five minutes! <o:p></o:p></span></p><p class=MsoNormal style='margin-left:.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:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>No question postgis is the best way to do this.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.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:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I cannot thank you enough – but then again I cannot check the answer it gives is right </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'> - but at least the methodology is consistent. <o:p></o:p></span></p><p class=MsoNormal style='margin-left:.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:.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:.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:.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:.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:.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:.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:.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:.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:.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 [<a href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Paragon Corporation<br><b>Sent:</b> 28 November 2015 05:04<br><b>To:</b> 'PostGIS Users Discussion' <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><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'><span lang=EN-GB><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'>For completeness, your vector query would then look like<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'>CREATE TABLE mymodel.network AS<o:p></o:p></span></p><pre style='margin-left:.5in'><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.5in'><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.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 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.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 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.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 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.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:.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 [<a href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>] <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' <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><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 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'>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: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 that's a fairly fast operation as I recall.<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'><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 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.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'>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.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'>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.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'>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.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 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.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'>Thanks again<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><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> 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.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'>Darrel,<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'>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.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'>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.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'>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.5in'><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.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'>and this SQL Statement<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'>CREATE TABLE mymodel.network AS<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 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.5in'><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.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'>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.5in'><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.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'>So your query would look something like<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:2.0in'><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.5in'><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.5in'><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:2.0in'><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:2.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:2.0in'><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:2.0in'><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'>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></div></body></html>