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) started_at, finished_at, geom AS dfo_geom FROM precipitation_grid, flood_event_lib WHERE ST_Within(precipitation_grid.centroid, flood_event_lib.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 SELECT cell_id, gid AS dfo_id, started_at, finished_at, date, precipitation, flood_lib_geom, cell_geom FROM within, precipitation_lib WHERE within.cell_id = precipitation_lib.cell AND(precipitation_lib.date >= within.started_at AND precipitation_lib.date 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 SELECT *, AVG(precipitation) OVER (PARTITION BY date ORDER BY dfo_id) FROM libjoinAnd then I sum it up by adding the averages of each day within the flood event
CREATE TABLE libjoin_sum AS SELECT *, SUM(avg) OVER (PARTITION BY started_at ORDER BY dfo_id) FROM libjoin_avgThis 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 precipitaion 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 amout for the calculation of the overall precipitation for each flood event but I dont know how to do that. Any help is much appreciated!I am open for other solutions, not based on the centroid of the pattern, aswell. Thank you very much in advance!
أكثر...
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) started_at, finished_at, geom AS dfo_geom FROM precipitation_grid, flood_event_lib WHERE ST_Within(precipitation_grid.centroid, flood_event_lib.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 SELECT cell_id, gid AS dfo_id, started_at, finished_at, date, precipitation, flood_lib_geom, cell_geom FROM within, precipitation_lib WHERE within.cell_id = precipitation_lib.cell AND(precipitation_lib.date >= within.started_at AND precipitation_lib.date 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 SELECT *, AVG(precipitation) OVER (PARTITION BY date ORDER BY dfo_id) FROM libjoinAnd then I sum it up by adding the averages of each day within the flood event
CREATE TABLE libjoin_sum AS SELECT *, SUM(avg) OVER (PARTITION BY started_at ORDER BY dfo_id) FROM libjoin_avgThis 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 precipitaion 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 amout for the calculation of the overall precipitation for each flood event but I dont know how to do that. Any help is much appreciated!I am open for other solutions, not based on the centroid of the pattern, aswell. Thank you very much in advance!
أكثر...