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

[Mr. Puneet Kishor]

On Dec 20, 2011, at 9:48 PM, Martin Davis wrote:

> For more detail check out this thread on the same issue:
> 
> http://postgis.refractions.net/pipermail/postgis-users/2011-November/031345.html


Thanks. Chopping up my coverage into hundreds of small regions is the last avenue I want to try. Going by the text of that email, it seems that "few, large, regions with many vertices (may be) the problem." I will try generalizing my continents so that I have "few, large regions with *very few* vertices" and see if that speeds up the SELECTs.


> 
> 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
>> 
>> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users