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

[Martin Davis]

For more detail check out this thread on the same issue:

http://postgis.refractions.net/pipermail/postgis-users/2011-November/031345.html

On 12/20/2011 5:28 PM, Puneet Kishor 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
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.1890 / Virus Database: 2109/4692 - Release Date: 12/20/11
>
>