Storing and querying latitudes and logitudes in SQLite doesn't yield expected results

Question

I have saved locations (latitude and longitudes) as 'Double' data types in a SQLite database as shown below: SELECT * FROM 'locations'; all rows of table shown

However, when making queries with longitude, it doesn't always return the correct results as shown below: SELECT * FROM 'locations' WHERE longitude = '-117.8496541' ; query yield expected results

non-working query: SELECT * FROM 'locations' WHERE longitude = '-117.84457309999999' ; query yields no results, which is incorrect

Any advice on how to resolve this issue is highly appreciated. Not a homework, it is part of an Android app I am working on. I saved the locations using google maps locally on the phone and extracted the database to the computer to test queries on them. Thank you.

EDIT: More pictures to support question.

creating table with longitude of double datatype inserting row into db using ContentValues where longitude of double datatype

Answer 1

I believe your issue is related to the Storage Class/Type affinity.

That is the same value can be saved differently and thus retrieved differently.

I believe your solution may to force the type retrieved by using CAST.

That is by using :-

SELECT * FROM 'locations' WHERE CAST(longitude AS REAL) = '-117.84457309999999';

However, it would probably be best to ensure that the values are stored using only a single type rather than a mixture of TEXT and REAL.

Hopefully to explain, consider the following :-

DROP TABLE IF EXISTS locations;
CREATE TABLE IF NOT EXISTS locations (
        longitude1,           -- BLOB type affinity as no type has been specified
        longitude2 TEXT, 
        longitude3 REAL, 
        longitude4 NUMERIC, 
        longitude5 INTEGER, 
        longitude6 BLOB,
        longitude7 rUmpleStilSkin
        );
INSERT INTO locations VALUES('-117.84457309999999','-117.84457309999999','-117.84457309999999','-117.84457309999999','-117.84457309999999','-117.84457309999999','-117.84457309999999');
INSERT INTO locations VALUES(-117.84457309999999,-117.84457309999999,-117.84457309999999,-117.84457309999999,-117.84457309999999,-117.84457309999999,-117.84457309999999);
SELECT rowid AS id,
    typeof(longitude1) AS l1type, longitude1,
    typeof(longitude2) AS l2type, longitude2,
    typeof(longitude3) AS l3type, longitude3,
    typeof(longitude4) AS l4type, longitude4,
    typeof(longitude5) AS l5type, longitude5,
    typeof(longitude6) AS l6type, longitude6,
    typeof(longitude7) AS l7type, longitude7
FROM locations;

SELECT rowid AS id,* FROM locations WHERE longitude1 = '-117.84457309999999';
SELECT rowid AS id,* FROM locations WHERE longitude1 = -117.84457309999999;
SELECT rowid AS id,* FROM locations WHERE CAST(longitude1 AS REAL) = -117.84457309999999;
SELECT rowid AS id,* FROM locations WHERE CAST(longitude1 AS REAL) = '-117.84457309999999';

The stored values (first query)

The first result shows that even though the same values have been used, that the values are actually stored differently depending upon the type affinity (typeof shows the storage class)

as per :-

• When the storage class is REAL then the shortened form is used, whilst when the storage class is TEXT the full form is stored.
• From your first image it appears that the pair of values are always stored with a different storage class.

The 2nd and 3rd results

These show that using just the column and the value only 1 or the two rows is returned as the longitude1 value has a different storage class (TEXT for the 1st row, REAL for the 2nd) and relates to your issue.

The 4th and 5th results

Both of these show that using CAST to REAL for the longitude1 column extracts both rows.

You may wish to have a read of Datatypes In SQLite Version 3