The sample poly.name's you've provided in your example seem to be integers. Is that always the case? Are the poly_id's unique? If both of these are true, you might consider making poly_id your primary key in the polys table in order to eliminate a column.
This problem is very difficult to do in pure SQL efficiently. You might conisder reading in all the polys in memory, and then doing a single pass over your point data, assigning a name to each point as you go along. The SQL indexes would be of no use to you with this algorithm. Instead, you would need a sorted perl array of poly \references for each of ymin, ymax, xmin, xmax. The 200K polys and in-memory perl indexes should take no more than 20M of RAM. --- 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/ > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
