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">andreasft@gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">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 class="h5"><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 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">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>