Postgis “esri union” on a “unlimited” sized data set.


“Esri union” is a function that is often used by Arcgis users and in many cases an important reason why people still need Arcgis software, even when the data area available in Postgis. For NIBIO the Arcgis software was working ok with smaller datasets, but not bigger datasets.  So we asked the users why not run this is in Postgis ? The answer was “The function is not available in Postgis, can you make one that works ?”


In this presentation we will go into how to manage

  •     Content based grid 
  •     Mill of rows and billions of points
  •     Big single polygons (1.5 mill points)
  •     Parallel processing  
  •     Remove grid lines from the result when done

We have tested this function on quite big tables, se tables se below.

Table         num_points     num_polygons     table_size

Table1     40435700     1088614         637_MB

Table1     933145431     7924019        10127_MB

Result         2042294001     43668256         30_GB


The time used to get the result table was 152 minutes. This means that postgis are able to create almost 5000 polygons pr. second and more 220000 points pr. second. We are not able to run this kinds of tests in our Arcgis environment, but will compare some smaller datasets.


We hope to run some tests on bigger data sets to hopefully find out when will a single Postgis server stop to scale. My impression is that Postgis scales very good on a single database server.  


What caused us most problems was not related to number of rows in a table, but very big single polygons. We have a polygon with 1471333 and 52079 interior rings. The problem with this polygon was that even very simple intersections  took more than 15 minutes . We will here show how we can reduce this time to less than 4 seconds. 


And the end we will do short ref Postgis Topology and show why topology could help us in cases like this.


The Esri Union Postgis code is available at  and check out the wiki and tests where there is some more info about this function. 


For info about the Esri Union function check out


Session details
Speaker(s): Session Type: Experience level:
Track: Tags:
Schedule info
Session Time Slot(s):
Tuesday, May 3, 2016 - 16:15 to 16:50