[postgis-users] perform intersection on large tables
Graeme B. Bell
graeme.bell at nibio.no
Thu Sep 3 02:03:19 PDT 2015
> I'm trying to perform an intersection using two tables, one table contains
> a regular grid of polygon geometries the other table contains parcels
> polygons. I need to perform an intersection to extract the parcels as
> lines with a label point for each polygon in the grid table. My novice
> approach was to create new table with a generic geometry type and then loop
> through each row in the grid table to run the intersection against the
> parcels and insert the results into the table.
> The approach works OK when dealing with a few records but fails miserably
> when run against larger tables
Here is a fast implementation of a GIS intersection which will perform well even on huge tables, which you can simplify or modify as you like.
It uses parallel programming to get an extra speedup from the multiple cores of your server.
Whatever you do, make sure you have spatial indices on both your source geo columns! That's essential.
More information about the postgis-users