我有纬度和经度,想要从数据库中取出最靠近这个坐标的记录,如果距离超过了指定的距离,则不检索它。
表结构:
id
latitude
longitude
place name
city
country
state
zip
sealevel
我有纬度和经度,想要从数据库中取出最靠近这个坐标的记录,如果距离超过了指定的距离,则不检索它。
表结构:
id
latitude
longitude
place name
city
country
state
zip
sealevel
simpledb.execSQL("CREATE TABLE IF NOT EXISTS " + tablename + "(id INTEGER PRIMARY KEY AUTOINCREMENT,lat double,lng double,address varchar)");
simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2891001','70.780154','craftbox');");
simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2901396','70.7782428','kotecha');");//22.2904718 //70.7783906
simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2863155','70.772108','kkv Hall');");
simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.275993','70.778076','nana mava');");
simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2667148','70.7609386','Govani boys hostal');");
double curentlat=22.2667258; //22.2677258
double curentlong=70.76096826;//70.76096826
double curentlat1=curentlat+0.0010000;
double curentlat2=curentlat-0.0010000;
double curentlong1=curentlong+0.0010000;
double curentlong2=curentlong-0.0010000;
try{
Cursor c=simpledb.rawQuery("select * from '"+tablename+"' where (lat BETWEEN '"+curentlat2+"' and '"+curentlat1+"') or (lng BETWEEN '"+curentlong2+"' and '"+curentlong1+"')",null);
Log.d("SQL ", c.toString());
if(c.getCount()>0)
{
while (c.moveToNext())
{
double d=c.getDouble(1);
double d1=c.getDouble(2);
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
这里是 MS SQL 版本:
DECLARE @SLAT AS FLOAT
DECLARE @SLON AS FLOAT
SET @SLAT = 38.150785
SET @SLON = 27.360249
SELECT TOP 10 [LATITUDE], [LONGITUDE], SQRT(
POWER(69.1 * ([LATITUDE] - @SLAT), 2) +
POWER(69.1 * (@SLON - [LONGITUDE]) * COS([LATITUDE] / 57.3), 2)) AS distance
FROM [TABLE] ORDER BY 3
+----+-----------------------+---------+--------------+---------------+
| id | email | name | location_lat | location_long |
+----+-----------------------+---------+--------------+---------------+
| 7 | test@gmail.com | rembo | 23.0249256 | 72.5269697 |
| 25 | test1@gmail.com. | Rajnis | 23.0233221 | 72.5342112 |
+----+-----------------------+---------+--------------+---------------+
$lat = 23.02350629;
$long = 72.53230239;
DB::SELECT("SELECT * FROM (SELECT , ((ACOS(SIN(($lat * pi() / 180)) * SIN((lat
* pi() / 180)) + COS(($lat * pi() / 180)) * COS((lat
* pi() / 180)) * COS((($long - LONG
) * pi() / 180)))) * 180 / pi()) * 60 * 1.1515 * 1.609344) AS distance FROM users
) users WHERE distance <= 2");
使用Mysql查询搜索距离限制和where条件的坐标
SELECT id, ( 3959 * acos( cos( radians('28.5850154') ) * cos( radians(latitude) ) * cos( radians( longitude ) - radians('77.07207489999999') ) + sin( radians('28.5850154') ) * sin( radians( latitude ) ) ) ) AS distance FROM `vendors` HAVING distance < 5;
这个问题并不难,但如果需要优化它,就会变得更加复杂。
我的意思是,你的数据库中有100个位置还是100百万个位置?这是很大的区别。
如果位置数量较少,可以通过以下方式将它们从SQL中提取到代码中:
Select * from Location
一旦你将它们编码,使用Haversine公式计算每个纬度/经度与原始值之间的距离并进行排序。
HAVING distance < ...
,那么该查询很可能会检查每一行并计算每一行的距离。(速度慢且不可扩展。) - Rick James