I've been out of the loop for a bit, but try this:
SELECT Count(c_id)
FROM c, continents n
WHERE c.the_geom && n.the_geom AND
ST_Intersects(c.the_geom, n.the_geom) AND
n.continent = 'North America';
The key line being:
c.the_geom && n.the_geom
Check out item 7.7:
http://postgis.refractions.net/documentation/manual-1.5/reference.html
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.
HTH,
Brian
On Tue, Dec 20, 2011 at 8:28 PM, Puneet Kishor <punk.k...@gmail.com> wrote:
>
> On Dec 20, 2011, at 7:21 PM, Paul Ramsey wrote:
>
> > Chop up the continents into smaller pieces.
> >
>
>
> hmmm... I am not sure I understand the above. And then what? UNION each
> smaller piece query?
>
>
> > On Tue, Dec 20, 2011 at 4:35 PM, Puneet Kishor <punk.k...@gmail.com>
> wrote:
> >> 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).
> >>
> >> SELECT Count(c_id)
> >> FROM c, continents n
> >> WHERE ST_Intersects(c.the_geom, n.the_geom) AND
> >> n.continent = 'North America';
> >>
> >>
> >> Both tables have gist indexes on the geometries. The above query has
> the following plan
> >>
> >> "Aggregate (cost=9.66..9.67 rows=1 width=4)"
> >> " -> Nested Loop (cost=0.00..9.66 rows=1 width=4)"
> >> " Join Filter: _st_intersects(c.the_geom, n.the_geom)"
> >> " -> Seq Scan on continents n (cost=0.00..1.10 rows=1
> width=32)"
> >> " Filter: ((continent)::text = 'North America'::text)"
> >> " -> Index Scan using pbdb__collections_the_geom on collections
> c (cost=0.00..8.30 rows=1 width=104)"
> >> " Index Cond: (c.the_geom && n.the_geom)"
> >>
> >> 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.
> >>
> >>
> >>
> >> --
> >> Puneet Kishor
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
