<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.7600.16722"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=671382513-23022011><FONT color=#0000ff
size=2 face=Arial>Andrea,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=671382513-23022011><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
class=671382513-23022011>Try </SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT size=2><FONT color=#0000ff><SPAN
class=671382513-23022011></SPAN><SPAN
class=671382513-23022011></SPAN></FONT></FONT></FONT> </DIV>
<DIV dir=ltr align=left><SPAN class=671382513-23022011><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 class=671382513-23022011>
<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><SPAN class=671382513-23022011><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 class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Andreas Forø Tollefsen<BR><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>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
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 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></BODY></HTML>