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

[Michael Fuhr]

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).

-- 
Michael Fuhr