[postgis-users] ST_Value from Polygon

[Andreas Forø Tollefsen]

Hi. Thanks Regina and Leo,
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.
So my result will be the rastervalue with the highest count within each of
the grid cells.
However, as far as i know, there is now Max(COUNT) function in postgresql.

Any idea how i can modify the below query to only return the rastervalue
within the grid cell occuring most frequently?
Consequently i want only one row for each gid, and the maximum occuring
rastervalue.

SELECT gid, (foo.geomval).val, COUNT((foo.geomval).val) AS ct
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
WHERE gid > 151000 AND gid < 151010
GROUP BY gid, (foo.geomval).val;

gid; val; ct
151001;14;381
151001;150;9
151001;50;7
151001;140;91
151001;40;1
151001;70;2
151001;130;4
151001;200;48
151001;100;3
151001;;0
151001;190;1
151001;20;203
151001;11;111
151001;210;16
151001;30;105


2011/2/23 Paragon Corporation <lr at pcorp.us>

>  Have you looked at ST_Intersection.  I'm not sure how large your grids
> are so might still be a bit too slow.
>
>
> http://www.postgis.org/documentation/manual-svn/RT_ST_Intersection.html
>
> 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.
>
> http://www.postgis.us/presentations
>
> Hope that helps,
> Regina and Leo
>  ------------------------------
> *From:* postgis-users-bounces at postgis.refractions.net [mailto:
> postgis-users-bounces at postgis.refractions.net] *On Behalf Of *Andreas Forø
> Tollefsen
> *Sent:* Tuesday, February 22, 2011 4:28 AM
> *To:* PostGIS Users Discussion
> *Subject:* [postgis-users] ST_Value from Polygon
>
> Hi all,
>
> I am working with a large raster dataset that i want to aggregate into
> vector grids.
> 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.
>
> I have been looking at the ST_Value function, but this is not usable
> together with the cell polygon.
>
> 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.
> Hence i need to calculate the area of each raster class within each cell
> and select the largest class.
>
> Any idea? So far i have only come this far:
>
>  DROP TABLE IF EXISTS globshortpoly;
> SELECT priogrid_land.cell, ST_Value(rast, ST_Centroid(cell))
> INTO globshortpoly
> FROM priogrid_land, globshort
> WHERE rast && priogrid_land.cell
> LIMIT 1000
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110223/3e64c3db/attachment.html>