storing double values in SQLite: how to ensure precision?

Question

hi there!

i have a problem with double values i need to store in an android homed sqlite database. since these double values represent gps values (lat & lng), i really NEED an absolute precision down to the 9th number after the comma.

now i have a table like this:

CREATE TABLE x REAL lng;

and insert sth (hardcoded) like:

INSERT INTO x lng = '1.0';

and when reading lng from this table into some (java) double variable, i get a value like "0.999956837" - this renders the values pretty useless to me.

is there a way to enforce the precision i need other than storing the values as "text" fields (what would make expensive casts neccessary) or storing them as integers (meaning i need to multiply/divide at each write/read-op)?

Answer 1

SQLite is typeless, that means all representation is written as text, probably the wrapper api does some converts you don't know of, that you get those results.

If you need to store the data as string do it.

Just when you read out the double make sure you saved in the right format, you can use getDouble on the column.

Answer 2

double has about 17 decimal digits of precision, so if 9 digits is what you need, there should be no problem (assuming that you don't do any complex calculations on those values). Just make sure you never end up using float, because that has only about 7 digits of precision.

You should also make sure you understand how binary floating-point works, and that it will always result in seemingly "round" values becoming slightly off - which simply does not matter for most applications (including yours) as long as it happes somewhere in the 17th decimal digit. See that link also for alternatives for applications where it does matter.