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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
