我想在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);更多推荐
是否使用“纬度—经度"返回用户位置可用的最近地点?
发布评论