[postgis-users] optimize: count up points near a point
josh at livniconsulting.com
Thu Jul 13 18:07:36 PDT 2006
I checked a few threads on this topic, but didn't come across anything
that helped me make my query a lot quicker. So, I post here asking for
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...
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
I have a btree index on the attribute and other_attribute columns, and a
gist index on the parcels centroid(the_geom) column.
Any help is greatly appreciated,
More information about the postgis-users