Sqlite Getting Nearest Locations (With Latitude and Longitude)

SQlite Getting nearest locations (with latitude and longitude)

1) At first filter your SQLite data with a good approximation and decrease amount of data that you need to evaluate in your java code. Use the following procedure for this purpose:

To have a deterministic threshold and more accurate filter on data, It is better to calculate 4 locations that are in radius meter of the north, west, east and south of your central point in your java code and then check easily by less than and more than SQL operators (>, <) to determine if your points in database are in that rectangle or not.

The method calculateDerivedPosition(...) calculates those points for you (p1, p2, p3, p4 in picture).

Sample Image

/**
* Calculates the end-point from a given source at a given range (meters)
* and bearing (degrees). This methods uses simple geometry equations to
* calculate the end-point.
* 
* @param point
*           Point of origin
* @param range
*           Range in meters
* @param bearing
*           Bearing in degrees
* @return End-point from the source given the desired range and bearing.
*/
public static PointF calculateDerivedPosition(PointF point,
            double range, double bearing)
    {
        double EarthRadius = 6371000; // m

        double latA = Math.toRadians(point.x);
        double lonA = Math.toRadians(point.y);
        double angularDistance = range / EarthRadius;
        double trueCourse = Math.toRadians(bearing);

        double lat = Math.asin(
                Math.sin(latA) * Math.cos(angularDistance) +
                        Math.cos(latA) * Math.sin(angularDistance)
                        * Math.cos(trueCourse));

        double dlon = Math.atan2(
                Math.sin(trueCourse) * Math.sin(angularDistance)
                        * Math.cos(latA),
                Math.cos(angularDistance) - Math.sin(latA) * Math.sin(lat));

        double lon = ((lonA + dlon + Math.PI) % (Math.PI * 2)) - Math.PI;

        lat = Math.toDegrees(lat);
        lon = Math.toDegrees(lon);

        PointF newPoint = new PointF((float) lat, (float) lon);

        return newPoint;

    }

And now create your query:

PointF center = new PointF(x, y);
final double mult = 1; // mult = 1.1; is more reliable
PointF p1 = calculateDerivedPosition(center, mult * radius, 0);
PointF p2 = calculateDerivedPosition(center, mult * radius, 90);
PointF p3 = calculateDerivedPosition(center, mult * radius, 180);
PointF p4 = calculateDerivedPosition(center, mult * radius, 270);

strWhere =  " WHERE "
        + COL_X + " > " + String.valueOf(p3.x) + " AND "
        + COL_X + " < " + String.valueOf(p1.x) + " AND "
        + COL_Y + " < " + String.valueOf(p2.y) + " AND "
        + COL_Y + " > " + String.valueOf(p4.y);

COL_X is the name of the column in the database that stores latitude values and COL_Y is for longitude.

So you have some data that are near your central point with a good approximation.

2) Now you can loop on these filtered data and determine if they are really near your point (in the circle) or not using the following methods:

public static boolean pointIsInCircle(PointF pointForCheck, PointF center,
            double radius) {
        if (getDistanceBetweenTwoPoints(pointForCheck, center) <= radius)
            return true;
        else
            return false;
    }

public static double getDistanceBetweenTwoPoints(PointF p1, PointF p2) {
        double R = 6371000; // m
        double dLat = Math.toRadians(p2.x - p1.x);
        double dLon = Math.toRadians(p2.y - p1.y);
        double lat1 = Math.toRadians(p1.x);
        double lat2 = Math.toRadians(p2.x);

        double a = Math.sin(dLat / 2) * Math.sin(dLat / 2) + Math.sin(dLon / 2)
                * Math.sin(dLon / 2) * Math.cos(lat1) * Math.cos(lat2);
        double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
        double d = R * c;

        return d;
    }

Enjoy!

I used and customized this reference and completed it.

Ordering with SQLITE by nearest latitude & longitude coordinates

SELECT * AS distance FROM items ORDER BY ABS(location_lat - lat) + ABS(location_lng - lng) ASC

This should roughly sort the items on distance in MySQL, and should work in SQLite.

If you need to sort them preciser, you could try using the Pythagorean theorem (a^2 + b^2 = c^2) to get the exact distance.

closest matching values sqlite

You have to calculate distance and then order by that distance. I think this will help you.

SELECT Latitude, Longitude, StationName,
(
   3959 *
   acos(cos(radians(UserLatitude)) * 
   cos(radians(Latitude)) * 
   cos(radians(Longitude) - 
   radians(UserLongitude)) + 
   sin(radians(UserLatitude)) * 
   sin(radians(Latitude )))
) AS distance 
 FROM stations  
 ORDER BY distance ASC LIMIT 0,5;

Thanks :)

get nearest coordinate from sqlite database

SQLite has no square root function, but for comparing distances, we can just as well use the square of the distance:

SELECT *
FROM MyTable
ORDER BY min((x1-x0)*(x1-x0) + (y1-y0)*(y1-y0),
             (x2-x0)*(x2-x0) + (y2-y0)*(y2-y0))
LIMIT 1

Android Studio : SQLite Database Query for getting closest location from coordinates

HAVING requires a GROUP BY clause, as per the highlighted part of :-

Sample Image

SQL As Understood By SQLite- SELECT

You want to use WHERE instead of having, so :-

String query = "SELECT COL_NAME, COL_LATITUDE, COL_LONGITUDE, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM TABLE_CAMPSITES WHERE distance < 25 ORDER BY distance LIMIT 0 , 1;"

You will then get an error CURSOR INDEX OUT OF BOUNDS because after the Cursor is generated it is positioned before the first row (-1). You need to move to a row in the Cursor (if any exist) use something like :-

Campsite campsite = new Campsite();
if (cursor.moveToFirst() {
    campsite.setName(cursor.getString(1));
    campsite.setLatitude(cursor.getDouble(6));
    campsite.setLongitude(cursor.getDouble(7));
}
cursor.close();
return campsite;

Noting that if no rows are selected, then the returned Campsite object will be as per when it is constructed (which you should check for).

Find near locations with SQLite and Android

You appear to have your + and - mixed up:

String sql = "LATITUD_COLUMN > (" + (current_latitude + cuadras) + ") AND "
+ "LATITUD_COLUMN < (" + (current_latitude - cuadras) +") AND "
+ "LONGITUD_COLUMN > ("+ (current_longitude - cuadras) +") AND "
+ "LONGITUD_COLUMN < ("+ (current_longitude + cuadras) +")";

should be

String sql = "LATITUD_COLUMN > (" + (current_latitude + cuadras) + ") AND "
+ "LATITUD_COLUMN < (" + (current_latitude - cuadras) +") AND "
+ "LONGITUD_COLUMN > ("+ (current_longitude + cuadras) +") AND "
+ "LONGITUD_COLUMN < ("+ (current_longitude - cuadras) +")";

Note the swapped + and - in the last two lines.

LONGITUD_COLUMN > (-64132838) AND LONGITUD_COLUMN < (-64172838) is always false. :-)