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

[Evan Martin]

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 columnsand one of them is being 
used (the one on the lines). Each line only has 2 points, butthe 
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 wantthe 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 versionof 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 thereanother 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130702/29332274/attachment.html>