Get locations by GPS position

Question

I have a table called "pois", I want to run SQL query which will show all locations nearest to the phone GPS location in 500 m, I copied the MySQL code somewhere and used it for SQLLite and it does not work, maybe anyone can help me to translate the query to SQLLite query version?

The code is as follows :

Sub GPS_LocationChanged (Location1 As Location)

Loc1 = Location1.Latitude
Loc2 = Location1.Longitude

Dim Cursor As Cursor

Qry = "Select place_id,place_name,lat,lon,poi_catid, ( 6371 * acos( cos( radians( " & Loc1 & " ) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians( " & Loc2 & ") ) + sin( radians( " & Loc1 & " ) ) * sin( radians( lat ) ) ) ) as distance FROM pois HAVING distance < 0.5 ORDER BY distance"
    Cursor = SQL1.ExecQuery(Qry)
    For i = 0 To 15
    Cursor.Position = i
    ToastMessageShow(Cursor.GetString("place_name"),True)
    Next
    Cursor.Close
End Sub

Error message says :

android.database.sqlite.SQLiteException: no such function: acos (code 1):

LS_ᴅᴇᴠ · Answer 1 · 2013-11-14T11:18:36.697

1

SQLite doesn't provide mathematical functions you are using and, IFAIK, you can't add user defined functions in Android.

I would calculate a bounding rectangle, to limit database rows, and query database for entries within this rectangle. Then, for each row, calculate exact distance to filter out entries too far. This way I would also save lots of computation time.

edited Nov 14 '13 at 11:18

answered Nov 14 '13 at 11:04

LS_ᴅᴇᴠ

10,823
1
23
46

How to calculate a bounding rectangle please, I need the code, thank you – Idris Sardi Nov 14 '13 at 11:10
I could research it, but so you can. Just use a function to calculate latitude some distance up and down and longitude some distance left and right. – LS_ᴅᴇᴠ Nov 14 '13 at 11:16

score 0 · Answer 2 · answered Nov 14 '13 at 10:53

0

There's nothing magical about doing the calculations in the database statement -- the computer is still going to have to examine each individual row. (There's database software which is designed to support efficient spatial queries, but SQLite is not one.) You may as well just select all the rows, and do the distance calculation in VB.

answered Nov 14 '13 at 10:53

Sneftel

40,271
12
71
104

I have 149000 records, to select all rows I think it is not good, I have tested it on php/mysql it works fine... – Idris Sardi Nov 14 '13 at 11:04
It doesn't matter if you select all rows or not. Databases aren't magical, and just because you write something in a SQL statement doesn't mean it becomes efficent. They'll be running the same test condition you would be. – Sneftel Nov 14 '13 at 11:28

Get locations by GPS position

2 Answers2