[postgis-users] ST_Value from Polygon

[Andreas Forø Tollefsen]

Thanks!
That solved it.

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.

Andreas


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

>  Andrea,
>
> Try
>
> SELECT DISTINCT ON(gid) 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
> ORDER BY gid, ct DESC
>
>  ------------------------------
> *From:* postgis-users-bounces at postgis.refractions.net [mailto:
> postgis-users-bounces at postgis.refractions.net] *On Behalf Of *Andreas Forø
> Tollefsen
> *Sent:* Wednesday, February 23, 2011 4:05 AM
> *To:* PostGIS Users Discussion
> *Subject:* Re: [postgis-users] ST_Value from Polygon
>
> 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
>>
>>
>
> _______________________________________________
> 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/20110224/0a14ce0e/attachment.html>