1

I have SQLite table contains 1.5M lines, the table contains <lat,lng, info>, what i want to do is, for every time I receive data "lat,lng" from the GPS, I should access the table and display the corresponding info.

my question is, is this operation will be exhaustive and time consuming? if yes, how can I achieve better data retrieval fro mthe table every time i receive GPS data?

rmaik
  • 1,076
  • 3
  • 15
  • 48
  • 1
    Shouldn't be an issue, sounds like a simple `where lat = x and lng = y`. You may want to consider performance issues in how often you query the data though, you could code a check to ensure you don't query the same values consecutively. Or maybe even add a cache if you don't expect there to be too many changes to position (all depends on what your app does). Furthermore you may want to consider storage space of such a large table on a mobile device, but again depending on app you might not have any other options than storing all the data – musefan Aug 20 '15 at 09:19
  • You could simply load a number of points into memory when you query for a LatLng using a _range_ that represents points in a close proximity. – Ed Holloway-George Aug 20 '15 at 09:21
  • @musefan i will query the data "where lat = x and lng = y" every time i receive GPS signal? would that be an issue – rmaik Aug 20 '15 at 09:21
  • @EdGeorge is there any specific way to save into memory?please clarify – rmaik Aug 20 '15 at 09:23
  • 1
    Just put them in an collection of some sort like `ArrayList` or something. Although I dont think there is really any need for that, as it has already been said, unless you are polling constantly there should not be a real overhead. If you are polling too much, just check the distance between the last point you polled and your current position is greater than a certain delta. – Ed Holloway-George Aug 20 '15 at 09:24
  • @rmaik: The reality is, we can't answer that. It depends a lot on how your app is coded (such as frequency of calls) and will also depend on device resources. This is really something that only you can determine, you have to do your own performance testing and decide if the results are acceptable by your requirements. I feel I will have to vote to close this question as it seems too broad – musefan Aug 20 '15 at 09:25
  • 1
    I agree, the question is far too broad. You haven't provided any code for context and it is all revolves around the question of how many times you poll the database. If you think you do it too much, chances are it is too much and you'll need to limit it. – Ed Holloway-George Aug 20 '15 at 09:33
  • The question is well unclear. You receive a position from GPS, why do you need to look up info in the table? Please note that practically you never will get the same position twice. So your lookup cannot be an exact lookup. You need a lookup of type getInfoNearbyOf(lat, long) – AlexWien Aug 20 '15 at 14:27
  • you have exactly 0 chance to be at a given `lat,lng` point. – njzk2 Aug 20 '15 at 14:44
  • @njzk2 would you please explain why? – rmaik Aug 20 '15 at 14:49
  • Because latitude is continuous, not discrete. Move one meter to your left, you'll receive a different position. Better yet, don't move, you'll still receive a different position, because of your GPS receiver's precision. – njzk2 Aug 20 '15 at 14:56
  • @musefan in your comment you said: Or maybe even add a cache if you don't expect there to be too many changes to position. whould you please tell me what is "cache" and why it could be a solution to my question? – rmaik Aug 21 '15 at 09:18
  • 1
    @rmaik: In this case a cache would be when you store a query result in memory so that next you call the same query you take the result from memory instead of pulling it from the database. For example, everytime you query the database, store the result in a list of some sort with the key being the query input (x and y). Then everytime you need to query, first check your list for matching x and y, If you find match use that result, if no match then query the database (and cache that result). – musefan Aug 21 '15 at 09:21
  • 1
    @rmaik: Pulling from memory will be faster, but the overhead comes in play if you start making lots of queries. If the memory usage of your cache becomes too great that itself will start to cause performance issues. The choice really depends on the situation. You could even go as far as limiting the cache to the last 10 unique queries for example, which may be a good option to take anyway. So if the device isn't moving, you don't have to keep querying database for same position, just use the one in memory instead. In fact, I would definitely look into doing this anyway – musefan Aug 21 '15 at 09:24
  • @musefan but i would like to clarify a point. on th road while driving the gps coords. change "and lets say i would read gps coords every 3 seconds" and if i used in-memory storage "limited to 100 entry", i will still query the data base more often that the in-memory storage, becaus ethe cache will contain the last queried info based on lat and lng, and while driving the chance to be at the same lat and lng is really low...please advice – rmaik Aug 21 '15 at 10:00
  • 1
    @rmaik: This is what I mean by it depends on the situation, if driving you are unlikely to get the same query with 3 second intervals, but perhaps you will be stuck in traffic or at traffic lights so not moving. I would say just stick with a handful of cached results (maybe 5 - 10). It won't take up much memory, but it will be useful for instances where the device is not moving (or moving very little). This cache approach wont solve all problems, but it's definitely going to improve some things. So worth doing. Try to use a dictionary/hashset type collection, they are faster at lookups – musefan Aug 21 '15 at 10:24

