在MySQL中查询经度和纬度 [英] Querying within longitude and latitude in MySQL

问题描述

在询问特定的代码示例之前，我只想问一下是否可以像这样的伪代码进行查询：

从表中选择项/ lon = -within特定经纬度x英里 -

这是可行的吗？或者我必须跳过一些箍？
可以推荐的任何好方法都会很棒！

解决方案

您应该搜索Haversine公式，良好的开端可能是：

• 创建使用PHP，MySQL& Google地图 - 请参阅'使用MySQL查找地点'部分


b
$ b

下面是SQL语句，它将查找距离37，-122坐标25英里范围内最近的20个位置。它根据该行的纬度/经度和目标纬度/经度计算距离，然后仅请求距离值小于25的行，按距离排序整个查询，并将其限制为20个结果。要以公里而不是英里搜索，请将3959替换为6371.
$ b

  SELECT id，（3959 * acos（cos （弧度（37））* cos（弧度（lat））* cos（弧度（lng） - 弧度（-122））+ sin（弧度（37））* sin（弧度（lat））））距标记的距离具有距离< 25 ORDER BY距离LIMIT 0,20; 
  




Before asking for specific code examples, I just wanted to ask whether it is possible to make a query something like this pseudo code:

select items from table where lat/lon = -within x miles of a certain lat/lon point-

Is that doable? Or do I have to jump through some hoops? Any good approaches that could be recommended would be great!

解决方案

You should search for the Haversine formula, but a good start could be:

• Creating a Store Locator with PHP, MySQL & Google Maps - See Section 'Finding Locations with MySQL'
• Geo/Spatial Search with MySQL

Citing from the first url:

Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

这篇关于在MySQL中查询经度和纬度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！