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

  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;

enter image description here

  • Welcome to GIS.se! I reformatted your question because your map appeared in the middle of your code. Could you edit your question to explain why you are creating these buffers? – raphael May 10 '17 at 14:01
  • try this bounding box query for your where statement : WHERE m.geom && a.geom – ziggy May 10 '17 at 14:07
  • 1
    Please do not refer to a database table as a "shapefile". These are very different formats. – Vince May 10 '17 at 14:10
  • thanks @ziggy I am testing this now. Can you explain the bounding box query, why it should be faster? – beatricze May 10 '17 at 14:26
  • The query with WHERE m.geom && a.geom took 2:10 hours, so longer than with ST_Intersects – beatricze May 11 '17 at 6:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.