Android Question Library Spazialite and calculating distance

AlpVir

AlpVir

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.
 
Sort by date Sort by votes
gvoulg

gvoulg

Member
Licensed User
Longtime User
Try this syntax for the query
B4X: 
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
 
Upvote 0
You must log in or register to reply here.
Top