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 <pram...@opengeo.org> wrote: > On Wed, Jan 28, 2009 at 10:41 AM, Sky <shlyon+post...@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@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
