I have two tables that I join in my existing workflow. One of these includes flood events (was a shapefile that has been imported to PostgreSQL), the other one is a grid that holds the amount of precipitation for each day and each grid. It is a grid spawned across the world, each pattern one degree high and one degree wide (64800 in total). In my workflow, I combine the flood event with the summed up precipitation in the affected area. And this is how I do it: At first I calculate the centroid of each pattern of the grid (st_centroid).
Then I check if the centroid is within an event of the flood-polygon, using the st_within function:
CREATE TABLE within
SELECT cell_id, (id of the grid)
geometry AS cell_geom,
gid, (id of the flood event)
geom AS dfo_geom
CREATE INDEX within_gix ON within USING GIST (dfo_geom);
In the next step, I combine the two libraries, based on the cell_id that I used in the first step.
CREATE TABLE libjoin AS
gid AS dfo_id,
FROM within, precipitation_lib
WHERE within.cell_id = precipitation_lib.cell AND
(precipitation_lib.date >= within.started_at AND precipitation_lib.date <= within.finished_at)
AND precipitation > 0
ORDER BY gid, cell_id, date;
In the final two steps, I calculate first the average of the daily precipitation
CREATE TABLE libjoin_avg AS
AVG(precipitation) OVER (PARTITION BY date ORDER BY dfo_id)
And then I sum it up by adding the averages of each day within the flood event
CREATE TABLE libjoin_sum AS
SUM(avg) OVER (PARTITION BY started_at ORDER BY dfo_id)
This workflow is working but I would like to optimise it. My grid is not very precise and, as I am using the centroid of a pattern, I miss pretty many flood events because the centroid of a pattern is not always within the flood event. Another problem is, that once the centroid is within the flood event, the pattern is used for the calculation, no matter how much of its area is actually covered. You can see that in the following image, especially on the upper right side. These patterns are used for calculation because the centroid is within the flood event geometry.
This image shows my different layers, imported in QGIS. The green background is the actual grid and the highlighted blue ones are those with the centroid within the flood event. So they are used for the calculation of the summarised precipitation of the flood event shown in light blue on top.
Do you have any ideas of how I could optimise my workflow? I would like to weight the patterns by the amount of area that is covered by the flood event polygon and use that weighted amount for the calculation of the overall precipitation for each flood event but I don't know how to do that.
I am open for other solutions, not based on the centroid of the pattern, as well.