6 Answers6

2

You should use Spatialite, it is a Sqlite extension to use with geographical data, it allows you to create spatial indexes and then query data which is included in a rectangle, for example around x meters from your gps coordinates.

ED73170
  • 21
  • 1
  • Looks like Spatialite already does what my answer partially proposed and worse than that it has a python friend as well - pyspatialite 2.6.1. – Rolf of Saxony Aug 20 '15 at 15:24
  • thanks for your answer. all what i need to save in the data base is latitude,longitude and speed information and the type of the road. do u think i need spatalite for that? thanks. – rmaik Aug 21 '15 at 08:46
1

You do no want to search for an exact location (GPS is not accurate enough for that), but for nearby entries.

Two-dimensional interval searches are not efficient when using 'normal' indexes. You would need to use something like an R-tree index, which is not available on Android by default.

If your table never changes, you could prepare the R-tree on another machine, and in the app, search for entries manually.

Community
  • 1
  • 1
CL.
  • 173,858
  • 17
  • 217
  • 259
0

I don't know exactly what your data looks like but the first thing I'd do (given that GPS position may not evolve much) is to first create a temp table with a smaller range of coordinates so you can probably reduce the 1.5M records into some thousands only. Then everything should be faster.

I didn't know about the R-Tree @CL spoke about but indeed they look very promising if only you could manage to use them under android.

tafia
  • 1,512
  • 9
  • 18
  • upvote for The R-Tree. There are simpler and more suitable solutions, but it will work very well with an R-Tree – AlexWien Aug 20 '15 at 14:31
0

Well if your use case is not as other suggests - means that you are looking for an exact position rather then an area, Then all you can do is make sure you add an index on (lat,lng) and pray for the best...

CREATE INDEX index_lat_lng ON MyTable(lat,lng);
Ofek Ron
  • 8,354
  • 13
  • 55
  • 103
  • 2
    for lat, lon delivered by GPS practically never an exact lookup is suitable. MAybe he asked an unclear question, but your answer will not solve his problem. – AlexWien Aug 20 '15 at 14:28
  • from my understanding he wants to search for a specific lat,lng. if its not what he wants then others answer might be more relavant its for him to decide – Ofek Ron Aug 20 '15 at 14:34
  • 1
    Since it is extremly unlikely that two GPS receivers deliver the same position, altough the stand at the same location, he cannot do an exact lat/long search, – AlexWien Aug 20 '15 at 14:41
  • he said he saves GPS locations he never said that he takes raw gps data and qurey using it, maybe he saves the gps in the database and send it somewhere else and later want to retrieve the info from the database, jizzz – Ofek Ron Aug 20 '15 at 15:00
  • You cannot retrieve later, if you don't know the lat, long later. You get the lat, lon once, and never again. – AlexWien Aug 20 '15 at 16:23
0

Your question is far to woolly.
what i want to do is, for every time I receive data "lat,lng" from the GPS, I should access the table and display the corresponding info.
You need to define how often you receive data.
Will it be exhaustive and time consuming? Maybe, maybe not, without knowing how often you receive data.
Given the above, have you considered and assuming that the GPS co-ordinates are not coming in from all over the shop, creating an in memory database snapshot of the area in and around where your latitude and longitudes are currently coming from and using that until such time as they stray from the current snapshot area. At that point you will need to write out the existing data create a new snapshot for the new co-ordinates. The snapshot areas could be defined by degrees, minutes or seconds depending how fast the thing, that I suppose you are tracking, is expected to move.

Rolf of Saxony
  • 21,661
  • 5
  • 39
  • 60
  • thanks for your answer. all what i need to save in the data base is latitude,longitude and speed information and the type of the road. do u think i need spatalite for that? thanks – rmaik Aug 21 '15 at 08:47
  • I will defer to ED73170, as they suggested it and I have not used that software. – Rolf of Saxony Aug 21 '15 at 09:36
-2

You can try just with a command like:

SELECT info FROM Table WHERE lat = 'lat' AND lng = 'long';

Doesn't that just work well enough for your purpose?

Aquiles Carattino
  • 910
  • 1
  • 10
  • 23