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

[Evan Martin]

Hi Lelo,

I don't think that will help - ST_Envelope only works on geometry and I 
need this calculated on the sphere. Besides ST_Intersects() already does 
a bounding box check.

Evan

On 02.07.2013 18:18, Lelo - Luiz Rogério De Pieri wrote:
> 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 <mailto: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 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
>
>     _______________________________________________
>     postgis-users mailing list
>     postgis-users at lists.osgeo.org <mailto: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 <http://www.twitter.com/lelopieri>
> blogdolelo.wordpress.com <http://blogdolelo.wordpress.com>
>
>
> _______________________________________________
> 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/20130704/2d93f529/attachment.html>