Instead of indexing each column on its own, try making them one big index.
On 8/26/06, P Kishor <[EMAIL PROTECTED]> wrote:
As a follow-up to my own email below, I ran the following query SELECT COUNT(py.poly_id) FROM polys py JOIN points pt ON (py.xmin < pt.x AND py.ymin < pt.y AND py.xmax > pt.x AND py.ymax > pt.y); to determine how many exact points-in-poly matches I could find. The query has now been running for the past couple of hours, no end in sight. So, I am not even sure if UPDATEing the points.name column based on this logic would be helpful. In any case, suggestions welcome. On 8/26/06, P Kishor <[EMAIL PROTECTED]> wrote: > Greets, > > Using SQLite for Windows 3.3.7. I have the following two tables > -- bounding box of each poly > CREATE TABLE polys ( > poly_id INTEGER PRIMARY KEY, > xmin REAL, > ymin REAL, > xmax REAL, > ymax REAL, > name TEXT > ) > > data look like so > > 1|1723885.18957644|282631.95646140|1727224.46537863|287816.54753434|100030117004 > 2|1716073.04710809|281166.39606662|1725746.97483727|287098.00276086|100030118009 > > -- coord pair of each point > CREATE TABLE points ( > point_id INTEGER PRIMARY KEY, > x REAL, > y REAL, > name TEXT > ) > > data look like so > > 1|-2268900.28781180|191367.60670709| > 2|-2269660.73941476|193426.66511514| > > I have built the following indexes > > CREATE INDEX ix_polys_xmin ON polys (xmin) > CREATE INDEX ix_polys_ymin ON polys (ymin) > CREATE INDEX ix_polys_xmax ON polys (xmax) > CREATE INDEX ix_polys_ymax ON polys (ymax) > CREATE INDEX ix_points_x ON points (x) > CREATE INDEX ix_points_y ON points (y) > > Here is what I want to do: I want to SET points.name = polys.name WHERE > > polys.xmin < (SELECT points.x FROM points WHERE point_id = ?) AND > polys.ymin < (SELECT points.y FROM points WHERE point_id = ?) AND > polys.xmax > (SELECT points.x FROM points WHERE point_id = ?) AND > polys.ymax > (SELECT points.y FROM points WHERE point_id = ?) > > yields one and only one result. For all other records, I want to run a > further point-in-poly function which enables me to find an exact > match. In other words, I minimize the number of times I have to run my > point-in-poly function by getting SQLite's help in eliminating the > points I am know to definitely fall inside a specific poly. > > So, to test my approach, I set up a loop over the points (I am using > Perl DBI), and tried the following -- > > foreach point_id > > SELECT name > FROM polys > WHERE > xmin < (SELECT x FROM points WHERE point_id = ?) AND > ymin < (SELECT y FROM points WHERE point_id = ?) AND > xmax > (SELECT x FROM points WHERE point_id = ?) AND > ymax > (SELECT y FROM points WHERE point_id = ?) > > If only one polys.name was found, that's it > > elsif more than one polys.name were found > run another function to determine exact match > > Well, I learned that perhaps this may not be the way to do this. You > see, I have more than 200k rows in my polys table, and more than 5 > million rows in the points table. I ran the above query last night, > went to sleep, and this morning it was still churning away (or had > frozen the 'puter). I had to kill it. Looping over each point is just > way too slow. > > Another approach would be to loop over each poly and narrow my set to > all the points in it. Then run my function on each point against that > poly. However, this will also likely take very long -- one, I have to > still loop over each row, and then, when I find the points, I have > check each point (as I might have overlapping polys). > > Suggestions? > > -- > 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/ > -- 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] -----------------------------------------------------------------------------
-- Cory Nelson http://www.int64.org ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
