I am developing Python code to explore suitable sites for wind power development in some countries using spatial data and Postgresql/PostGIS.
For that reason I have tried to process a buffer around buildings using different buffer values ( 400m - 1000 m) stored in the another table called adminlvl2b. I used OSM data for Germany and Portugal to test the performance. In case of Portugal the query for one region lasts 1.30 minutes, but for 20 regions the process lasts 1 hours and 40 minutes. In case of Germany I am not able to process the buffer due to higher number of features stored in the memory while processing ST_Union. So I got an error.
I also tried ST_Collect instead of ST_Union, but it took also 1hour 40 minutes and produced only 3 buffered building in 3 regions although it supposed to produce 20 different buffered buildings per region. I am aware that St_intersection requires much of the total time.
So my question is, how I can rewrite the query to speed up the process and it will work regardless the size of the building data.
I am working with
PostgreSQL 9.5.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
Here is the query used and a map representing the results for Portugal:
DROP TABLE IF EXISTS buffered.buildings_residential; CREATE TABLE buffered.buildings_residential (pkey serial, geom geometry); INSERT INTO buffered.buildings_residential (geom) SELECT ST_Union(ST_Intersection(ST_Buffer(m.geom, a.buffer_m), a.geom)) FROM selected.buildings_residential as m, selected.adminlvl2b as a WHERE ST_Intersects(m.geom, a.geom) --and a.pkey=19 -- in case of one region GROUP BY a.pkey; ALTER TABLE buffered.buildings_residential ADD CONSTRAINT buildings_residential_pkey PRIMARY KEY (pkey); CREATE INDEX ON buffered.buildings_residential USING gist (geom); ANALYZE buffered.buildings_residential;