Try something like this: CREATE INDEX ix_polys ON polys (xmin, ymin, xmax, ymax);
SELECT name FROM polys WHERE EXISTS (SELECT x FROM points WHERE point_id = 1 AND xmin < x AND ymin < y AND xmax
x AND ymax > y);
In your query you do 4 select from points table. Here, you do only one. I think this should be an important improvement because table points has 5M records. By the way, if you can, it is faster to create the index after you have inserted all record in the DB. And it also consume less storage in disk. Sorry my english. On 8/26/06, P Kishor <[EMAIL PROTECTED]> wrote:
On 8/26/06, Joe Wilson <[EMAIL PROTECTED]> wrote: > > --- P Kishor <[EMAIL PROTECTED]> wrote: .. > > Actually, I have tried many other approaches, and SQL seems to be a > > fairly efficient one. Here is why -- in the worst case, I have to test > > each point against each poly (a geometry algorithm allows me to test > > if the point is definitely in the poly or not). That is the worst > > case, as it is 200k X 5 mill = 1 trillion transactions. > > You don't have to test each point against each poly if wisely use > the 4 in-memory sorted perl poly ref arrays. > 5m points x log2(200k polys) x 4 = 352m in-memory comparisons maximum. > You could process the 5 million points in less than an hour in perl, > or in just a few minutes in C/C++ or Java. > Of course this is just computation/matching time. It excludes the > additional time it would take to update the names of the points in > the database. > That sounds very intriguing and worth exploring. Except, I am being really daft and not following what you are suggesting. So, given my poly data like so poly_id (int pk), xmin, ymin, xmax, ymax (all real), and name (text), you want me to create four arrays, each array containing each one of xmin, ymin, etc., sorted? Where is this going? -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.ies.wisc.edu/ Open Source Geospatial Foundation https://edu.osgeo.org/ ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
-- Pablo
