[postgis-users] optimize: count up points near a point
mike at fuhr.org
Thu Jul 13 18:27:17 PDT 2006
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
> 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).
More information about the postgis-users