Android Question Using Location2.DistanceTo in an sql query

[woodpecker]

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?

 

[Erel]

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

 

[woodpecker]

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:-

where (lat between ("& lat1 &" - ("& latmile &" * exrange)) AND ("& lat2 &" + ("& latmile &" * exrange)) AND lon between ("& lon1 &" - ("& lonmile &" * exrange)) AND ("& lon2 &" + ("& lonmile &" * exrange)))

It seems to work.

 

[Erel]

It didn't seem efficient

You need to test it. My guess is that it will be fast enough.

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

 

[woodpecker]

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?

 

[RB Smissaert]

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:-

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

 

[Erel]

is there any other reason?

1. Simpler to write and maintain.
2. No need to worry about escaping issues.
3. Safe from SQL injections.

There are no reasons not to use them.

 

[Jorge M A]

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.

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.

 

[Jorge M A]

Btw, I have tested trigonometric functions in SQL Lite and they work very fine.

 

[RB Smissaert]

Btw, I have tested trigonometric functions in SQL Lite and they work very fine.

View attachment 69150

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.

RBS