I've been out of the loop for a bit, but try this:<div><br></div><div><span class="Apple-style-span" style> SELECT Count(c_id)</span><br style><span class="Apple-style-span" style> FROM c, continents n</span><br style>
<span class="Apple-style-span" style> WHERE c.the_geom && n.the_geom AND</span></div><div><span class="Apple-style-span" style> ST_Intersects(c.the_geom, n.the_geom) AND</span><br style><span class="Apple-style-span" style> n.continent = 'North America';</span></div>
<div><font class="Apple-style-span" color="#222222" face="arial, sans-serif"><br></font></div><div><font class="Apple-style-span" color="#222222" face="arial, sans-serif">The key line being:</font></div><div><span class="Apple-style-span" style>c.the_geom && n.the_geom</span></div>
<div><font class="Apple-style-span" color="#222222" face="arial, sans-serif"><br></font></div><div><font class="Apple-style-span" color="#222222" face="arial, sans-serif">Check out item 7.7: <a href="http://postgis.refractions.net/documentation/manual-1.5/reference.html">http://postgis.refractions.net/documentation/manual-1.5/reference.html</a></font></div>
<div><font class="Apple-style-span" color="#222222" face="arial, sans-serif"><br></font></div><div><font class="Apple-style-span" color="#222222" face="arial, sans-serif">The && uses the bounding boxes to check if they overlap. This is a very quick operation that might eliminate the need to further examine if two geoms are intersecting. </font></div>
<div><font class="Apple-style-span" color="#222222" face="arial, sans-serif"><br></font></div><div><font class="Apple-style-span" color="#222222" face="arial, sans-serif">HTH,</font></div><div><font class="Apple-style-span" color="#222222" face="arial, sans-serif">Brian</font></div>
<div><br><div class="gmail_quote">On Tue, Dec 20, 2011 at 8:28 PM, Puneet Kishor <span dir="ltr"><<a href="mailto:punk.kish@gmail.com">punk.kish@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="im"><br>
On Dec 20, 2011, at 7:21 PM, Paul Ramsey wrote:<br>
<br>
> Chop up the continents into smaller pieces.<br>
><br>
<br>
<br>
</div>hmmm... I am not sure I understand the above. And then what? UNION each smaller piece query?<br>
<div class="HOEnZb"><div class="h5"><br>
<br>
> On Tue, Dec 20, 2011 at 4:35 PM, Puneet Kishor <<a href="mailto: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>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br></div>