[postgis-users] Tricks to find polygon/line intersection faster

[Lelo - Luiz Rogério De Pieri]

Hi Evan,

What about instead of verify the intersection with Simplified polygon,
verify the intersection with the BBox ?
*ST_Envelope *
http://postgis.refractions.net/documentation/manual-1.4/ST_Envelope.html
I've been using this strategy on my system but using geotools and gives me
nice results.

Good luck


On Tue, Jul 2, 2013 at 10:39 AM, Evan Martin
<postgresql at realityexists.net>wrote:

>  Hi,
>
> I have tables of ~25,000 polygons and ~80,000 lines and I want to find which
> lines intersect which polygons using PostGIS 2.1. Both are geographies and
> can span the dateline. Doing this the simple way using ST_Intersects(geog,
> geog) takes about 3 hours on my machine and I'd to see if there's a way
> to speed this up.
>
> I already have indexes on the geography columns and one of them is being
> used (the one on the lines). Each line only has 2 points, but the
> polygons have anywhere from 4 to 20,000 points and some of them are very
> large. It would be OK to miss some of the smaller intersections (ie.
> where the two only just barely intersect), but I wouldn't want the query
> to return false positives. In fact, ideally, I'd like to find only the
> lines that "substantially" intersect a polygon, eg. at least x km or x%
> of the line is in the polygon, but finding any intersections at all would
> be a start.
>
> One trick I tried is ST_SimplifyPreserveTopology. I used that to create
> simplified version of the polygons (at least those that don't span the
> dateline) and check those first, then if they intersect then check the
> real polygons. This seems to work, but the performance gains are marginal
> compared to the simple approach.
>
> Is there another trick I can use to do this faster? I know
> ST_Intersects() internally calls ST_Distance(), which calculates the distance
> to a fraction of a metre. I don't need that kind of precision, so surely
> there's some "shorcut" to be found?
>
> Thanks,
>
> Evan
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>


-- 
Rogério De Pieri  (Lelo)
MBA em Gerenciamento de Projetos - FGV
SCJP 5
Buscando melhorar a cada dia
Áudio, Hardware & Software
www.twitter.com/lelopieri
blogdolelo.wordpress.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130702/26993a9b/attachment.html>