MySQL选择范围内的坐标 [英] MySQL select coordinates within range

问题描述

我的数据库中有 100 000 个地址(即记录).

I've in my database 100 000 addresses (that is records).

每一个都有自己的坐标(纬度和经度).

Each one of them has its own coordinates (latitude and longitude).

现在，根据用户的地理位置(纬度和经度)，我想在地图上仅显示 5 英里范围内的地址(使用 Google 地图 v3 API).

Now, given the geo location of the user (latitude and longitude), I want to show on a map only the addresses inside the 5 miles range (using Google maps v3 APIs).

这意味着通常只需显示 100 000 个地址中的 5 或 6 个地址.

This means that usually only 5 or 6 addresses have to be shown out of the 100 000 addresses.

一种解决方案可能是检索所有记录并在 Java 中应用一个公式来计算每个地址的距离，并且仅当它在范围内时才显示它.

One solution could be retrieving all the records and apply a formula in Java to calculate the distance of each address and show it only if it's inside the range.

那会浪费处理能力，因为我需要检索所有记录，而我只需要在地图上显示 5 或 6 个记录.

That would be a waste of processing power, because I would need to retrieve all the records, when I only need to show 5 or 6 of them on the map.

如何在数据库端(MySQL)解决这个问题，以便只返回 5 英里范围内的地址?

How can I solve this problem on the database side (MySQL), in order to return only the addresses in the 5 miles range?

推荐答案

您可以使用所谓的 Haversine 公式.

$sql = "SELECT *, ( 3959 * acos( cos( radians(" . $lat . ") ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(" . $lng . ") ) + sin( radians(" . $lat . ") ) * sin( radians( lat ) ) ) ) AS distance FROM your_table HAVING distance < 5";

其中 $lat 和 $lng 是您点的坐标，而 lat/lng 是您的表格列.以上将列出 5 海里范围内的位置.将 3959 替换为 6371 以更改为公里.

Where $lat and $lng are the coordinates of your point, and lat/lng are your table columns. The above will list the locations within a 5 nm range. Replace 3959 by 6371 to change to kilometers.

此链接可能有用:https://developers.google.com/maps/articles/phpsqlsearch_v3

我没看到你提到了 Java.这个例子是用 PHP 编写的，但查询仍然是你所需要的.

I didn't see you mentioned Java. This example is in PHP but the query is still what you need.

这篇关于MySQL选择范围内的坐标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！