PHP/MySQL-从实际gps坐标查找半径500米以内的所有项目 [英] PHP / MySQL - Find all items in 500 meters radius from actual gps coordinates

问题描述

我正在基于GPS坐标比较同一位置的价格. 所以我有一个坐标为的项目:

I'm doing some compare of prices in same location based on GPS coordinates. So e.g. I've got an item with coordinates:

lat: 45.815005 
lng: 15.978501

我想在我的MySQL数据库中搜索每个项目，例如那个地方周围500米. (它不必是圆形的，X上只有500米，Y上只有500米). Lat和lng在我的数据库中存储为单独的列类型float(10,6)

I want to search in my MySQL database for each items which are e.g. 500 meters around that place. (it doesn't need to be circle, just 500 meters on X and 500 meters on Y both ways). Lat and lng are stored as a separate columns type float(10,6) in my DB

我知道，要计算精确的lng和lat并不容易，但是如果我错过了每个站点几米的地方，我很好.

I am aware of the fact it's not easy to calculate exact lng and lat but I'm fine if I miss few meters each site.

这是一个非常复杂的问题，但是我很感谢任何能够启动我的建议.

This is pretty complex question but I would be thankful for any advise which will kickoff my start.

推荐答案

考虑到hasrsine公式，计算两个坐标之间的距离实际上并不那么困难.

Calculating the distance between two coordinates isn't actually that difficult given the haversine formula.

SELECT 
  -- stuff here
  , ( 6371000 * acos( cos( radians(45.815005) ) * cos( radians( stuff.lat ) ) * cos( radians( stuff.lng ) - radians(15.978501) ) + sin( radians(45.815005) ) * sin(radians(stuff.lat)) ) ) AS distance 
FROM 
  stuff
HAVING 
  distance < 500

参考答案

与原始答案相比有必要更改:

Necessary changes from the original answer:

1. 原始答案中提供的常数提供了英里或公里的值.我已经更改了此处的常数以使用仪表.

1. The constant offered in the original answer supplied the values for miles or kilometers. I've changed the constant here to work with meters.

常量已更改为使用您的坐标.您可能需要进一步调整查询以使用这些参数而不是常量.

The constants have changed to use your coordinates. You might want to adapt the query a little further to make those parameters instead of constants.

having表达式略有变化，以反映您对500米的渴望.同样，这可能是您要参数化的内容.

The having expression changed a little to reflect your desire for 500 meters. Again, this might be something you want to parameterize.

这篇关于PHP/MySQL-从实际gps坐标查找半径500米以内的所有项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！