Android - 在SQLite数据库中保存位置

I have problem with save latitude and longitude in SQLite database. I can save some location like (123.123999, 123.123999) and it will save in the database. But if I want to read it, I get rounded location like (123.124, 123.124).

我在SQLite数据库中保存纬度和经度有问题。我可以保存一些位置,如(123.123999,123.123999),它将保存在数据库中。但如果我想阅读它,我会得到像(123.124,123.124)那样的圆形位置。

My table created by this sql segment:

我的表由这个sql段创建:

CREATE TABLE locs(id INTEGER PRIMARY KEY AUTOINCREMENT, lat DOUBLE, lng DOUBLE, num INTEGER)

And querys results:

并查询结果:

SELECT * FROM locs WHERE lat = 123.124; //RETURN NO ROWS
SELECT * FROM locs WHERE lat = 123.123999; //RETURN 1 ROW

So I can't use the numbers I get to do queries on the data.

所以我不能用我得到的数字来查询数据。

I believe that I am not the first that handling with this problem, because it is basic thing. But I didn't found any answers that work on SQLite database.

我相信我不是第一个处理这个问题的人,因为这是基本的事情。但我没有找到任何适用于SQLite数据库的答案。

How can I read and write the correct number in the db?

如何在db中读取和写入正确的数字?

2 个解决方案

#1

How can I read and write the correct number in the db?

如何在db中读取和写入正确的数字?

I suggest you an one quick solution.

我建议你快速解决一个问题。

Since you know in what format you are storing your coordinates e.q. numbers so there is no problem to store them as TEXT and then retrieve them as TEXT and simple perform parsing back to DOUBLE if you'll want to do some stuff with them.

因为您知道以什么格式存储坐标e.q.因此将它们存储为TEXT然后将它们检索为TEXT并将其解析为DOUBLE(如果您想要对它们执行某些操作)没有问题。

CREATE TABLE locs(
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   lat TEXT,
   lng TEXT,
   num INTEGER
)

#2

You should define an epsilon around the queried value, i.e.

您应该在查询的值周围定义一个epsilon,即

SELECT * FROM locs WHERE lat BETWEEN value-epsilon AND value+epsilon;

You can change your epsilon according to your need. In the example you gave 1e-3 should suffice.

您可以根据需要更改您的epsilon。在示例中,您给出的1e-3应该足够了。

Another option is to round the values when you insert them, but then you'll lose information you might need down the road.

另一种选择是在插入值时对值进行舍入,但随后您将丢失在路上可能需要的信息。

#1