<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">Hi Lelo,<br>
<br>
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.<br>
<br>
Evan<br>
<br>
On 02.07.2013 18:18, Lelo - Luiz Rogério De Pieri wrote:<br>
</div>
<blockquote
cite="mid:CAFVxN6MRzXaGL_i8+DkEPKS_dY3Mu9qNgOinQ+ziMYMzVRJ0dQ@mail.gmail.com"
type="cite">
<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
moz-do-not-send="true"
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
moz-do-not-send="true"
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 moz-do-not-send="true"
href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a moz-do-not-send="true"
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 moz-do-not-send="true"
href="http://www.twitter.com/lelopieri"
target="_blank">www.twitter.com/lelopieri</a></div>
<div><a moz-do-not-send="true"
href="http://blogdolelo.wordpress.com" target="_blank">blogdolelo.wordpress.com</a></div>
</div>
</div>
</div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
</body>
</html>