[postgis-users] ST_Value from Polygon

[Paragon Corporation]

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




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110223/489a0f10/attachment.html>