[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] perform intersection on large tables
From: Rémi_Cura <remi.cura () gmail ! com>
Date: 2015-09-03 11:38:48
Message-ID: CAJvUf_s5kWRF28QfwzYHiE7anjxgoSFonm6mdqqZ23LP3h3m=Q () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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@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@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
[Attachment #5 (text/html)]
<div dir="ltr"><div class="gmail_default" \
style="font-family:monospace,monospace">Hey,<br></div><div class="gmail_default" \
style="font-family:monospace,monospace">not trying to insult anyone here, \
<br></div><div class="gmail_default" style="font-family:monospace,monospace">but you \
look like you should start with the basics,<br></div><div class="gmail_default" \
style="font-family:monospace,monospace">aka read a basic SQL tutorial for concept of \
join, then <br></div><div class="gmail_default" \
style="font-family:monospace,monospace">(join, 2 tables properly indexed ) \
<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">--do \
one time<br></div><div class="gmail_default" \
style="font-family:monospace,monospace">CREATE INDEX ON my_table_1 USING GIST(geom) ; \
<br>CREATE INDEX ON my_table_2 USING GIST(geom) ; <br><br><br></div><div \
class="gmail_default" style="font-family:monospace,monospace">--your query, should be \
very fast (seconds to minutes for usual size)<br></div><div class="gmail_default" \
style="font-family:monospace,monospace">SELECT ST_Intersection(t1.geom, t2.geom), \
--or whatever <br></div><div class="gmail_default" \
style="font-family:monospace,monospace">FROM my_table_1 AS t1, my_table_2 AS \
t2<br></div><div class="gmail_default" style="font-family:monospace,monospace">WHERE \
ST_Intersects(t1.geom, t2.geom) = TRUE ;<br></div><div class="gmail_default" \
style="font-family:monospace,monospace">--note that it could be written better, this \
is for understanding<br></div><div class="gmail_default" \
style="font-family:monospace,monospace"><br>),<br></div><div class="gmail_default" \
style="font-family:monospace,monospace"><br><br><br></div><div class="gmail_default" \
style="font-family:monospace,monospace">then you may try to increase performances and \
exploit all the CPU you have,<br> if it is indeed the bottleneck <br>(maybe IO is \
your bottleneck, maybe network , maybe RAM...).<br><br><br></div><div \
class="gmail_default" style="font-family:monospace,monospace">Cheers,<br></div><div \
class="gmail_default" style="font-family:monospace,monospace">Rémi-C <br></div><div \
class="gmail_extra"><br><div class="gmail_quote">2015-09-03 11:03 GMT+02:00 Graeme B. \
Bell <span dir="ltr"><<a href="mailto:graeme.bell@nibio.no" \
target="_blank">graeme.bell@nibio.no</a>></span>:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><span class="">> I'm trying to perform an intersection \
using two tables, one table contains<br> > a regular grid of polygon geometries \
the other table contains parcels<br> > polygons. I need to perform an \
intersection to extract the parcels as<br> > lines with a label point for each \
polygon in the grid table. My novice<br> > approach was to create new table with \
a generic geometry type and then loop<br> > through each row in the grid table to \
run the intersection against the<br> > parcels and insert the results into the \
table.<br> ><br>
> The approach works OK when dealing with a few records but fails miserably<br>
> when run against larger tables<br>
<br>
</span>Hi Travis,<br>
<br>
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.<br> It uses parallel \
programming to get an extra speedup from the multiple cores of your server.<br> \
Whatever you do, make sure you have spatial indices on both your source geo columns! \
That's essential.<br> <br>
<a href="https://github.com/gbb/fast_map_intersection" rel="noreferrer" \
target="_blank">https://github.com/gbb/fast_map_intersection</a><br> <br>
Graeme Bell<br>
<br>
_______________________________________________<br>
<div class="HOEnZb"><div class="h5">postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" \
rel="noreferrer" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br></div></div>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic