[postgis-users] help optimize distance calculation

[Sky]

Paul,

Now it's starting to click...

A query over 400,000 points w/o dwithin took 972 seconds. A query over
400,000 points w/ dwithin(25000) took 111 seconds. Nice!

Sounds like I need to think about my scenario for a bit and figure out
if I can strike a balance between dwithin tolerance and query
execution time.

Really appreciate your help and patience with me,
Sky


On Wed, Jan 28, 2009 at 1:55 PM, Paul Ramsey <pramsey at opengeo.org> wrote:
> On Wed, Jan 28, 2009 at 10:41 AM, Sky <shlyon+postgis at gmail.com> wrote:
>
>> Again, the query doesn't seem to use the ao.the_geom index... correct?
>> Here is the query I used:
>>
>>  explain analyse select ao.gid, min(distance(f.the_geom,ao.the_geom))
>> from features f, ao ao group by ao.gid limit 400000
>>
>> This query should use the grid_inx_geo... yeah?
>
> No, this query has not got a spatial constraint, so the spatial
> indexes have nothing to work against.
>
> First, is the "max mindistance" concept going to be useful to you? If
> not, just discard the index idea -- you're going to have to brute
> force it, which is what you're already doing. For the "max
> mindistance" to help, it has to be a small fraction of your grid
> extent.  If I knew that, in my grid, I had a candidate point at the
> center of each quadrant of the grid, I could use a "max mindistance"
> of grid_width / 4 and know that I would pick up one candidate for
> every cell.
>
> Or, perhaps there are distances you just don't care about, they are
> too big to be meaningful to your analysis. That's another upper
> threshold.
>
> In all events, the distance has to be small enough that applying it at
> a grid cell reduces the amount of candidate area to be tested
> substantially relative to the total grid extent.
>
> Best,
>
> P
>
>>
>> Thanks again for your assistance Paul,
>> Sky
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>