1

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

SELECT PostGIS_Full_Version();

Result

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

Postgre

SELECT version();
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
  • Do you use a spatial index ? Can you give us INSERT INTO command to test and help you? – Vincent Bré Mar 6 at 9:40
  • Sorry, this is something that I missed. The column boundaries are Multipolygon and for that reason, I am using Multi. Vincent, the insert query is in the question. I am not familiar with spatial index and how to use it, is it help me in my case? – Ivan Abadzhiev Mar 6 at 10:18
  • A spatial index does not help here (but I highly suggest to dive into the concept and have one in place!), a regular index on name and/or level might. In this case, ST_Union is the expensive part, as it dissolves boundaries (so for Bulgaria and Romania, you would end up with a single 'borderless' Polygon); this may or may not be what you want, but if having both countries shapes as individual entities in the MultiPolygon is what you are after, use ST_Collect instead. – geozelot Mar 7 at 13:49
  • Also, you may want to help the query planner and filter for ...WHERE name IN ('Bulgaria', 'Romania') AND .... – geozelot Mar 7 at 13:53
  • ST_Union should be much faster than 10 s. How big is the regions table? If it has many records and there is no index on name that might be slow. You can post the EXPLAIN ANALYZE to show this. – dr_jts Mar 8 at 1:36

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.