Android Question Using Location2.DistanceTo in an sql query

woodpecker

Member
Licensed User
Longtime User
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?
 

woodpecker

Member
Licensed User
Longtime User
Why not read all the points into a list and work with the list instead of the database?

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 seems to work.
 
Upvote 0

woodpecker

Member
Licensed User
Longtime User
You need to test it. My guess is that it will be fast enough.

Tip: never write queries like this. Always use parameterized queries:


I thought parameterized queries were only really needed to try and prevent sql injection attacks, is there any other reason?
 
Upvote 0

RB Smissaert

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

If doing this in code (as suggested by Erel) is indeed too slow then you could try using Spatialite. I think that has a built-in Distance function.

RBS
 
Upvote 0

Jorge M A

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

This is good for short distances.
 
Upvote 0

Jorge M A

Well-Known Member
Licensed User
Btw, I have tested trigonometric functions in SQL Lite and they work very fine.

upload_2018-6-21_10-20-40.png
 
Upvote 0
Top