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 <l...@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-boun...@postgis.refractions.net [mailto: > postgis-users-boun...@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 <l...@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-boun...@postgis.refractions.net [mailto: >> postgis-users-boun...@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@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> > > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > >
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users