MySQL中经纬度范围内的查询 [英] Querying within longitude and latitude in MySQL

问题描述

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屋！