> I added an index on the ID field for the search in the city database, > that helped a bit, but i am dissapointed that the rtree search is not > faster than the normal search for bigger areas.. Here are the query times in ms for full globe view with zooming in to Romania:
-- 21290 -- full globe view -- 5338 -- 2347 -- 2621 -- 82 -- romania The last one is pretty good - i get almost all the cities in the country in 82ms, which is great. But the intial one is way to slow, it blocks the app 21 seconds - granted, it is the inital start up, but still... Here is the SQL used, with the query time below it. The database "Citylookup" is an rtree-table: -- "Select * from cities where id in (select id from citylookup where (citylookup.longitude_min>-45.000000 and citylookup.longitude_max<45.000000) and (citylookup.latitude_min>-45.110066 and citylookup.latitude_max<44.889934)) and cities.class_dds<2 order by class_dds limit 50" -- 2008 -- "Select * from cities where id in (select id from citylookup where (citylookup.longitude_min>-36.913433 and citylookup.longitude_max<53.086567) and (citylookup.latitude_min>-29.448473 and citylookup.latitude_max<60.551527)) and cities.class_dds<2 order by class_dds limit 50" -- 4305 -- "Select * from cities where id in (select id from citylookup where (citylookup.longitude_min>-28.706917 and citylookup.longitude_max<61.293083) and (citylookup.latitude_min>-5.173247 and citylookup.latitude_max<84.826753)) and cities.class_dds<2 order by class_dds limit 50" -- 4299 -- "Select * from cities where id in (select id from citylookup where (citylookup.longitude_min>1.764689 and citylookup.longitude_max<28.204563) and (citylookup.latitude_min>32.128411 and citylookup.latitude_max<46.077725)) and cities.class_dds<4 order by class_dds limit 50" -- 425 -- "Select * from cities where id in (select id from citylookup where (citylookup.longitude_min>23.468423 and citylookup.longitude_max<50.946270) and (citylookup.latitude_min>34.570643 and citylookup.latitude_max<48.494728)) and cities.class_dds<4 order by class_dds limit 50" -- 278 -- "Select * from cities where id in (select id from citylookup where (citylookup.longitude_min>13.282298 and citylookup.longitude_max<40.226794) and (citylookup.latitude_min>33.355038 and citylookup.latitude_max<47.291672)) and cities.class_dds<4 order by class_dds limit 50" -- 297 -- "Select * from cities where id in (select id from citylookup where (citylookup.longitude_min>21.390184 and citylookup.longitude_max<34.262570) and (citylookup.latitude_min>37.546776 and citylookup.latitude_max<44.330523)) and cities.class_dds<5 order by class_dds limit 60" -- 63 -- "Select * from cities where id in (select id from citylookup where (citylookup.longitude_min>21.477932 and citylookup.longitude_max<29.315407) and (citylookup.latitude_min>40.614945 and citylookup.latitude_max<44.657669)) and cities.class_dds<6 order by class_dds limit 80" -- 36 -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
