<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:"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;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle84
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle85
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle86
{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'>I'm a bit confused about something. I thought concentrated was what you were using to determine whether or not to consider a pixel in the deposition raster, so shouldn't the query be:<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:1.0in'><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:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> SELECT mymodel.deposition.rid, mymodel.networkpoly.gid, geom, ST_SummaryStats( ST_Clip(rast, geom) ) As st<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'> FROM mymodel.deposition INNER JOIN mymodel.networkpoly ON ( ST_Intersects(rast,geom) )<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'> WHERE filename='10_inci.tif'<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'>SELECT sum( (st).sum)<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'>FROM foo;<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><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>As your original query – was using deposition.<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:10.0pt;font-family:"Courier New"'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'>CREATE TABLE mymodel.networkdep AS<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'>SELECT filename, gid, ST_Value(rast, geom) val<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'>FROM mymodel.deposition, mymodel.network<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'>WHERE ST_Intersects(rast, geom) <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'>ORDER BY gid, rid;<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><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Gathering from the count it looks like all the pixels of those are treated as data when some should be treated as no data. If it's not an error in the raster you are using,<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'>it would really help to get output of the polygon and also the other stats I mentioned which (st).* outputs So a query something 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 style='margin-left:1.0in'><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:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> SELECT mymodel.concentrated.rid, mymodel.networkpoly.gid, geom, ST_SummaryStats( ST_Clip(rast, geom) ) As st<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'> FROM mymodel.concentrated INNER JOIN mymodel.networkpoly ON ( ST_Intersects(rast,geom) )<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'> WHERE filename='10_inci.tif'<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'>SELECT rid, gid, geom, (st).*<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'>FROM foo;<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'>You might actually want to plot the geom on the map overlaid with the problem tiles (or original raster) you classified for that region.<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'>My guess is something went wrong in the ST_Reclass and all the pixels in those tiles did not get set to 0 (and thus are treated as data doing a summary stats of the problem pre-reclassed tiles and looking at the min,max will give you a clue about that).<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'>This second point may be moot if it's just you meant to put deposition instead of concentration, but good knowledge to keep in mind for future troubleshooting.<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'>Hope that helps,<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'>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><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><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><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> Sunday, November 29, 2015 10:43 AM<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'>Dear Regina and Roxanne,<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'>Many thanks for your suggestions. I reran the query to show the breakdown of the sum elements as suggested. <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 I know (or at least think I know) from the points version that there should be around 15500 intersect, Looking at the table of information there are some odd counts for two rids.<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 count 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'>1779 65536 16.78682<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'>1810 65536 3.576006<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'>There are 186 rids in total and although there are duplicate rids there are no duplicate counts that I can see, so I think that is OK. The summation of the sum column gives the high value previously reported.<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'>The counts on the two rids shown above make little sense and they do, of course, seem to account for the discrepancy in sums (albeit removing these would overshoot the alternate sum at 0.898929)<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’m afraid this does not make me any wiser, especially as if I load the polygon and raster into QGIS from the same postgis tables and do the extraction there ( using grid statistics for polygons and then save the attribute table to a csv), the summation gives the lower sum and a cell count of 15k which looks right.<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 must confess I have no idea why the sql query returns these unusual counts and sums. The actual raw data tables appear to be fine.<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><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> 29 November 2015 03:30<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'>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'>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 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:1.0in'><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:1.0in'><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:1.0in'><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:1.0in'><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: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'>SELECT rid, gid, (st).*<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'>FROM foo;<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'>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: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'>So your query should have<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'>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: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'>Hope that helps,<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'>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'><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></div></body></html>