Ah, I see...I totally missed that last line. Sorry.
On Tue, Dec 20, 2011 at 8:49 PM, Puneet Kishor <punk.k...@gmail.com> wrote: > > On Dec 20, 2011, at 7:44 PM, Brian Stempin wrote: > > > 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 > > > > > If you see the EXPLAIN QUERY PLAN I posted (see below), ST_Intersects > automatically does an && check. Nevertheless, I tried your specific > suggestion above, and it took 201 seconds. No joy. > > > > > > 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
