I have read all the threads I can find on this but stuggling to solve it...
I have an sqlite db with about 50000 lat, lon (dblat and dblon) coordinates plus a third rangeextension (dbrangeextension) column in miles.
I want to return the rows where the dblat, dblon is within (x miles + rangeextension) of my gps coordinates, ie something like:-
select * from table1 where Location2.DistanceTo(dblocation) < 'x + dbrangeextension'
To do this I would need to initialise the db locations but I don't know the db locations before the query and can't see a way to initialise the location in the query, so I'm a bit stuck??
Becuase sqlite doesn't support acos, cos etc I can't do a haversine type lookup, can anyone help with a way to do this in a query please?
It didn't seem efficient, I think I may have solved it, I pre- calculated the lat and lon variation in degrees for one mile then expanded my between lat and lon points in the query like this:-
B4X:
where (lat between ("& lat1 &" - ("& latmile &" * exrange)) AND ("& lat2 &" + ("& latmile &" * exrange)) AND lon between ("& lon1 &" - ("& lonmile &" * exrange)) AND ("& lon2 &" + ("& lonmile &" * exrange)))
It didn't seem efficient, I think I may have solved it, I pre- calculated the lat and lon variation in degrees for one mile then expanded my between lat and lon points in the query like this:-
B4X:
where (lat between ("& lat1 &" - ("& latmile &" * exrange)) AND ("& lat2 &" + ("& latmile &" * exrange)) AND lon between ("& lon1 &" - ("& lonmile &" * exrange)) AND ("& lon2 &" + ("& lonmile &" * exrange)))
If I am understanding your need well, maybe this query may give you some clue. The rangeextension I do not know how you use it.
The query is in MS SQL Server syntax, but I do not think you have any difficulty in passing it to SQL Lite.
B4X:
SELECT D.* FROM
(SELECT TOP (30) locId,
3959 * ACOS(COS(RADIANS(yourLatitud)) * COS(RADIANS(dbLatitud))
* COS(RADIANS(dbLongitude) - RADIANS( yourLongitud ))
+ SIN(RADIANS(yourLatitud)) * SIN(RADIANS(dbLatitud))) AS distance,
country, region, city,
postalCode,latitud,longitude
FROM yourTableLocation
ORDER BY distance) D
WHERE distance <= YourDesireDistance
ORDER BY distance
--To search by kilometers instead of miles, replace 3959 with 6371.
They must be application UDF's, don't think SQLite has these functions.
One way to do it with pure SQLite would be lookup tables. Should be quite quick with indexes.