<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body 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></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></font></font></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></font></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>
</body>
</html>