[postgis-users] speeding up simple pt-in-poly lookups

[Brian Stempin]

Ah, I see...I totally missed that last line.

Sorry.

On Tue, Dec 20, 2011 at 8:49 PM, Puneet Kishor <punk.kish at 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.kish at 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.kish at 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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111220/5542f911/attachment.html>