I am new to the endless world of GIS. I am working on the project where I need to create a "Blocks" which are a combination of some countries. e.g: I have Block "Balkans" where Bulgaria, Serbia and Romania are added. Each user with a particular role will be able to create blocks. The blocks can be from 2 to 20 countries. The problem that I have is with the performance when I try to combine Bulgaria and Romania cost me 10 seconds.
INSERT INTO regions (boundaries) VALUES ( (Select ST_MULTI(ST_UNION(boundaries)) FROM regions WHERE (name='Bulgaria' OR name='Romania') AND level='country') );
This is the query for inserting both countries. Here is a Pastebin with the Multipolygon for both countries https://pastebin.com/Hq6EhZd1 if you need to run it and test it.
I tried to add it as BINARY but the query took 10 seconds as well.
INSERT INTO regions (boundaries) VALUES ( (SELECT ST_AsEWKB( ST_MULTI( (SELECT ST_UNION(boundaries) from regions WHERE (name='Bulgaria' OR name='Romania') AND level='country') ) ) ) );
I am using this version of PostgreSQL and PostGIS
POSTGIS="2.4.4 r16526" PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.1.4, released 2017/06/23" LIBXML="2.9.1" LIBJSON="0.12" LIBPROTOBUF="1.3.0" RASTER
PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
Analyze result is
Insert on regions (cost=209.86..209.88 rows=1 width=684) (actual time=4.634..4.634 rows=0 loops=1) InitPlan 1 (returns $0) -> Aggregate (cost=209.84..209.86 rows=1 width=32) (actual time=3.883..3.884 rows=1 loops=1) -> Seq Scan on regions regions_1 (cost=0.00..209.84 rows=1 width=22613) (actual time=0.078..0.251 rows=2 loops=1) Filter: ((level = 'country'::text) AND ((name = 'Bulgaria'::text) OR (name = 'Romania'::text))) Rows Removed by Filter: 591 -> Result (cost=0.00..0.02 rows=1 width=684) (actual time=3.943..3.944 rows=1 loops=1) Planning time: 0.400 ms Trigger refresh_site_regions_on_region_change: time=412200.845 calls=1 Execution time: 412210.485 ms