从数据库获取相似的经纬度 [英] Getting similar longitude and latitude from database

问题描述

我正在使用 Phonegap 和 JQuery 创建一个移动应用.

I am creating a mobile app using Phonegap and JQuery.

该应用程序收集用户的经纬度并将其存储在数据库中(使用 PHP 脚本 - 网络服务)，并通过某种算法检测是否有流量.我需要的是从数据库中获取所有彼此接近的经度和纬度值，比如在同一街道/200 米范围内.

The app collects the longitude and latitude of users and stores them in a database (using a PHP script - web service), and with some algorithm it detects whether there is traffic or not. What I need is to grab all the longitude and latitude values from the database which are close to each other, say in the same street/200m range.

假设您在数据库中有一个列表，其中 5 lon/lat 彼此靠近(在同一条街道 A 中)，3 lon/lat 在某些街道 B 中彼此靠近，而其他一些则随机代表用户在其他街道.谁能告诉我如何检测彼此接近的 lon/lat 值?我将这些值作为单独的值存储在 MySQL 数据库中，即一个字段表示经度，另一个字段表示纬度.

Lets say you have a list in the database with 5 lon/lat which are near each other (in the same street A), 3 lon/lat which are near each other in some Street B, and some other representing users randomly in other streets. Can anyone tell me how I can detect the lon/lat values that are near each other? The values I am storing them as separate values in the MySQL database, ie one field for longitude and another for latitude.

一旦我获得彼此靠近的经度/纬度，那么我需要找到街道 A 中的用户和街道 B 中的用户之间的中心点以标记为交通拥堵(基于其他一些检测).

Once I obtain the lon/lat which are near each other, then I would need to find the center point between the users in street A, and users in street B to mark as a traffic congestion (based on some other detections).

推荐答案

您应该研究Haversine 公式.请看下图:

You should look into the Haversine formula. Please see below:

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;

这将返回输入长/纬度对 25 英里范围内的所有记录.

This will return all records that are within 25 miles of the input long / lat pair.

这篇关于从数据库获取相似的经纬度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！