5

scenario: I have a db with three tables, users, locations, stock;

within users I have: id, name, email

within locations I have: id, place, lati, logi

within stock I have: id, user_id, product, total_count, location_id

I am fairly new and I managed to join all the tables:

$qry = "
SELECT COUNT(id)
  FROM stock
  LEFT
  JOIN users 
   ON stock.user_id= users.id
  LEFT
  JOIN locations
   ON stock.location_id= locations.id ";

But what I am hoping to be able to do is sort it by distance using the lati and logi. So for example, I want everything within 25 miles, sorted closest to furthest.

How can i pull this off giving the scenario?

I did some googling but everything i'm finding show me how I can get the distance between two points, but what I want to be able to use one set of points, and get everything within X miles of it?

Not sure if that all makes sense ?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Keezy
  • 293
  • 4
  • 15
  • What is the formula for calculating the distance between two points of latitude and longitude? – toonice Jan 06 '16 at 05:48
  • you have to apple sin and cos function for this. – kamlesh.bar Jan 06 '16 at 05:51
  • Is the Haversine formula (as per http://www.movable-type.co.uk/scripts/latlong.html) okay? – toonice Jan 06 '16 at 05:51
  • 2
    http://stackoverflow.com/questions/8994718/mysql-longitude-and-latitude-query-for-other-rows-within-x-mile-radius – devpro Jan 06 '16 at 05:54
  • http://stackoverflow.com/questions/4687312/querying-within-longitude-and-latitude-in-mysql – devpro Jan 06 '16 at 05:54
  • http://www.codeproject.com/Articles/12269/Distance-between-locations-using-latitude-and-long its for sql-server but the concept is just the same. Create the function for mysql and use it your query – Alan Francis Jan 06 '16 at 06:32
  • 1
    I guess I might have just been overthinking it. Thanks @devpro the link you provided was able to do the trick. – Keezy Jan 07 '16 at 01:41

1 Answers1

1

I have come up with below simplified formula for calculating this in my application :

pow(CentralLati - LatitudeofCircle, 2) + pow(CentralLongi - LongitudeofCircle, 2) <= 4

where,

CentralLati , CentralLongi => co-ordinates of a point A

LatitudeofCircle, LongitudeofCircle => co-ordinates of the points 25kms around the point A.

You may adjust the above formula to match your database naming conventions.

Hope I understood your requirement correctly.

Yesh
  • 318
  • 3
  • 11