Shane Harrelson wrote: >> -- 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; > > > > If I understand correctly, this is just suppose to select the City id's from > CityLoc that are completely inside the selection rectangle (with the > additional constraints)? >
The sub select on the RTree table should return the ids of all the cities with a bounding rectangle that overlaps any part of the selection rectangle, not only those that are completely inside the selection rectangle. The outer select does the work of applying the other constraints, ordering, etc. I see now that this optimized where condition also correctly handles the single point special case (i.e. where lat_min = lat_max and long_min = long_max, and the rectangle has zero area) with the same number of comparisons so it can be used in all cases. Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
