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 <[email protected]>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 > [email protected] > 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
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
