是否使用“纬度—经度"返回用户位置可用的最近地点? [英] Return Nearest Place available to Users location using Latitude-Longitude?

问题描述

我想在Java或MySQL中实现用户可用的最近位置算法.

I want to implement Nearest Place available to User algorithm in Java or MySQL.

我在 MySQL 数据库中有 Stations 表，该表具有大约100K的经度和纬度的工作站记录.如果用户将他的纬度和经度分别指定为x和y，那么我想返回最近的站点，该站点可从用户的位置获取.

I have Stations table in MySQL database which has around 100K records of Stations with Latitude and Longitude. If User gives his latitude and longitude as x and y, then I want to return nearest stations available from User's location.

因此，请向我建议 Java或MySQL 中可用的任何算法.

So please suggest me any algorithm available in Java or MySQL.

我尝试了以下查询，但似乎性能较慢-

I tried with the following query, but it seems slower in performance -

SELECT *,3956*2*ASIN(SQRT(POWER(SIN((user_lat-abs(st.station_lat))*pi()/180/2 ), 2) + COS(user_lat*pi()/180)*COS(abs(st.station_lat) *pi()/180)*POWER(SIN((user_lon-
st.station_lon)*pi()/180/2 ),2))) AS distance FROM Stations st HAVING distance < 10 ORDER BY distance;

谢谢.

推荐答案

我使用 Haversine公式在以下PHP PDO查询中.它从具有2.7K条记录的表中提取数据，并将它们显示在 MAP 中，使用地理编码1秒钟.如果在数据库范围之外(巴黎25英里)搜索，它将完全默认.

I use the Haversine formula in the following PHP PDO query. It pulls data from a table with 2.7K records and displays them on a MAP in less than 1 second with geocoding.. It defaults cleanly if searching outside range of database(Paris 25 miles).

如果需要公里数而不是英里数，则在公式中使用6357.

Use 6357 in formula if kilometers are required instead of miles.

$stmt = $dbh->prepare("SELECT  name, lat, lng, ( 3959 * acos( cos( radians(?) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( lat ) ) ) ) AS distance FROM gbstn HAVING distance < ? ORDER BY distance LIMIT 0 , 20");
    // Assign parameters
    $stmt->bindParam(1,$center_lat);
    $stmt->bindParam(2,$center_lng);
    $stmt->bindParam(3,$center_lat);
    $stmt->bindParam(4,$radius);

这篇关于是否使用“纬度—经度"返回用户位置可用的最近地点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！