我有经纬度,我想从数据库中提取记录,它有最近的经纬度,如果这个距离比指定的长,就不检索它。

表结构:

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();
    }

其他回答

在极端情况下,这种方法会失败,但为了性能考虑,我跳过了三角函数,只是简单地计算对角线的平方。

这个问题一点也不难,但是如果你需要优化它,它就会变得更加复杂。

我的意思是,你的数据库中有100个地点还是1亿个?这有很大的不同。

如果位置的数量很小,只需执行->,就可以将它们从SQL中取出并放入代码中

Select * from Location

一旦你把它们转换成代码,用哈弗辛公式计算出每一个纬度/长度与原始值之间的距离,然后排序。

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();
    }

这个问题最初的答案是好的,但是mysql的新版本(mysql 5.7.6上)支持地理查询,所以你现在可以使用内置的功能,而不是进行复杂的查询。

你现在可以这样做:

select *, ST_Distance_Sphere( point ('input_longitude', 'input_latitude'), 
                              point(longitude, latitude)) * .000621371192 
          as `distance_in_miles` 
  from `TableName`
having `distance_in_miles` <= 'input_max_distance'
 order by `distance_in_miles` asc

结果以米为单位返回。因此,如果你想计算KM,只需使用。001而不是。000621371192(这是英里)。

MySql文档在这里

SELECT latitude, longitude, SQRT(
    POW(69.1 * (latitude - [startlat]), 2) +
    POW(69.1 * ([startlng] - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM TableName HAVING distance < 25 ORDER BY distance;

其中[starlat]和[startlng]是开始测量距离的位置。