我怎样才能让一个SELECT语句顺序，如方程式？ （由最短距离定做） [英] How can I make a Select statement to Order By an equation? (To order by shortest distance)

问题描述

我的工作需要显示最近的酒店（例如）用户的位置的应用程序。 SQLite数据库包含了经度和纬度以及所有的酒店。我搜索后发现，要做到这一点最简单的方法是在select语句写某个公式中的排序。

I'm working on an application that needs to display the nearest hotels (for example) to the user's location. A SQLite database contains all hotels along with their Longitudes and Latitudes. I searched and found that the easiest way to do this is to write a certain equation in the "Order By" in the select statement.

Cursor cursor = database.query(MySQLiteHelper.TABLE_Hotel, hotelsNames, null, null, null, null, ((lat - Double.valueOf(hotelsLat[0].trim()).doubleValue()) * (lat - Double.valueOf(hotelsLat[0].trim()).doubleValue()) +
            (lon - Double.valueOf(hotelsLong[0].trim()).doubleValue()) * (lon - Double.valueOf(hotelsLong[0].trim()).doubleValue()));

注：hotelsLong和hotelsLat都是String []包含从数据库的经度和纬度

Note: The hotelsLong and hotelsLat are String[] containing the longitudes and latitudes from the database.

这是我做的select语句，问题是，有，说是select语句的参数必须是一个错误（字符串，字符串[]，字符串，字符串[]，字符串，字符串，字符串），但我已经做了是（字符串，字符串[]，NULL，NULL，NULL，NULL， 双击的）

This is the select statement I made, the problem is that there is an error that says that the select statement's parameters must be (String, String[], String, String[], String, String, String) but what I've done is (String, String[], null, null, null, null, double)

我应该怎么办呢？以及如何将公式的结果排序的结果？

How should I do it? and how will it order the results by the equation's results?

推荐答案

首先，你的距离计算似乎不正确。请参阅<一个地点类的源href=\"http://grep$c$c.com/file/repository.grep$c$c.com/java/ext/com.google.android/android/2.2_r1.1/android/location/Location.java#Location.distanceBetween%28double,double,double,double,float%5B%5D%29\"相对=nofollow> distanceBetween（）方法。我建议你​​使用这个或<一个href=\"http://developer.android.com/reference/android/location/Location.html#distanceTo%28android.location.Location%29\"相对=nofollow> distanceTo（）同一类的。

First, your distance calculation seems incorrect. See source of Location class for distanceBetween() method. I advise you to use this or distanceTo() of the same class.

关于适配器使用。 的CursorAdapter 是，如果你的数据来自数据库中使用，否则，使用 BaseAdapter 的一些后裔。你有两个选择。保存在SQL表中的当前距离作为新列（每次新位置收到或用户自定义搜索的位置），并比其排序或使用 ArrayAdapter 为你的的ListView 。

About the adapter to use. CursorAdapter is used if your data comes from database, otherwise some descendant of BaseAdapter is used. You have two options. Save the current distance in the sql table as a new column (everytime the new location is received or the user defined a location to search) and than sorting by it or use the ArrayAdapter for your ListView.

我将描述第二个选项，如果根据用户位置和位置更新相当频繁，因为它不访问数据库每次被接收新的位置被显示宾馆应该平滑，但在另一方面，它消耗更多的内存，因为酒店都存储为对象：

I'll describe the 2nd option, which should be smoother if hotels are being displayed according to the user location and location updates are quite frequent as it doesn't access database everytime new location is received, but on the other hand it consumes more memory because hotels are stored as objects:

创建的AsyncTask 来获取包含酒店A 光标。遍历光标并填写酒店的列表：

Create AsyncTask to get a Cursor containing hotels. Traverse cursor and fill a list of hotels:

@Override
protected List<Hotel> doInBackground(Void... unused) {
    final Cursor c = database.query(getting hotels);
    if (c == null) {
        return null;
    }
    List<Hotel> hotels = new ArrayList<Hotel>();
    try {
        for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
            Hotel hotel = new Hotel();
            hotel.fillFromCursor(c); // fills data from cursor
            hotels.add(hotel);
        }
    } finally {
        c.close();
    }
    return hotels;
}

@Override
protected void onPostExecute(List<Hotels> hotels) {
    if (hotels != null) {
        mHotelsAdapter.clear();
        for (Hotel h : hotels) {
            mHotelsAdapter.add(h);
        }
        // mLocation is our current location, if we have one, set the distance, see below
        mHotelsAdapter.updateDistance(mLocation);
    }
}

mHotelsAdapter是您的ListView的适配器。适配器包含 updateDistance（）方法，它应该每次调用所需的位置在 onLocationChanged改变（如（地点）您LocationListener的的）。该方法更新的距离和排序项。

mHotelsAdapter is an adapter for your ListView. Adapter contains updateDistance() method which should be called everytime the desired location is changed (like in onLocationChanged(Location location) of your LocationListener). The method updates the distance and sorts items.

mHotelsAdapter = new HotelAdapter(this);
getListView().setAdapter(mHotelsAdapter);
...

public class HotelsAdapter extends ArrayAdapter<Hotel> {
    ...
    public void updateDistance(Location location) {
        if (location != null) {
            for (int i = 0; i < getCount(); i++) {
                Hotel hotel = getItem(i);
                hotel.setDistance(location);
             }
             sort(mComparator);
             notifyDataSetChanged();
        }
    }
    @Override
    public View getView(int position, View convertView, ViewGroup parent) {
        // implement getView
    }

    private static final Comparator<Hotel> mComparator = new Comparator<Hotel>() {
        @Override
        public int compare(Hotel lhs, Hotel rhs) {
            if (lhs.getDistance() > rhs.getDistance()) {
                return 1;
            } else if (lhs.getDistance() < rhs.getDistance()) {
                return -1;
            } else {
                return 0;
            }
        }
    };
    ...
}

和终于在这里是酒店类的<​​code> setDistance（）法，其中规定了酒店的距离以作为参数的位置：

and finally here is the setDistance() method of the Hotel class, which sets the distance of the hotel to the location given as a parameter:

public void setDistance(Location location) {
    float results[] = new float[1];
    Location.distanceBetween(location.getLatitude(), location.getLongitude(),
        getLatitude(), getLongitude(), results);
    mDistance = results[0];
}

这篇关于我怎样才能让一个SELECT语句顺序，如方程式？ （由最短距离定做）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！