I have a table of single polygons (table rast_poly) derived from a raster and a table of polygons that represents buffered points (table pt_buff). The raster polygon geometry has been reduced by using an ST_Intersection() operation with the buffered points, so the geom is clipped to those boundaries. Each polygon from the raster contains an integer value that corresponds to a crop code. I want to sum the areas for each crop type within each buffered point polygon. I have to join the cropcodes table to the first query on 'value' and 'country' because the value-category pairs are unique to each country. I am almost there with the following code:
select t3.id, t3.country, t3.val, t3.km2::numeric(10,2), t4.category
from (select t2.id, t1.val, sum(st_area(t1.geom::geography)/1000000) as km2, t1.country
from rast_poly t1, pt_buff t2
where st_intersects(t1.geom, t2.geom) and t2.id = '17DYR07'
group by t2.id, val, t1.country
) t3
left join cropcodes t4 on t3.val = t4.value and t3.country = t4.country;
I've checked the results against both the dumped polygons from the raster, and the original raster itself. Everything is correct except the two duplicate rows at the bottom of the output.
How can I fix this?
cropcodes
for ca / 220?