[postgis-users] optimize: count up points near a point
josh at livniconsulting.com
Thu Jul 13 18:57:06 PDT 2006
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.
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
>> 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