How to retrieve a set of locations within particular range from user's location from SQLite database

Question

I have some location coordinates stored in my SQLite database table. I want to retrieve the locations within 1 km range from the use's current location. Right now I am fetching all values from database and have written a method to retrieve the values within my range. This leaves me great overhead as my table might contain more than 1000 coordinates.

So i am looking for a better way to do this. Is it possible to directly retrieve locations within my range directly using SELECT query? I found question 1 and question 2 but couldn't find a possible workaround.Any help will be greatly appreciated.

This is my SELECT Query now :

String selectQuery = "SELECT  "+COLUMN_OBJECTID+","+COLUMN_OBJECTNAME_ENGLISH+"," 
                      +COLUMN_OBJECTNAME_ARABIC+","+COLUMN_OBJECTLATITUDE+","+COLUMN_OBJECTLONGITUDE+"," 
                              +COLUMN_OBJECTCATEGORYID+","+COLUMN_OBJECTADDRESS_ENGLISH+","+COLUMN_OBJECTADDRESS_ARABIC 
                              +" FROM " + TABLE_OBJECTLIST+" WHERE "+COLUMN_OBJECTCATEGORYID+"='"+categoryId+"' AND "+ 
                              ((userLat-Double.parseDouble(COLUMN_OBJECTLATITUDE))*(userLat-Double.parseDouble(COLUMN_OBJECTLATITUDE)) 
                                      +(userLong-Double.parseDouble(COLUMN_OBJECTLONGITUDE))*(userLong-Double.parseDouble(COLUMN_OBJECTLONGITUDE))<=range);

Your problem is that you are confusing SQL and Java. Your `COLUMN_*` symbols work only in SQL, `parseDouble()` works only in Java. You should first construct the correct SQL query (in a tool like SQLite Manager or something like that), and then work out how to execute it from Java. — CL., Nov 03 '15 at 08:02
Ok.Then can you help me construct this query with my required condition? — Jas, Nov 03 '15 at 08:26
The answers to the questions you linked to already show the correct SQL. — CL., Nov 03 '15 at 08:33
I'm saving my lat long values as String. So if I change that to double, can I use it in my query? — Jas, Nov 03 '15 at 09:03
@Jas you have mixed Java functions and SQL code ! Just write simple SQL code .. see my example — Varun Verma, Nov 04 '15 at 01:58
I think you can find your solution here: http://stackoverflow.com/questions/3695224/sqlite-getting-nearest-locations-with-latitude-and-longitude — drdawud, Nov 04 '15 at 04:49
@Jas see getNeighbourhoodArea() down below, this function is used to construct the values for the sql-statement (which limits the range). collectPOIs() uses these params to resolve the content from the database. If you have any question feel free to ask. — Martin Pfeffer, Nov 04 '15 at 05:15

Varun Verma · Accepted Answer · 2015-11-04T01:56:00.607

2

You can write a SQL query with following where condition :

where ( (my_lat - LAT)*(my_lat - LAT) + (my_lon - LON)*(my_lon - LON) ) <= 1KM

The idea is that use Pythagoras method to calculate approx location and filter based on that. Here I have not taken the square root because I guess SQL functions in SQLite don't have sqrt.

This is good for approximate calculations ...

I used following SQL and it worked ...

// Table with columns as String and Float
CREATE TABLE "locations" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "lat_string" VARCHAR, "long_string" VARCHAR, "lat_val" FLOAT, "long_val" FLOAT)

// Insert example data
INSERT INTO "locations" VALUES(1,'12.9587926','77.7477416',12.9587926,77.7477416);
INSERT INTO "locations" VALUES(2,'12.9973486','77.6967362',12.9973486,77.69673619999999);
INSERT INTO "locations" VALUES(3,'12.9715987','77.5945627',12.9715987,77.5945627);
INSERT INTO "locations" VALUES(4,'12.9629354','77.7122996',12.9629354,77.7122996);

// Select when column format is string. This works in SQLIte
SELECT id, ( (77.7580827 - long_string)*(77.7580827 - long_string) + (12.9905542 - lat_string)*(12.9905542 - lat_string) ) as dist FROM locations

// Select when column format is float. This works in SQLIte
SELECT id, ( (77.7580827 - long_val)*(77.7580827 - long_val) + (12.9905542 - lat_val)*(12.9905542 - lat_val) ) as dist FROM locations

edited Nov 04 '15 at 01:56

answered Nov 03 '15 at 06:22

Varun Verma

542
4
17

