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

[Ben Madin]

Puneet,

Trying to find which country a large set of points was in we have actually found it much quicker to find points in provinces (smaller polygons) and then return the country code associated with the province. No chopping anything, so I would guess you could use a world map, allocate a continent to each country in the world map and query it. 

The speed up we saw was (I guess) for the same reason - the bbox was efficient.

cheers

Ben


On 21/12/2011, at 12:06 PM, Mr. Puneet Kishor wrote:

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