2

I have an app in development that displays a list of users, sorted by some criteria such as newest members (DESC created_at) and members nearest to you (ASC distance). I could easily display this sorted data on my app writing additional code on the PHP file with MySQL statements, like sorting DESC created_at:

$result = mysql_query("SELECT * from users ORDER BY created_at DESC;");

However, things get a little bit confusing if I were to get the distances from the users. The first few things that came to mind was:

  1. How do I translate this into SQL if I do not have a distance column. Can I make a temporary column with value calculated from lat (latitude) column and lng (longitude) column?
  2. After I did that, how exactly am I going to compare a user's distance with multiple users' distances? For the final data to be sorted according to the other users' distances?

I've found a pretty rational equation to get the distance based on lat and lng data here that looks like this:

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) 
AS distance FROM markers 
HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

I do appreciate any better alternatives for this purpose. Thank you very much in advance!

The json objects:

{"users":[{"uid":"16","username":"easy3","picture":"071911.jpg","lat":"4.059912","lng":"100.123804","type_of_tradesman":"Architect, Electrician, Joiner","created_at":"2015-07-23 13:05:40"},
{"uid":"17","username":"easy4","picture":"071911.jpg","lat":"8.059912","lng":"110.008804","type_of_tradesman":"None, Bricklayer, DIY Handyman, Ground Worker, Labourer, Plant Operator","created_at":"2015-07-23 13:15:02"},
{"uid":"18","username":"easy5","picture":"071911.jpg","lat":"8.051232","lng":"100.008804","type_of_tradesman":"Bricklayer, DIY Handyman, Ground Worker, Labourer, Plant Operator","created_at":"2015-07-23 13:23:16"},
{"uid":"19","username":"easy6","picture":"173234.jpg","lat":"8.000912","lng":"116.077805","type_of_tradesman":"Electrician, Joiner, Labourer, Plumber, Scaffolder","created_at":"2015-07-23 13:24:09"}]}
Reynolds R. G. G.
  • 175
  • 2
  • 12

1 Answers1

1

In the example given, 37 and -122 are the coordinates that you are comparing each row with. So, you'll have to pass these to the query (and not use 37/-122) so it can calculate it.

If you'll have a lot of data and are worried about a performance hit this stackoverflow question and answers are worth a read.

Warning: mysql_query is deprecated (and even removed in from PHP >= 7.0.0). Use something like mysqli_query instead.

ddmps
  • 4,350
  • 1
  • 19
  • 34