What is LAT and LON? Which lat long values are these? – Jas Nov 03 '15 at 06:26
LAT and LON are your SQLite DB column names where you are stroing geo locations ... my_lat and my_lon are geo location of your point of reference – Varun Verma Nov 03 '15 at 06:28
Ok.Thanks for answering. Let me try this – Jas Nov 03 '15 at 06:30
I tried this but i couldn't retrieve the values from database in SELECT query without using cursor – Jas Nov 03 '15 at 06:51
It gives NumberFormatException – Jas Nov 03 '15 at 06:51
@Jas - Yes, using SQL query you will have to use Cursors because multiple rows can be selected .. NumberFormatException may come if your LAT LONG are not stored as real / float type ... ensure that the type is correct ... – Varun Verma Nov 03 '15 at 06:53
Can u please check the updated question? I have added my SELECT query there. – Jas Nov 03 '15 at 07:05
Another weird problem is that `"+COLUMN_OBJECTCATEGORYID+"='"+categoryId+"'` this computation is working perfectly but location check is giving NumberFormatException – Jas Nov 03 '15 at 07:20
May be there are some values that cannot be converted .. for e.g. null values or wrongly formatted values with some spaces ! Test your query on a smaller set of rows to ensure that query is correct ... Then find out why error comes on other rows – Varun Verma Nov 03 '15 at 07:21
Currently I'm saving my lat long values as String. So if I change that to double, can I use it in my query?I couldn't perform any arithmetic operation inside query – Jas Nov 03 '15 at 09:05
Can u please help me? – Jas Nov 03 '15 at 11:03
How is it even possible to subtract a Float value and a String "long_val" within the query like `(77.7580827 - long_val)`? – Jas Nov 04 '15 at 04:25
Because SQLite converts it before the operator is applied ... Note : Unlike most SQL databases, SQLite does not restrict the type of data that may be inserted into a column based on the columns declared type. Instead, SQLite uses dynamic typing. The declared type of a column is used to determine the affinity of the column only. Read : https://www.sqlite.org/datatype3.html Read : https://www.sqlite.org/lang_createtable.html The above code works for me ... – Varun Verma Nov 04 '15 at 04:32
I'm getting all the rows in my table. Seems like there is some issue with the equation used – Jas Nov 04 '15 at 06:13

score 1 · Answer 2 · answered Nov 04 '15 at 04:57

Here is some code which should give you an idea how to get it working:

public static double[] getNeighbourhoodArea(
            final double lat, final double lng, final int distInMtrs) {

        double[] area = new double[4];

        final double latRadian = Math.toRadians(lat);

        final double degLatKm = 110.574235;
        final double degLngKm = 110.572833 * Math.cos(latRadian);
        final double deltaLat = distInMtrs / 1000.0 / degLatKm;
        final double deltaLong = distInMtrs / 1000.0 / degLngKm;

        final double minLat = lat - deltaLat;
        final double minLng = lng - deltaLong;
        final double maxLat = lat + deltaLat;
        final double maxLng = lng + deltaLong;

        area[0] = minLat;
        area[1] = minLng;
        area[2] = maxLat;
        area[3] = maxLng;

        return area;
    }


    /**
     * search POIs in the neighbourhood
     */
    private PntInrtst collectPOIs(double lat, double lng) {

        if (mDb == null) return Const.NULL_POI;

        Cursor cursorStat = mDb.getPoisInArea(lat, lng, Const.SIDE_LENGTH_GEO_OFFSET);

        double area[] = Logic.getProtectionArea(lat, lng, Const.SIDE_LENGTH_GEO_OFFSET);

        ArrayList<PntInrtst> poiArray = new ArrayList<PntInrtst>();

        PntInrtst poi = Const.NULL_POI;

        if (cursorStat.moveToFirst()) {
            for (int i = 0; i < cursorStat.getCount(); i++) {
                double potLat = cursorStat.getFloat(Const.COL_LA);
                double potLng = cursorStat.getFloat(Const.COL_LO);

                if ((potLat < area[Const.MAX_LAT] && potLat > area[Const.MIN_LAT])
                    && (potLng < area[Const.MAX_LNG] && potLng > area[Const.MIN_LNG])) {

                    poi = Logic.getPoiByCursor(getApplicationContext(), cursorStat);
                    poiArray.add(poi);

                }
                cursorStat.moveToNext();
            } // End "Cursor"
        }
        cursorStat.close();

        // more than once, fire the nearest
        if (poiArray.size() > 1) return closest(poiArray, lat, lng);
        else return poi; // one or null
    }


    /**
     * filter POIs which won't be useful (avoids flooding the cache)
     */
    public Cursor getPoisInArea(double latitude, double longitude, int range) {

        double area[] = getNeighbourhoodArea(latitude, longitude, range);

        String where = "la" + "<" + area[MAX_LAT] +
                       " AND " + "la" + ">" + area[MIN_LAT] +
                       " AND " + "lo" + "<" + area[MAX_LNG] +
                       " AND " + "lo" + ">" + area[MIN_LNG];

        SQLiteDatabase db = mDbHelper.getReadableDatabase();
        return db.query(Const.POI_DB_TABLE, null, where, null, null, null, null);
    }

hey. I'm getting error at places like Logic,PntInrtst,Const as couldn't be resolved — Jas, Nov 04 '15 at 05:34
That is my "PointOfInterest" - a geo-position, containing a set of primitives. You can fetch an object from the database at this point. If you have e.g. filling stations in your data source, then return them. :) — Martin Pfeffer, Nov 04 '15 at 06:41
@ Martin Pfeffer.Can you get me a link of sample for this topic ?(Full sample).Thanks a lot. — , Aug 23 '16 at 11:58
@JoJoRoid I really want to help you out, but I don't got a solid sample. The code I provided has a lot of dependencies in the project, so it is harder to understand my code than the logic itself^^ Let me know what's unclear and I will give my very best to answer your question. — Martin Pfeffer, Aug 23 '16 at 19:09

How to retrieve a set of locations within particular range from user's location from SQLite database

2 Answers2

Linked