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">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">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 class="h5">
<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">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>