Android Question Library Spazialite and calculating distance

[AlpVir]

I have a database SQLite with 10,000 recond with the following fields :
- name (string)
- latitude (double)
- longitude (double)
Should I select only the first 5 records sorted by distance (closest) to a point where I know the coordinates (lat 44.111 long 7.222).
I saw that the implementation of the Android SQLite does not allow you to use the functions acos and cos.
I turned then to the library Warwound Spazialite as well as its GUI but I have not figured out how to use it. I could not find a function "distance" I know how to use the functions acos, and cos in a similar formula

Select TOP 5 Name, distance As ((radians (ACOS (SIN (radians (44.111)) * Sin (radians (Latitude)) + Cos (radians (44.111)) * COS (radians (Latitude)) * COS (radians (7.222 - Longitude))))) * 69.09) FROM TabWaypoints

Thanks for any help and suggestion.

 

[derez]

You can do it easily without the formula, look here : http://www.b4x.com/android/forum/threads/help-with-some-b4a-calculations.45988/#post-283706

 

[gvoulg]

Try this syntax for the query

dim p1 as latlng 'that is your point
Dim str_poly As StringBuilder
  str_poly.Initialize
str_poly.Append("ST_DISTANCE(ST_GeomFromText('POINT(").Append(p1.Longitude).Append(" ").Append(p1.Latitude)
str_poly.Append(")',4326),Geometry)")
  Dim SpatialiteStatement As Spatialite_Stmt
  Dim SQL_STR As String
SQL_STR="SELECT name,AsText(Geometry)," & str_poly & " AS DIST FROM " & TABLENAME & " ORDER BY DIST ASC LIMIT 5;"
SpatialiteStatement=SpatialiteDatabase.Prepare(SQL_STR)
  Do While  SpatialiteStatement.Step
etc..

To take advantage of spatialite the points in your database should be in geometry format,
that is POINT format not just Lat and Long in different fields
George