<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;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle87
{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-GB" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Dear Regina,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Apologies I pasted from the same operation on a different table.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">This operation is being used to extract various attributes from the same location. The data are stored in a number of tables (e.g.
table concentrated[has rasters with filenames such as 10_inci.tif], table diffuse [filenames are 10_ero.tif], deposition [filenames are 10_depo] ..etc). There are 8 such tables – each have 136 tifs tiles at 256x256). I’m afraid the file naming system is
little of a dogs breakfast – sorry.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">The polygon for the network is derived from a flow accumulation raster (10_FA.tif etc) – I did this separately and saved the result
as networkpoly which is used in all cases to extract from the various rasters.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Anyhow the values I used are from the concentrated table i.e. if I do this using intersection with the points file I get a sum of
~0.9, I get the same sum value if I do the extraction in QGIS using networkpoly. If I use the query in postgres I get ~21 for the summation.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">The networkpolygon looks correct in QGIS so I am a little confused over the suggestion that ST_Reclass may be the problem – that seems
to have worked fine, there is certainly nothing visable that would account for intersection with an additional ~130k cells in the underlying raster. Anyhow I will continue to dig, but at present it is difficult to see why these would work in QGIS but not in
postgres/postgis<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Apologies again for the confusion with the filenames – the operation remains the same.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Darrel<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><a name="_MailEndCompose"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></a></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span lang="EN-US" 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> 29 November, 2015 4:08 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"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">Darrel,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" 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 lang="EN-US" 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">WITH foo 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 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 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 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 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">SELECT sum( (st).sum)<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 foo;<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" 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 lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New""><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New"">CREATE TABLE mymodel.networkdep AS<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New""><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" 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 lang="EN-US" style="font-size:10.0pt;font-family:"Courier New""><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New"">FROM mymodel.deposition, mymodel.network<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New""><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New"">WHERE ST_Intersects(rast, geom)
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New""><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New"">ORDER BY gid, rid;<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" 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 lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" 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 lang="EN-US" 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">WITH foo 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 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 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 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 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">SELECT rid, gid, geom, (st).*<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 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"><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">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 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">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 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 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"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" 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 lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span lang="EN-US" 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> Sunday, November 29, 2015 10:43 AM<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-US"><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">Dear Regina and Roxanne,<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">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 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">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 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">rid count sum<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">1779 65536 16.78682<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">1810 65536 3.576006<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">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 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">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 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’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 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 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 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">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"><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 lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span lang="EN-US" 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"><o:p> </o:p></p>
<p class="MsoNormal" style="margin-left:.5in"><span lang="EN-US" 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-US" 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-US" 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 lang="EN-US" 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">WITH foo 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 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 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 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 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">SELECT rid, gid, (st).*<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 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">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 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 should have<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">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 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:2.0in"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
</div>
</body>
</html>