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

Josh Livni 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 
your advice:

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 mailing list