Android Question SQLite query for calculating distance

[Idris Sardi]

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 sqlite and it does not work, maybe anyone can help me to translate the red highlighted query to sqlite query version ?
Thank you
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):

 

[Harris]

It is my Default Code Module.
I use it for Globals and functions that pertain to the entire app.

 

[dibesw]

It is my Default Code Module.
I use it for Globals and functions that pertain to the entire app.

Thanks, but I have an error because it not found in your GPSServMod.
How must be declared?

 

[Harris]

You won't be able to compile it...

There are many references to DefCM in the GPSServMod.
Some want to Set polling GPS at boot...
Others want to init the SQL DB (a table I built for North American locations)

Like I stated previously:

The posted code is an Example of how to get locations near you from a table. It will not run (or compile) on its' own. It is part of my GPSServmod attached.

What I was trying to show is how to get locations that are nearest to your current Lat/Lon.
When using my table containing 48,000 records, the query returns anything within a 0.5 degree radius.
If no records were found, it increases the radius and tries again until something close is found.

You may use it as example code.

What are you trying to accomplish exactly?

Thanks

 

[achtrade]

Sorry, I fixed it, I had incorrect field types for lon and lat, they were text type, I changed them to REAL now this works fine :

Select place_id,place_name,lat,lon, ( 6371 * acos( cos( radians( -6.845830100000001 ) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians( 107.4736016) ) + sin( radians( -6.845830100000001 ) ) * sin( radians( lat ) ) ) ) as dist FROM pois group by place_name HAVING dist < 3 limit 10;

My problem now is HOW DO WE RUN THIS on basic4android !!!

Idris, is this distance in meter?

 

[derez]

A simple solution without any special apis and libraries:
Set the query to select point which are within a square of 1000 m around the location. It is a question of abs(x-500) and abs(y-500) .
You'll get some more results than are in the circle but you can activate the formula for a circle to screen these results out.
Anyway - the calculation in the original query is (I guess) for great_circle arc distance while for doing 500 m it is completely not required, a simple sqrt((x-x1)*(x-x1) + (y-y1)*(y-y1)) gives the same results (using correct units).