[postgis-users] optimize: count up points near a point

[Josh Livni]

Thanks for the good points Michael -- I'll definitely make a centroid 
geometry column (I only query this; never update).

I'll also create index on the 'expand(centroid_geom),3280', since that 
seems to have sped it up a lot.  Unfortunately my distance sometimes 
varies, and is not always a kilometer, but I still think it will be 
faster to just make a bunch of indices.

Anyway, more to the point, as I was changing my query to take advantage 
of my new centroid geometry column, I realized my massive typo:  I was 
using "(expand(p.the_geom,3280.8) && ..." instead of 
"(expand(centroid(p.the_geom),3280.8) && ..."

At first glance, fixing this up, combined with your suggestion of 
indexing the expanded centroid, seems to speed it up an order of 
magnitude or so.



   -Josh

PS I think I'll also make it "(expand(p.the_geom,3280.8 + some extra 
distance) && ... " and index that too, to be sure I'm picking up the 
points I want, but this should still be much much faster than expanding 
the polygon w/no index


Michael Fuhr wrote:
> On Thu, Jul 13, 2006 at 06:07:36PM -0700, Josh Livni wrote:
>> I have about a million parcels, some of which are missing an attribute. 
>>  I'd like to query parcels nearby (say within 1km) to get some stats on 
>> their value for the attribute.
>>
>> Right now it takes about 40-60 seconds to report on the nearby parcels - 
>> since over 100k parcels are missing the attribute, this is a problem...
>>
>> ---BEGIN QUERY---
>> SELECT count(*) as count,
>>   avg(asr_impr) as avg,
>>   sum(asr_impr) as sum,
>>   max(asr_impr) as max,
>>   min(asr_impr) as min
>> FROM parcels as p
>> WHERE attribute > 0 and other_attribute = 11
>>  AND (expand(p.the_geom,3280.8) && setsrid('POINT(6296272.3 
>> 1955364.5)'::geometry,102646))
>>  AND distance(centroid(p.the_geom), setsrid('POINT(6296272.3 
>> 1955364.5)'::geometry,102646)) < 3280.8
>> ---END QUERY---
>>
>> I have a btree index on the attribute and other_attribute columns, and a 
>> gist index on the parcels centroid(the_geom) column.
> 
> What does EXPLAIN ANALYZE show for this query?  I don't think an
> index on centroid(the_geom) is going to be used, but an index on
> expand(the_geom, 3280.8) should be if you have one.  You might also
> be able to speed up queries by creating another geometry column to
> hold the centroid, which you could populate automatically via a
> trigger (at the cost of slowing down inserts and updates).
>