1

I am using SQLite in java to connect to a database, in the database users will pass some data in and SQLite will return a list of 5 results based on that.

I am unable to figure out a way of teh code using two variables to bring back the closest matching results

The program it self will use Longitude and Latitude to search the database for the 5 closest train stations, and order them nearest to farthest away, my issue is that the Long and Lat will be dynamically input through an android app. so i cannot hard code the Long and Lat

this is my current code

SELECT Latitude, Longitude, StationName FROM stations WHERE Latitude like '51%' AND Longitude like '-3%' LIMIT 5

as you can see for testing the rest of teh code i have used a wildcard and like option to give me teh appearance of it working, however this only gives me an entire list of all of the trainstations in alphabetical order, a realistic lat would be more like 53.4198 and a train station closest to that one could be at 53.4183.

help please

cozbouk
  • 17
  • 1
  • 6
  • For that sort of thing you're better off using something like the SQLite GIS extension SpatiaLite . – pvg May 02 '17 at 04:28
  • 1
    There are better ways to do this see for example http://stackoverflow.com/questions/25170175/query-database-values-based-on-users-location. If you have larger distances you will need to use better formulas, taking the spherical shape of the earth into account. – Henry May 02 '17 at 04:28

1 Answers1

-1

You have to calculate distance and then order by that distance. I think this will help you.

SELECT Latitude, Longitude, StationName,
(
   3959 *
   acos(cos(radians(UserLatitude)) * 
   cos(radians(Latitude)) * 
   cos(radians(Longitude) - 
   radians(UserLongitude)) + 
   sin(radians(UserLatitude)) * 
   sin(radians(Latitude )))
) AS distance 
 FROM stations  
 ORDER BY distance ASC LIMIT 0,5;

Thanks :)

Shohan Ahmed Sijan
  • 4,391
  • 1
  • 33
  • 39
  • If the DBMS does not support trigonometric functions, one can still use a similar method by not only storing lat and long, but also their sin and cos values. – Henry May 02 '17 at 08:39
  • So, what if trigonometric functions not work then how you do this? @Henry – Shohan Ahmed Sijan May 03 '17 at 07:15
  • Don't know what to add to my previous comment. Store lat, sinlat, coslat, and similar for longitude in the db table, then use the same formula. Note, for just ordering the records you don't need the `acos` as it is monotonous over the interesting interval.. – Henry May 03 '17 at 08:03
  • Wow... Good approach @Henry – Shohan Ahmed Sijan May 03 '17 at 08:29