I have a table with approximately 6.13 million entries, though I plan to extend it to nearly 100 million in the near future. Each entry has three (important) properties: ID, latitude and longitude. ID is a 64 bit integer and latitude and longitude are 64-bit floats, though 32-bit floats would still work well.
At the moment, issuing a query like this: sqlite> select * from nodes where n_lat >= 51.5 and n_lon >= -1 and n_lat <= 51.505 and n_lon <= -0.995; takes ages (about 30 seconds for 6 rows out of 6 million), because it requires a full table scan and the index on latitude and longitude doesn't help. I have already managed to get r-trees working with my roads database, and that is very fast. Before, I used to be able to fetch a small bounding box in about 80 ms. Now I can do it at least 60x faster. I am wondering whether r-trees even work with nodes, which are not bboxes, just points. I was considering adding a small bounding box around each node, but this might cause my database to balloon even more (size is a serious concern as the database will be stored on 32 GB SD cards for an entire world map and it is taking 500 MB (squashfs gzip) just to store the UK which is approximately 6% of the total map) Is there a better way for what I want to do or do I have to do this? Thanks, Tom _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
