查询以在 SQLite 中基于半径获取记录? [英] Query to get records based on Radius in SQLite?

问题描述

我有这个在 MySQL 中运行良好的查询

SELECT ((ACOS(SIN(12.345 * PI()/180) * SIN(lat * PI()/180) +COS(12.345 * PI()/180) * COS(lat * PI()/180) * COS((67.89 - lon) *PI()/180)) * 180/PI()) * 60 * 1.1515 * 1.609344) AS 距离，poi.*发件人WHERE lang='eng'有距离<='30'

距离以公里为单位，输入为 lat=12.345 和 lon=67.89

SQLite 是 3，我不能像在 Android 上那样用它运行自定义函数.我也没有 acos() 等...因为它不是标准 SQLite 的一部分.

上面的查询在 SQLite 中会怎样?

解决方案

您可以创建 4 个新列，分别是 lat 和 lon 的 sin 和 cos.由于cos(a+b) = cos a cos b - sin a sin b，以及sin 和cos 的其他表现，如SIN(12.345 * PI()/180)可以在运行查询之前在程序中计算，大的距离"表达式简化为 SQLite3 可以处理的 P * SIN_LAT + Q * COS_LAT + ... 形式.>

顺便说一句，另见 Android 上的 Sqlite:如何创建 sqlite dist db 函数 - 在应用程序中使用经纬度进行距离计算.

I have this query which does work fine in MySQL

SELECT ((ACOS(SIN(12.345 * PI() / 180) * SIN(lat * PI() / 180) +
         COS(12.345 * PI() / 180) * COS(lat * PI() / 180) * COS((67.89 - lon) * 
         PI() / 180)) * 180 / PI()) * 60 * 1.1515 * 1.609344) AS distance, poi.* 
FROM poi
WHERE lang='eng' 
HAVING distance<='30'

distance is in Kilometers, the input is lat=12.345 and lon=67.89

The SQLite is 3, and I can't run custom functions with it as it's on Android. I also don't have acos() etc... as that is not part of the standard SQLite.

How would be the above query in SQLite?

解决方案

You can create 4 new columns, being sin and cos of lat and lon. Since cos(a+b) = cos a cos b - sin a sin b, and other appearances of sin and cos like SIN(12.345 * PI() / 180) can be calculated in the program before running the query, the big "distance" expression reduces to something of the form P * SIN_LAT + Q * COS_LAT + ... that can be handled by SQLite3.

BTW, see also Sqlite on Android: How to create a sqlite dist db function - to be used in the app for distance calculation using lat, long.

这篇关于查询以在 SQLite 中基于半径获取记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！