<div dir="ltr">Hi Evan,<div><br></div><div style>What about instead of verify the intersection with Simplified polygon, verify the intersection with the BBox ?</div><div style><b class="" style="font-size:13px;color:rgb(33,55,128);font-family:monospace">ST_Envelope </b><a href="http://postgis.refractions.net/documentation/manual-1.4/ST_Envelope.html">http://postgis.refractions.net/documentation/manual-1.4/ST_Envelope.html</a><br>
</div><div style>I've been using this strategy on my system but using geotools and gives me nice results.</div><div style><br></div><div style>Good luck</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">
On Tue, Jul 2, 2013 at 10:39 AM, Evan Martin <span dir="ltr"><<a href="mailto:postgresql@realityexists.net" target="_blank">postgresql@realityexists.net</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div text="#000000" bgcolor="#FFFFFF">
<font size="+1">Hi<font size="+1">,<br>
<br>
<font size="+1">I have tables of ~<font size="+1">25,000
polygons <font size="+1">and <font size="+1">~</font>80,000
li<font size="+1">nes and I want to find <font size="+1">which
lines intersect which polygons u<font size="+1">sing
PostGIS 2.1</font>. </font></font>Both
are g<font size="+1">eograp<font size="+1">hies an<font size="+1">d can span the dateline. Doing this the simple
way <font size="+1">using ST_Inters<font size="+1">ect<font size="+1">s(<font size="+1">geog, geo<font size="+1">g)
takes about 3 hours on my machine and I<font size="+1">'<font size="+1">d to see if there's
a way to speed this up.<br>
<br>
<font size="+1">I already have indexes on
the geography columns<font size="+1"> and
one of them is being used (the one on
the lines). Each line only has 2 points<font size="+1">, but<font size="+1"> the
polygons have anywh<font size="+1">ere
from 4 to <font size="+1">20,000
points and some of them are very
<font size="+1">large</font>. It
would be OK to </font></font>miss
some of the smaller intersections (ie. where
the two only just barely intersect), but I
wouldn't want<font size="+1"> the query to
return false positives. In fact, ideally,
I'd like to find only the lines </font>that
"substantially" intersect a polygon, eg. at
least x km o<font size="+1">r x<font size="+1">% of the line is in the
polygon, but finding any intersections
at all would be a start.<br>
<br>
<font size="+1">One trick I tried is
ST_SimplifyPreserveTopology. I used
that to create simplified version<font size="+1"> of the polygons (at least
those <font size="+1">that don't
span the datelin<font size="+1">e)
and check those first, then if
they intersect then <font size="+1">check the real
polygons. Th<font size="+1">is
seems to work<font size="+1">,
but the performance gains
are ma<font size="+1">rginal
compared to the <font size="+1">simple appr<font size="+1">oach</font></font>.<br>
<br>
<font size="+1">Is there<font size="+1"> another
trick I can use to <font size="+1">do this
faster? I know
ST_Intersects()
inter<font size="+1">nally
calls ST_D<font size="+1">istance(),
which
calculates the
dis<font size="+1">tance
to a fraction
of a metre. I
don't need th<font size="+1">at
kind of
precision</font></font></font>,
so surely there's some
"shorc<font size="+1">ut"
to be found?<br>
<br>
<font size="+1">T<font size="+1">hanks,<br>
<br>
<font size="+1">Evan</font><br>
</font></font>
</font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></div>
<br>_______________________________________________<br>
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" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br><br clear="all"><div><br></div>-- <br><div dir="ltr"><div>Rogério De Pieri (Lelo)<br></div>MBA em Gerenciamento de Projetos - FGV<br><div><div>SCJP 5<br><div><div>Buscando melhorar a cada dia<br>
Áudio, Hardware & Software</div></div><div><a href="http://www.twitter.com/lelopieri" target="_blank">www.twitter.com/lelopieri</a></div><div><a href="http://blogdolelo.wordpress.com" target="_blank">blogdolelo.wordpress.com</a></div>
</div></div></div>
</div>