D. Richard Hipp wrote: > > Let me strongly reiterate that you look into using the new R-Tree > virtual table available for SQLite. R-Trees are specifically designed > to do exactly the kind of query you are asking to do. See > > http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/rtree/README&v=1.2 > > R-Trees will be way faster than anything you will do using B-Tree > indices. >
Richard, Is this how you expect the RTree tables to be used in a case like the OP is interested in? create table City ( id integer primary key, name text, lat real, long real, class integer ); create virtual table CityLoc using rtree ( id integer referneces City, lat_min real, lat_max real, long_min real, long_max real ); -- if CityLoc uses point at center of city -- ie lat_min = lat_max and long_min = long_max select * from City where id in ( select id from CityLoc where lat_min between :min_lat and :max_lat and long_min between :min_long and :max_long ) and class <= :max_class order by class limit 20; -- general case where CityLoc has the extents of the -- city, ie lat_min < lat_max and long_min < long_max -- and you need to select all cities where any portion -- is between the limits -- this is the longest and possibly clearest -- where condition for the general case but it may -- execute slower due to the extra comparisons select * from City where id in ( select id from CityLoc where ((lat_min between :min_lat and :max_lat) or (lat_max between :min_lat and :max_lat) or (lat_min < :min_lat and lat_max > :max_lat)) and ((long_min between :min_long and :max_long) or (long_max between :min_long and :max_long) or (long_min < :min_long and long_max > :max_long)) ) and class <= :max_class order by class limit 20; -- this is an alternate where condition that excludes the -- the cases that do not overlap the area of interest select * from City where id in ( select id from CityLoc where not ((lat_min < :min_lat and lat_max < :min_lat) or (lat_min > :max_lat and lat_max > :max_lat)) and not ((long_min < :min_long and long_max < :min_long) or (long_min > :max_long and long_max > :max_long)) ) and class <= :max_class order by class limit 20; -- yet another alternate where condition after applying De'Morgans -- rule to the previous inverted logic select * from City where id in ( select id from CityLoc where (lat_min > :min_lat or lat_max > :min_lat) and (lat_min < :max_lat or lat_max < :max_lat) or (long_min > :min_long or long_max > :min_long) and (long_min < :max_long and long_max < :max_long) ) and class <= :max_class order by class limit 20; -- a further simplification of the general case that removes -- redundant terms select * from City where id in ( select id from CityLoc where (lat_min < :max_lat and lat_max > :min_lat) and (long_min < :max_long and long_max > :min_long) ) and class <= :max_class order by class limit 20; Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users