0

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;

Here is the output: enter image description here

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?

2
  • Have you ensured there is a single entry in cropcodes for ca / 220?
    – JGH
    Feb 28, 2019 at 18:28
  • @JGH, that was it! My source dataset contained a few other dupes as well. After fixing that, my query runs fine. If you want to answer, I'll accept it . . .
    – pdavis
    Feb 28, 2019 at 18:47

1 Answer 1

1

The inner query (t3) seems fine as the result is grouped by value/country. You can run it alone and ensure there is no duplicate.

The output table shows complete records for the duplicates, meaning there is a data row in both t3 and t4. The probable issue is therefore that there are duplicates in t4, which could be identified by

select value, country, count(*)
from cropcodes 
group by value, country
having count(*) > 1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.