[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Help with SQL query?
From: "Paragon Corporation" <lr () pcorp ! us>
Date: 2015-11-30 15:39:55
Message-ID: 000001d12b85$5d6749f0$1835ddd0$ () pcorp ! us
[Download RAW message or body]
This is a multipart message in MIME format.
[Attachment #2 (multipart/alternative)]
This is a multipart message in MIME format.
Darrel,
Don't be quiet. People rarely ask me raster questions, so it's a refreshing change \
:) to be able to sharpen my raster chops.
Thanks,
Regina
http://www.postgis.us
http://postgis.net
From: postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] On Behalf Of \
Darrel Maddy
Sent: Sunday, November 29, 2015 7:38 PM
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Subject: Re: [postgis-users] Help with SQL query?
Dear Regina,
Just to wrap this one up – you were right!
I decided to make the network file in arcgis and then import that into my postgis db. \
I then ran the same query but with the new polygon and the totals were exactly the \
same as the point file and the data I extracted in QGIS.
It is clear that I need to look at how I created the polygon in postgis (although \
whatever problems this caused they were ignored by QGIS). Thankfully I can do what I \
need to do now (the polygon extraction takes around 25 minutes which is half the time \
the point file took and that will do !), and I am happy with the workflow.
Many thanks again for all of your help and there is no need to reply. I promise to be \
quiet for a while now.
Best wishes
Darrel
From: postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] On Behalf Of \
Paragon Corporation
Sent: 29 November, 2015 4:08 PM
To: 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] Help with SQL query?
Darrel,
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:
WITH foo AS (
SELECT mymodel.deposition.rid, mymodel.networkpoly.gid, geom, ST_SummaryStats( \
ST_Clip(rast, geom) ) As st
FROM mymodel.deposition INNER JOIN mymodel.networkpoly ON ( \
ST_Intersects(rast,geom) )
WHERE filename='10_inci.tif'
)
SELECT sum( (st).sum)
FROM foo;
As your original query – was using deposition.
CREATE TABLE mymodel.networkdep AS
SELECT filename, gid, ST_Value(rast, geom) val
FROM mymodel.deposition, mymodel.network
WHERE ST_Intersects(rast, geom)
ORDER BY gid, rid;
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,
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:
WITH foo AS (
SELECT mymodel.concentrated.rid, mymodel.networkpoly.gid, geom, \
ST_SummaryStats( ST_Clip(rast, geom) ) As st
FROM mymodel.concentrated INNER JOIN mymodel.networkpoly ON ( \
ST_Intersects(rast,geom) )
WHERE filename='10_inci.tif'
)
SELECT rid, gid, geom, (st).*
FROM foo;
You might actually want to plot the geom on the map overlaid with the problem tiles \
(or original raster) you classified for that region.
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).
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.
Hope that helps,
Regina
[Attachment #5 (text/html)]
<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;
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;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle88
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle89
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle90
{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'>Don't be \
quiet. People rarely ask me raster questions, so it's a refreshing change \
</span><span style='font-size:11.0pt;font-family:Wingdings;color:#1F497D'>J</span><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> to be able \
to sharpen my raster chops.<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'>Thanks,<o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina<o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><a \
href="http://www.postgis.us">http://www.postgis.us</a><o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><a \
href="http://postgis.net">http://postgis.net</a><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 7:38 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'>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'>Just to wrap \
this one up – you were 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 decided to \
make the network file in arcgis and then import that into my postgis db. I then \
ran the same query but with the new polygon and the totals were exactly the same as \
the point file and the data I extracted 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'>It is clear \
that I need to look at how I created the polygon in postgis (although whatever \
problems this caused they were ignored by QGIS). Thankfully I can do what I \
need to do now (the polygon extraction takes around 25 minutes which is half the time \
the point file took and that will do !), and I am happy with the workflow. \
<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 \
again for all of your help and there is no need to reply. I promise to be quiet for a \
while now.<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'><a name="_MailEndCompose"></a><span \
lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div><div \
style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p \
class=MsoNormal style='margin-left:.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 4:08 \
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:.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'>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 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.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:1.5in'><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.5in'><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.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: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'>SELECT sum( \
(st).sum)<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span \
lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>FROM \
foo;<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal style='margin-left:.5in'><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal style='margin-left:.5in'><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>As your \
original query – was using deposition.<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:10.0pt;font-family:"Courier New"'><o:p> </o:p></span></p><p \
class=MsoNormal style='margin-left:.5in'><span \
style='font-size:10.0pt;font-family:"Courier New"'>CREATE TABLE mymodel.networkdep \
AS<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span \
style='font-size:10.0pt;font-family:"Courier New"'><o:p> </o:p></span></p><p \
class=MsoNormal style='margin-left:.5in'><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 \
style='margin-left:.5in'><span style='font-size:10.0pt;font-family:"Courier \
New"'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span \
style='font-size:10.0pt;font-family:"Courier New"'>FROM mymodel.deposition, \
mymodel.network<o:p></o:p></span></p><p class=MsoNormal \
style='margin-left:.5in'><span style='font-size:10.0pt;font-family:"Courier \
New"'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span \
style='font-size:10.0pt;font-family:"Courier New"'>WHERE ST_Intersects(rast, geom) \
<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span \
style='font-size:10.0pt;font-family:"Courier New"'><o:p> </o:p></span></p><p \
class=MsoNormal style='margin-left:.5in'><span \
style='font-size:10.0pt;font-family:"Courier New"'>ORDER BY gid, \
rid;<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal style='margin-left:.5in'><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal style='margin-left:.5in'><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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 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'>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 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.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:1.5in'><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.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:1.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: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'>SELECT rid, \
gid, geom, (st).*<o:p></o:p></span></p><p class=MsoNormal \
style='margin-left:1.5in'><span lang=EN-GB \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>FROM \
foo;<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'>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.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'>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 \
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic