<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
You may well be right that for this particular case of testing
polygon/point intersection it's efficient to use a distance
function, since that uses the simple stabbing line point-in-polygon
algorithm (whereas ST_Intersects has much more complicated code
behind it, which may not be fully optimized).<br>
<br>
It is probably better to use ST_DWithin(geom1, geom2, 0) rather than
ST_Distance(geom1, geom2) = 0, since ST_DWithin can optimize the
case where points lie outside the query polygon. When checking
intersection, the actual distance is irrelevant (and is expensive to
compute when the point lies outside the polygon.<br>
<br>
And note that using distance functions stops being efficient when
testing things that aren't points (such as linestrings or polygons),
since this uses a brute-force O(n^2) algorithm.<br>
<br>
On 12/21/2011 9:38 AM, <a class="moz-txt-link-abbreviated" href="mailto:pcreso@pcreso.com">pcreso@pcreso.com</a> wrote:
<blockquote
cite="mid:1324489104.6217.YahooMailClassic@web160705.mail.bf1.yahoo.com"
type="cite">
<table border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="font: inherit;" valign="top">Puneet,<br>
<br>
Chopping polygons is pretty simple, with a grid &
st_intersection(), but you can certainly generalise
polygons to reduce the number of vertices & size of
objects to de-toast... beware however that if you do this
then you are actually moving the polygon boundary, &
therefore a point very near a boundary may be inside the
original country polygon but outside the
generalised/simplified one.<br>
<br>
You can address this by simplifying a buffer of the
polygons, with the buffer very slightly larger than the
simplify distance, so that every simplified version fully
contains the original, but you will also have to check
against the original polygons to confirm the point is
genuinely inside the original.<br>
<br>
As an alternative approach, you might also try selecting
points where the distance from a polygon is zero, as the
ST_distance uses stabbing line algorithm, and may be
faster. The distance will be non-zero only for points
outside the polygon. <br>
<br>
Cheers,<br>
<br>
Brent Wood<br>
<br>
<blockquote style="border-left: 2px solid rgb(16, 16,
255); margin-left: 5px; padding-left: 5px;">
<div class="plainMail">On 21/12/2011, at 12:06 PM, Mr.
Puneet Kishor wrote:<br>
<br>
> <br>
> On Dec 20, 2011, at 9:48 PM, Martin Davis wrote:<br>
> <br>
>> For more detail check out this thread on the
same issue:<br>
>> <br>
>> <a moz-do-not-send="true"
href="http://postgis.refractions.net/pipermail/postgis-users/2011-November/031345.html"
target="_blank">http://postgis.refractions.net/pipermail/postgis-users/2011-November/031345.html</a><br>
> <br>
> <br>
> Thanks. Chopping up my coverage into hundreds of
small regions is the last avenue I want to try. Going
by the text of that email, it seems that "few, large,
regions with many vertices (may be) the problem." I
will try generalizing my continents so that I have
"few, large regions with *very few* vertices" and see
if that speeds up the SELECTs.<br>
> <br>
> <br>
>> <br>
>> On 12/20/2011 5:28 PM, Puneet Kishor wrote:<br>
>>> On Dec 20, 2011, at 7:21 PM, Paul Ramsey
wrote:<br>
>>> <br>
>>>> Chop up the continents into smaller
pieces.<br>
>>>> <br>
>>> <br>
>>> hmmm... I am not sure I understand the
above. And then what? UNION each smaller piece query?<br>
>>> <br>
>>> <br>
>>>> On Tue, Dec 20, 2011 at 4:35 PM,
Puneet Kishor<<a moz-do-not-send="true"
ymailto="mailto:punk.kish@gmail.com"
href="/mc/compose?to=punk.kish@gmail.com">punk.kish@gmail.com</a>>
wrote:<br>
>>>>> This is probably a really basic
question... my ST_Within or ST_Intersects selecting
points in a continent are way too slow (both take
upward of 200 secs).<br>
>>>>> <br>
>>>>> SELECT Count(c_id)<br>
>>>>> FROM c, continents n<br>
>>>>> WHERE
ST_Intersects(c.the_geom, n.the_geom) AND<br>
>>>>> n.continent =
'North America';<br>
>>>>> <br>
>>>>> <br>
>>>>> Both tables have gist indexes on
the geometries. The above query has the following plan<br>
>>>>> <br>
>>>>> "Aggregate (cost=9.66..9.67
rows=1 width=4)"<br>
>>>>> " -> Nested Loop
(cost=0.00..9.66 rows=1 width=4)"<br>
>>>>> " Join Filter:
_st_intersects(c.the_geom, n.the_geom)"<br>
>>>>> " -> Seq Scan on
continents n (cost=0.00..1.10 rows=1 width=32)"<br>
>>>>> " Filter:
((continent)::text = 'North America'::text)"<br>
>>>>> " -> Index Scan using
pbdb__collections_the_geom on collections c
(cost=0.00..8.30 rows=1 width=104)"<br>
>>>>> " Index Cond:
(c.the_geom&& n.the_geom)"<br>
>>>>> <br>
>>>>> The table c has approx 120K rows,
and the continents table has 8 rows.Suggestions on how
I can improve this? Yes, the computer is otherwise
very swift and modern.<br>
>>>>> <br>
>>>>> <br>
>>>>> <br>
>>>>> --<br>
>>>>> Puneet Kishor<br>
>>>
_______________________________________________<br>
>>> postgis-users mailing list<br>
>>> <a moz-do-not-send="true"
ymailto="mailto:postgis-users@postgis.refractions.net"
href="/mc/compose?to=postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
>>> <a moz-do-not-send="true"
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
>>> <br>
>>> <br>
>>> -----<br>
>>> No virus found in this message.<br>
>>> Checked by AVG - <a class="moz-txt-link-abbreviated" href="http://www.avg.com">www.avg.com</a><br>
>>> Version: 2012.0.1890 / Virus Database:
2109/4692 - Release Date: 12/20/11<br>
>>> <br>
>>> <br>
>>
_______________________________________________<br>
>> postgis-users mailing list<br>
>> <a moz-do-not-send="true"
ymailto="mailto:postgis-users@postgis.refractions.net"
href="/mc/compose?to=postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
>> <a moz-do-not-send="true"
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
> <br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a moz-do-not-send="true"
ymailto="mailto:postgis-users@postgis.refractions.net"
href="/mc/compose?to=postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
> <a moz-do-not-send="true"
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
ymailto="mailto:postgis-users@postgis.refractions.net"
href="/mc/compose?to=postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a moz-do-not-send="true"
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div>
</blockquote>
</td>
</tr>
</tbody>
</table>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<p class="" avgcert""="" color="#000000" align="left">No virus
found in this message.<br>
Checked by AVG - <a moz-do-not-send="true"
href="http://www.avg.com">www.avg.com</a><br>
Version: 2012.0.1890 / Virus Database: 2109/4694 - Release Date:
12/21/11</p>
</blockquote>
</body>
</html>