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 https://github.com/larsop/esri_union/ 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 http://resources.esri.com/help/9.3/arcgisengine/java/gp_toolref/analysis_tools/how_union_analysis_works.htm