> Is this how you expect the RTree tables to be used in a case like
> the OP
> is interested in?
This is how I expected it to be used. This whole post is a pretty
good example actually, we should something like this in the wiki.
As I think people have found, the final simplification is much
better than the other formulations. The R-Tree implementation (and
I think all other virtual tables) suffers from the same constraint
as regular SQLite indexes: If there is an 'OR' in the WHERE clause
then the special R-Tree index cannot be used and you end up with
a linear scan.
Another way to write it is to use a join to do the same thing:
SELECT city.*
FROM City, CityLoc
WHERE
City.id=CityLoc.id AND
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;
In this case I think both will perform similarly, because SQLite
has to pull all the data out of the tables before sorting it in
either case. But if the ORDER BY clause was omitted the join
method might be more responsive.
Dan.
> 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;
Dan.
> 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
