Christophe Leske wrote:
> I need lat and long pos from CityLoc.
>
> I got this currently,
>
> sqlite> Select cities.*, citylookup.longitude_min from cities,citylookup
> where c
> ities.id in (select id from citylookup where
> (citylookup.longitude_min>-45.00000
> 0 and citylookup.longitude_max<45.000000) and
> (citylookup.latitude_min>-45.11050
> 2 and citylookup.latitude_max<44.889498)) and cities.class_dds<2 order
> by class_
> dds limit 50;
>
> However, this doesn´t give me the city with the longitude and latitude
> for its position (which i need to position a label).
>
>
I had the lat and long data duplicated in the RTree table (i.e. I was
treating it much like an index).
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 you aren't storing the lat and long data in the main table anymore,
you will have to join the RTree table on the id to get that data. I'm
guessing about your tables definitions, but you should get the idea from
this:
Select cities.*,
citylookup.longitude_min,
citylookup.latitude_min,
from cities
join citylookup on citylookup.id = cities.id
where cities.id in
(
select id from citylookup as c
where c.longitude_min>-45.000000
and c.longitude_max<45.000000
and c.latitude_min>-45.110502
and c.latitude_max<44.889498
)
and cities.class_dds<2
order by class_dds
limit 50;
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
