<!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=902401014-24022011><FONT color=#0000ff
size=2 face=Arial>Andreas,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=902401014-24022011><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 class=902401014-24022011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=902401014-24022011><FONT color=#0000ff
size=2 face=Arial>So </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=902401014-24022011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=902401014-24022011>
<DIV>SELECT gid, <SPAN class=902401014-24022011>SUM</SPAN>(<SPAN
class=902401014-24022011>ST_Area(</SPAN>(foo.geomval).<SPAN
class=902401014-24022011>geom)</SPAN>)<SPAN class=902401014-24022011>/ [put your
pixel area size here] </SPAN> 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></SPAN></DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> Andreas Forø Tollefsen
[mailto:andreasft@gmail.com] <BR><B>Sent:</B> Thursday, February 24, 2011 8:33
AM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Cc:</B> Paragon
Corporation<BR><B>Subject:</B> Re: [postgis-users] ST_Value from
Polygon<BR></FONT><BR></DIV>
<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">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 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
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></BODY></HTML>