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

[Stephen Woodbridge]

Josh,

The expand of point forms a square and the distance forms a circle 
within the square. If your use case allows it and you don't care about 
trimming the corners off the square, skip the distance calc all 
together. Which will really speed things up.

-Steve W

Josh Livni wrote:
> 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.
> 
> 
> 
>   -Josh
> 
> 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 
>>> 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).
>>
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>