I've been asked to automate the calculation of cumulative polygon areas within a basins shapefile. The calculation will take the area of the upstream basin and add it to the area of the downstream basin, and continue adding to that cumulative value until reaching the farthest downstream basin. It also needs to take into account the areas from two or more stream basins due to stream confluences. This leads to two problems:
- How to add the value from one row to the value in another row (assuming with an update cursor).
- How to calculate the cumulative area moving from upstream to downstream.
The screenshot below shows the test shapefile I was given with the cumulative square miles manually calculated. As far as I know, the Basin_ID should always be unique. The table has some sample rows selected for Stream04 and Squaw Creek. For this example, BASIN17 needs to be the cumulative square miles of Basins 3 and 8 from Stream04, and Basins 4, 11, 14, 16, and 17 on Squaw Creek. In that order.
The "US" in Fields
US_4 stands for upstream (basin). "DS" is downstream basin. If the
US_1 field is blank, then that basin is the most upstream basin on the stream and the starting point for the cumulative area calculation. I think a field may need to be added to flag this basin as the upstream starting point, and is used in the example below (but isn't in the table).
Initially it seems like it should be:
shapefile = path fields = ["Basin_ID","Cum_SqMi","AreaSqMi","US_1","US_2","US_Start"] with arcpy.da.UpdateCursor(shapefile,fields) as uc: for row in uc: if row == " ": row = 1 # calc as 1 if true else: row = 0
Now I run out of logic. For the Stream04 example, we know that BASIN3 is the upstream most point. To calculate the cumulative area for BASIN8, it's simple math: BASIN3
AreaSqMi + BASIN8
AreaSqMi. These are separate rows, and as far as I know, this can't be done with a cursor since it's only looking at one row at a time.
So, what needs to be done to add the value from a row that meets a condition to the value in another row? What about a dictionary or an array? I'm not very familiar with those, hence the reason why I keep going back to the cursor. I know what's below won't work, but it shows what should be done. It also doesn't handle the multiple starting basins that will equal 1, so I may need to make that equal to the
if row == 1: startBasin = row startBasinValue = row if row == startBasin: row = row + startBasinValue
And then to how to continue with the calculation based on if the upstream cumulative area has been calculated yet? It seems like it would be possible to cascade the calculations from the starting point. I could manually add a field and calculate a sequential order for calculating the rows, but there are around 50 shapefiles that would have to be done for, and the user wants it as automated as possible.
Am I in over my head? Going about it entirely wrong? Should they stick to calculating the areas manually in a spreadsheet?