CODEWITHSUNDEEP
androidsqlitegps
rmaik
rmaik

Reputation: 1086

How to optimize spatial data retrieval in Android

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?

Upvotes: 1

Views: 849

Answers (6)

ED73170
ED73170

Reputation: 21

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.

Upvotes: 2

Rolf of Saxony
Rolf of Saxony

Reputation: 22453

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.

Upvotes: 0

Ofek Ron
Ofek Ron

Reputation: 8578

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);

Upvotes: 0

tafia
tafia

Reputation: 1562

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.

Upvotes: 0

CL.
CL.

Reputation: 180192

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.

Upvotes: 1

Aquiles Carattino
Aquiles Carattino

Reputation: 930

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?

Upvotes: -2

Related Questions