Great. Thank you so much. I should have noticed that these were unioned numbers. Btw. are there any way of getting the count of pixels within a polygon without actually aggregating them or union them?
Andreas 2011/2/24 Paragon Corporation <l...@pcorp.us> > Andreas, > 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. > > So > > SELECT gid, SUM(ST_Area((foo.geomval).geom))/ [put your pixel area size > here] 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 >= 139358 AND gid <= 139365 > GROUP BY gid > ORDER BY gid > > ------------------------------ > *From:* Andreas Forø Tollefsen [mailto:andrea...@gmail.com] > *Sent:* Thursday, February 24, 2011 8:33 AM > *To:* PostGIS Users Discussion > *Cc:* Paragon Corporation > > *Subject:* Re: [postgis-users] ST_Value from Polygon > > 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. > 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. > What i get is different, and it seems that the query is not providing me > with the number of pixels within the grid cell. > Any idea why this is so different? > > SELECT gid, 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 >= 139358 AND gid <= 139365 > GROUP BY gid > ORDER BY gid > > Result: > 139358;632 > 139359;1030 > 139360;912 > 139361;731 > 139362;760 > 139363;1230 > 139364;1314 > 139365;1014 > > The attached image shows the raster pixels within one cell. > > > 2011/2/24 Andreas Forø Tollefsen <andrea...@gmail.com> > >> 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
