Dennis- Your last "simplification":
> -- 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)? -Shane On 6/4/08, Dennis Cote <[EMAIL PROTECTED]> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
