[postgis-users] A function for “Esri union” union on big tables on github.

[Birgit Laggner]

Hi Lars,

awesome, thank you. I will surely test your code at the next occasion.

Regards,

Birgit

Am 10.02.2016 um 09:11 schrieb Lars Aksel Opsahl:
> Hi
>
>
> There has been different mails about this topic lately. We have now have added the code we use to Github and hopefully somebody can pick up some ideas or just use this function as it is.
>
>
> The basic idea is that you call this function and with 2 tables as input. The following happens in the function
>
>    *   Builds up a content based grid
>
>    *   Computes the result
>
>    *   Removes the grid lines from the result
>
>    *   Returns a table name with the union of this two tables. For areas that intersect you get attributes from both tables and for areas that only exits in one of the tables you only get attributes from one table.
>
>
> The code is found at https://github.com/larsop/esri_union
>
>
> About performance. The code added now runs in a single thread, but we have a slightly modified code that runs in parallel using “Gnu parallel” and then we can increase the performance many times depending on how many CPU you have on your server. Here is an example running with 20 threads.
>
>
> num points num polygons table size
>
> Table 1 40435700 1088614 637 MB
>
> Table 1 933145431 7924019 10127 MB
>
> Result table 2042294001 43668256 30 GB
>
>
> The time used to do the intersection was 152 minutes. I will add the parallel code later when I have time to make the code ready.
>
>
> Lars
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users