I am working on a project for determining potential areas. Therefore I am extracting many features e.g. roads, waterways, nature reserves which need to be buffered with a specific distance. All these features will be subtracted from another layer.
In the following, I will be using the example feature roads for showing my problem:
- Extracted roads are linestrings.
- Linestrings get buffered.
- Due to buffering, the resulting layer has many intersections.
I tried using the union function to dissolve all intersections. Due to the size of the data set, unioning takes hours if not days - and even crashes the server.
So I tried to not use union by directly subtracting the buffered roads from the final layer. Without any conditions on the query, the difference algorithm also takes way to long due to many polygons in the road table.
Then I tried to use the st_intersects method for speeding up the calculation (query can be found below). I also created a spatial index on all the tables. This indeed calculates a lot faster but the final difference does not make any sense. (See attached picture)
Instead of calculating a difference, only some features are considered. I also tried the fix geometry function (st_makevalid) with no other results.
CREATE TABLE schema.difference_result AS
SELECT st_difference(table1.geom, roads.way_buffer)
FROM schema.table1, schema.roads
WHERE st_intersects(table1.geom, roads.way_buffer);
My project contains very big data sets from all over Europe. Therefore it might be an idea to use grid processing but I have no idea how to implement it. I am not an SQL expert.
