如何根据纬度和纬度得到给定范围内的所有点？

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

场景:我有一个包含三个表,用户,位置,库存的数据库;

within users I have: id, name, email

在我拥有的用户中:id,name,email

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

在我所拥有的位置:id,place,lati,logi

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

我有股票: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.

但我希望能够做的是使用lati和logi按距离对其进行排序。因此,例如,我希望25英里内的所有内容,最接近最远的排序。

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?

我做了一些谷歌搜索,但我发现的一切都告诉我如何获得两点之间的距离,但我希望能够使用一组点,并获得X英里内的所有内容?

Not sure if that all makes sense ?

不确定这一切是否有意义?

1 个解决方案

#1

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

我在我的应用程序中提出了以下简化公式来计算:

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

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

where,

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

CentralLati,CentralLongi => A点的坐标

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

LatitudeofCircle,LongitudeofCircle =>点A周围25kms点的坐标。

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

您可以调整上面的公式以匹配您的数据库命名约定。

Hope I understood your requirement correctly.

希望我能正确理解你的要求。

#1