Android Question Library Spazialite and calculating distance


Well-Known Member
Licensed User
Longtime User
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.


Licensed User
Longtime User
Try this syntax for the query
dim p1 as latlng 'that is your point
Dim str_poly As StringBuilder
str_poly.Append("ST_DISTANCE(ST_GeomFromText('POINT(").Append(p1.Longitude).Append(" ").Append(p1.Latitude)
  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;"
  Do While  SpatialiteStatement.Step
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
Upvote 0