[postgis-users] perform intersection on large tables

[Rémi Cura]

Hey,
not trying to insult anyone here,
but you look like you should start with the basics,
aka read a basic SQL tutorial for concept of join, then
(join, 2 tables properly indexed )

--do one time
CREATE INDEX ON my_table_1 USING GIST(geom) ;
CREATE INDEX ON my_table_2 USING GIST(geom) ;


--your query, should be very fast (seconds to minutes for usual size)
SELECT ST_Intersection(t1.geom, t2.geom), --or whatever
FROM my_table_1 AS t1, my_table_2 AS t2
WHERE ST_Intersects(t1.geom, t2.geom) = TRUE ;
--note that it could be written better, this is for understanding

),



then you may try to increase performances and exploit all the CPU you have,
 if it is indeed the bottleneck
(maybe IO is your bottleneck, maybe network , maybe RAM...).


Cheers,
Rémi-C

2015-09-03 11:03 GMT+02:00 Graeme B. Bell <graeme.bell at nibio.no>:

> > 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
>
> Hi Travis,
>
> 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.
>
> https://github.com/gbb/fast_map_intersection
>
> Graeme Bell
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150903/f6d6a5c5/attachment.html>