选择落在中心点半径范围内的坐标？

发布于 2024-12-10 04:13:48 字数 258 浏览 0 评论 0原文

我在模式中有一个坐标数据库：

ID:Latitude:Longitude:name:desc

我已经设置了我的谷歌地图应用程序以在屏幕上有效地显示标记。然而，我需要添加另一个功能，用户可以通过该功能查看落在中心点半径范围内的所有指针。

我将如何编写这样的sql语句：

Select all pointers that fall within a 10 mile radius of X & Y

原文

I have a database of coordinates in the schema:

ID:Latitude:Longitude:name:desc

I've set up my google maps application to show the markers effectively on the screen. However I need to add another feature whereby the user can view all pointers that fall within the radius from a central point.

How would I write up a sql statement of the kind:

Select all pointers that fall within a 10 mile radius of X & Y

分享到QQ

分享到微博

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

发布评论

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

以往的大感动 2024-12-17 04:13:48

下面的 SQL 应该可以工作：

SELECT * FROM Table1 a 
WHERE (
          acos(sin(a.Latitude * 0.0175) * sin(YOUR_LATITUDE_X * 0.0175) 
               + cos(a.Latitude * 0.0175) * cos(YOUR_LATITUDE_X * 0.0175) *    
                 cos((YOUR_LONGITUDE_Y * 0.0175) - (a.Longitude * 0.0175))
              ) * 3959 <= YOUR_RADIUS_INMILES
      )

这是基于余弦球面定律，有关该主题的更多详细信息，请查看这篇文章 - http://www.movable-type.co.uk/scripts/latlong.html

The SQL below should work:

SELECT * FROM Table1 a 
WHERE (
          acos(sin(a.Latitude * 0.0175) * sin(YOUR_LATITUDE_X * 0.0175) 
               + cos(a.Latitude * 0.0175) * cos(YOUR_LATITUDE_X * 0.0175) *    
                 cos((YOUR_LONGITUDE_Y * 0.0175) - (a.Longitude * 0.0175))
              ) * 3959 <= YOUR_RADIUS_INMILES
      )

This is based on the spherical law of cosines, for more detailed information on the topic, check out this article - http://www.movable-type.co.uk/scripts/latlong.html

回复收藏 0 原文

梦归所梦 2024-12-17 04:13:48

您可能需要分两步完成此操作。选择中心位于 X、Y 的 20 英里正方形内的点。假设您首先计算正方形的上、左、下、右坐标，您可以从数据库中获取正方形内的所有点：

select * from coordinates where longitude < right and longitude > left and 
latitude < top and latitude > bottom;

第二步是查看点集是否在 10 英里半径的圆内。此时，我很想使用 Google 地图使用 google.maps.geometry.spherical.computeDistanceBetween(from:LatLng, to:LatLng, radius?:号）函数。检查答案是否小于 10 英里。该函数使用地球半径作为默认值。

You probably need to do this in two steps. Select the points that lie within a 20 mile square with it's centre at X,Y. Assuming you calculate the top,left and bottom,right coordinates of the square first you can get all the points inside the square from the database with:

select * from coordinates where longitude < right and longitude > left and 
latitude < top and latitude > bottom;

The second step is to see whether the set of points is inside the 10 mile radius circle. At this point I would be tempted to use Google maps to calculate the distance between the points and the centre of your square using the google.maps.geometry.spherical.computeDistanceBetween(from:LatLng, to:LatLng, radius?:number)function. Check the answer is less than 10 miles. This function uses the radius of the earth as a default.

回复收藏 0 原文

琉璃梦幻 2024-12-17 04:13:48

这个SQL给出了更准确的答案：

SELECT *
 FROM Table1 a
 WHERE 1 = 1
 AND 2 * 3961 * asin(sqrt( power((sin(radians((X - cast(a.latitude as decimal(10,8))) / 2))) , 2) + cast(cos(radians(cast(a.latitude as decimal(18,8)))) * cos(radians(X)) * power((sin(radians((Y - cast(a.long as decimal(18,8))) / 2))) , 2) as decimal(18,10) ))) <= Radius_In_Miles

X =质心纬度
Y = 质心经度

我是在 Redshift 中完成的，所以我必须使用强制转换来防止数值溢出错误。

参考：http://daynebatten.com/2015/09/latitude-longitude -距离-sql/

This SQL gives more accurate answer:

SELECT *
 FROM Table1 a
 WHERE 1 = 1
 AND 2 * 3961 * asin(sqrt( power((sin(radians((X - cast(a.latitude as decimal(10,8))) / 2))) , 2) + cast(cos(radians(cast(a.latitude as decimal(18,8)))) * cos(radians(X)) * power((sin(radians((Y - cast(a.long as decimal(18,8))) / 2))) , 2) as decimal(18,10) ))) <= Radius_In_Miles

X = Latitude of Centroid
Y = Longitude of Centroid

I did it in Redshift, so I had to use cast to prevent numeric value overflow error.

Reference: http://daynebatten.com/2015/09/latitude-longitude-distance-sql/

回复收藏 0 原文

~没有更多了~