Great.<div>Thank you so much. I should have noticed that these were unioned numbers. </div><div>Btw. are there any way of getting the count of pixels within a polygon without actually aggregating them or union them?</div><div>
<br></div><div>Andreas<br><br><div class="gmail_quote">2011/2/24 Paragon Corporation <span dir="ltr"><<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">Andreas,</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">Sorry should have recognized what you're doing. The
intersection returns a polygon which is a union of the clipped raster pixel
squares. So you need to use Sum of area instead and then divide by the
area of a pixel to get the equivalent of your count.</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">So </font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span>
<div>SELECT gid, <span>SUM</span>(<span>ST_Area(</span>(foo.geomval).<span>geom)</span>)<span>/ [put your
pixel area size here] </span> as ct</div><div class="im">
<div>FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid,
ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM globshort,
priogrid_land) AS foo</div>
<div>WHERE gid >= 139358 AND gid <= 139365</div>
<div>GROUP BY gid</div>
<div>ORDER BY gid</div></div></span></div><br>
<div dir="ltr" lang="en-us" align="left">
<hr>
<font size="2" face="Tahoma"><b>From:</b> Andreas Forų Tollefsen
[mailto:<a href="mailto:andreasft@gmail.com" target="_blank">andreasft@gmail.com</a>] <br><b>Sent:</b> Thursday, February 24, 2011 8:33
AM<br><b>To:</b> PostGIS Users Discussion<br><b>Cc:</b> Paragon
Corporation<div><div></div><div class="h5"><br><b>Subject:</b> Re: [postgis-users] ST_Value from
Polygon<br></div></div></font><br></div><div><div></div><div class="h5">
<div></div>I am a bit unsure whether my results are actually correct. According
to a total count using the below query, I get very different results between the
cells.
<div>Since the raster does actually cover the whole vector cell, i would assume
that the count should be similar in all cells. Meaning, the pixel count should
be the same.</div>
<div>What i get is different, and it seems that the query is not providing me
with the number of pixels within the grid cell.</div>
<div>Any idea why this is so different?</div>
<div><br></div>
<div>
<div>SELECT gid, count((foo.geomval).val) as ct</div>
<div>FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid,
ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM globshort,
priogrid_land) AS foo</div>
<div>WHERE gid >= 139358 AND gid <= 139365</div>
<div>GROUP BY gid</div>
<div>ORDER BY gid</div>
<div><br></div>
<div>Result:</div>
<div>
<div>139358;632</div>
<div>139359;1030</div>
<div>139360;912</div>
<div>139361;731</div>
<div>139362;760</div>
<div>139363;1230</div>
<div>139364;1314</div>
<div>139365;1014</div></div>
<div><br></div>
<div>The attached image shows the raster pixels within one cell.</div>
<div><br></div><br>
<div class="gmail_quote">2011/2/24 Andreas Forų Tollefsen <span dir="ltr"><<a href="mailto:andreasft@gmail.com" target="_blank">andreasft@gmail.com</a>></span><br>
<blockquote style="border-left:#ccc 1px solid;margin:0px 0px 0px 0.8ex;padding-left:1ex" class="gmail_quote">Thanks!
<div>That solved it.</div>
<div><br></div>
<div>This will probably take a lot of time. I have 259200 polygons measuring
0.5 x 0.5 decimal degrees while the raster dataset is of global cover and has
a pixelsize of 0.00277777777777778x0.00277777777777778. </div>
<div><br></div><font color="#888888">
<div>Andreas</div></font>
<div>
<div></div>
<div>
<div><br></div>
<div><br>
<div class="gmail_quote">2011/2/23 Paragon Corporation <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span><br>
<blockquote style="border-left:#ccc 1px solid;margin:0px 0px 0px 0.8ex;padding-left:1ex" class="gmail_quote">
<div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">Andrea,</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><font face="Arial"><font size="2"><font color="#0000ff"><span>Try </span></font></font></font></div>
<div dir="ltr" align="left"><font face="Arial"><font size="2"><font color="#0000ff"><span></span><span></span></font></font></font> </div>
<div dir="ltr" align="left"><span><font size="2" face="Arial"><font color="#0000ff">SELECT DISTINCT ON(gid) gid, <font size="3" face="Times New Roman">(foo.geomval).val, COUNT((foo.geomval).val) AS
ct</font></font></font></span></div>
<div dir="ltr" align="left"><span>
<div>
<div><font color="#0000ff">FROM (SELECT globshort.rid, priogrid_land.cell,
priogrid_land.gid, ST_Intersection(globshort.rast, priogrid_land.cell) AS
geomval FROM globshort, priogrid_land) AS foo</font></div>
<div><font color="#0000ff">WHERE gid > 151000 AND gid <
151010</font></div>
<div><font color="#0000ff">GROUP BY gid, (foo.geomval).val</font></div></div>
<div><span><font color="#0000ff">ORDER BY gid, ct DESC</font></span></div>
<div><font color="#0000ff" size="2" face="Arial"></font> </div></span></div>
<div dir="ltr" lang="en-us" align="left">
<hr>
<font size="2" face="Tahoma">
<div><b>From:</b> <a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>] <b>On
Behalf Of </b>Andreas Forų Tollefsen<br></div><b>Sent:</b> Wednesday,
February 23, 2011 4:05 AM<br><b>To:</b> PostGIS Users
Discussion<br><b>Subject:</b> Re: [postgis-users] ST_Value from
Polygon<br></font><br></div>
<div>
<div></div>
<div>
<div></div>Hi. Thanks Regina and Leo,
<div>I have been testing the raster and geom intersection a bit. I guess
what i need is to use the ST_Intersection together with a max(count)
function.
<div>So my result will be the rastervalue with the highest count within each
of the grid cells.</div>
<div>However, as far as i know, there is now Max(COUNT) function in
postgresql.</div>
<div><br></div>
<div>Any idea how i can modify the below query to only return the
rastervalue within the grid cell occuring most frequently?</div>
<div>Consequently i want only one row for each gid, and the maximum occuring
rastervalue.</div>
<div><br></div>
<div>
<div>SELECT gid, (foo.geomval).val, COUNT((foo.geomval).val) AS ct</div>
<div>FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid,
ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM
globshort, priogrid_land) AS foo</div>
<div>WHERE gid > 151000 AND gid < 151010</div>
<div>GROUP BY gid, (foo.geomval).val;</div></div>
<div><br></div>
<div>gid; val; ct</div>
<div>
<div>151001;14;381</div>
<div>151001;150;9</div>
<div>151001;50;7</div>
<div>151001;140;91</div>
<div>151001;40;1</div>
<div>151001;70;2</div>
<div>151001;130;4</div>
<div>151001;200;48</div>
<div>151001;100;3</div>
<div>151001;;0</div>
<div>151001;190;1</div>
<div>151001;20;203</div>
<div>151001;11;111</div>
<div>151001;210;16</div>
<div>151001;30;105</div></div>
<div><br></div>
<div><br>
<div class="gmail_quote">2011/2/23 Paragon Corporation <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span><br>
<blockquote style="border-left:#ccc 1px solid;margin:0px 0px 0px 0.8ex;padding-left:1ex" class="gmail_quote">
<div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">Have
you looked at ST_Intersection. I'm not sure how large your grids are
so might still be a bit too slow. </font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff"><a href="http://www.postgis.org/documentation/manual-svn/RT_ST_Intersection.html" target="_blank">http://www.postgis.org/documentation/manual-svn/RT_ST_Intersection.html</a></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">Below
is a link to our slides from our North Carolina GIS meeting that may
answer some of your questions (shows some Raster examples) as well as
the 3D ones people have asked.</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff"><a href="http://www.postgis.us/presentations" target="_blank">http://www.postgis.us/presentations</a></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div><span></span><font face="Arial"><font color="#0000ff"><font size="2">Hope that helps,</font></font></font></div>
<div><span></span><font face="Arial"><font color="#0000ff"><font size="2">R<span>egina and Leo</span></font></font></font><br></div>
<div dir="ltr" lang="en-us" align="left">
<hr>
<font size="2" face="Tahoma"><b>From:</b> <a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>] <b>On
Behalf Of </b>Andreas Forų Tollefsen<br><b>Sent:</b> Tuesday, February 22,
2011 4:28 AM<br><b>To:</b> PostGIS Users Discussion<br><b>Subject:</b>
[postgis-users] ST_Value from Polygon<br></font><br></div>
<div>
<div></div>
<div>
<div></div>Hi all,
<div><br></div>
<div>I am working with a large raster dataset that i want to aggregate
into vector grids.</div>
<div>The raster dataset is a landcover dataset, and i want to find which
of the raster values are the most dominant within each of the vector grid
cells.</div>
<div><br></div>
<div>I have been looking at the ST_Value function, but this is not usable
together with the cell polygon.</div>
<div><br></div>
<div>I have written a script that gives me the raster value of the
centroid of each cell, but i want to find which raster class is the
largest.</div>
<div>Hence i need to calculate the area of each raster class within each
cell and select the largest class.</div>
<div><br></div>
<div>Any idea? So far i have only come this far:</div>
<div><br></div>
<div>
<div>DROP TABLE IF EXISTS globshortpoly;</div>
<div>SELECT priogrid_land.cell, ST_Value(rast, ST_Centroid(cell))</div>
<div>INTO globshortpoly</div>
<div>FROM priogrid_land, globshort</div>
<div>WHERE rast && priogrid_land.cell </div>
<div>LIMIT
1000</div></div></div></div></div><br>_______________________________________________<br>postgis-users
mailing list<br><a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div></div></div></div></div><br>_______________________________________________<br>postgis-users
mailing list<br><a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div></div></div></blockquote></div><br></div></div></div></div>
</blockquote></div><br></div>