计算距我的地理位置最近的地点

发布于 2025-01-06 20:36:31 字数 232 浏览 2 评论 0原文

我在 mysql 数据库中有一系列地方，其中每个地方都有字段：纬度、经度和地址。我会计算距离通过地理定位获得的实际位置最近的地方。我将使用这里的函数：Google 地图 - 查找标记但是如何从 db 中获取所有值并将其放入 javascript 数组中？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

野鹿林 2025-01-13 20:36:32

选项 1：
通过切换到支持GeoIP的数据库来对数据库进行计算。

选项 2：
在数据库上进行计算：如果您使用的是 MySQL，那么以下存储过程应该有助于

CREATE FUNCTION distance (latA double, lonA double, latB double, LonB double)
    RETURNS double DETERMINISTIC
BEGIN
    SET @RlatA = radians(latA);
    SET @RlonA = radians(lonA);
    SET @RlatB = radians(latB);
    SET @RlonB = radians(LonB);
    SET @deltaLat = @RlatA - @RlatB;
    SET @deltaLon = @RlonA - @RlonB;
    SET @d = SIN(@deltaLat/2) * SIN(@deltaLat/2) +
    COS(@RlatA) * COS(@RlatB) * SIN(@deltaLon/2)*SIN(@deltaLon/2);
    RETURN 2 * ASIN(SQRT(@d)) * 6371.01;
END//

选项 3：
如果数据库中有纬度和经度索引，则可以通过使用所选脚本语言（minLat、maxLat、minLong 和 maxLong）计算初始边界框并限制计算量来减少需要计算的计算量。基于该行到条目子集（其中 minLat 和 maxLat 之间的纬度以及 minLong 和 maxLong 之间的经度）。然后MySQL只需要对该行子集执行距离计算。

如果您使用 SQL 语句或存储过程来计算距离，那么 SQL 仍然必须查看数据库中的每条记录，并计算数据库中每条记录的距离，然后才能决定是否返回该行或丢弃它。
由于计算的执行速度相对较慢，因此如果您可以减少需要计算的行集，消除明显超出所需距离的行，那么我们只需执行昂贵的计算即可行数较少。

使用边界框就像首先在地图上绘制一个正方形，其左、右、上、下边缘与中心点的距离适当。然后，我们的圆将在该框内绘制，圆上的最北、最东、最南和最西点接触框的边界。有些行会落在该框之外，因此 SQL 甚至不会尝试计算这些行的距离。它仅计算落在边界框内的那些行的距离，以查看它们是否也落在圆内。

Option 1:
Do the calculation on the database by switching to a database that supports GeoIP.

Option 2:
Do the calculation on the database: if you're using MySQL, so the following stored procedure should help

CREATE FUNCTION distance (latA double, lonA double, latB double, LonB double)
    RETURNS double DETERMINISTIC
BEGIN
    SET @RlatA = radians(latA);
    SET @RlonA = radians(lonA);
    SET @RlatB = radians(latB);
    SET @RlonB = radians(LonB);
    SET @deltaLat = @RlatA - @RlatB;
    SET @deltaLon = @RlonA - @RlonB;
    SET @d = SIN(@deltaLat/2) * SIN(@deltaLat/2) +
    COS(@RlatA) * COS(@RlatB) * SIN(@deltaLon/2)*SIN(@deltaLon/2);
    RETURN 2 * ASIN(SQRT(@d)) * 6371.01;
END//

Option 3:
If you have an index on latitude and longitude in your database, you can reduce the number of calculations that need to be calculated by working out an initial bounding box in your scripting language of choice (minLat, maxLat, minLong and maxLong), and limiting the rows to a subset of your entries based on that (WHERE latitude BETWEEN minLat AND maxLat AND longitude BETWEEN minLong AND maxLong). Then MySQL only needs to execute the distance calculation for that subset of rows.

If you're using a SQL statement or a stored procedure to calculate the distance, then SQL still has to look through every record in your database, and to calculate the distance for every record in your database before it can decide whether to return that row or discard it.
Because the calculation is relatively slow to execute, it would be better if you could reduce the set of rows that need to be calculated, eliminating rows that will clearly fall outside of the required distance, so that we're only executing the expensive calculation for a smaller number of rows.

Using a bounding box is like drawing a square on the map first with the left, right, top and bottom edges at the appropriate distance from our centre point. Our circle will then be drawn within that box, with the Northmost, Eastmost, Southmost and Westmost points on the circle touching the borders of the box. Some rows will fall outside that box, so SQL doesn't even bother trying to calculate the distance for those rows. It only calculates the distance for those rows that fall within the bounding box to see if they fall within the circle as well.

回复收藏 0 原文