2010-06-24 17:18
浏览 48

我可以选择MySQL的哪些行与目标坐标相距<x英里? (mysql邻近搜索)

There's a few questions like this on Stack Overflow but because i can't find any quite similar enough for me to follow.

I'm looking to select rows from db which are within x miles of a target.

example table columns:

| city_name(varchar) | lat(decimal) | lon(decimal) |

query effect i'm looking for (either using php or mysql)

$target_lat = ;
$target_lon = ;
"SELECT table.city_name WHERE (,table.lon) LESS THAN 20 MILES FROM ($target_lat,$target_lon")

obviously i don't expect query to take this structure! (i've never used Pi etc in mysql...)

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • dongrenshi0889
    dongrenshi0889 2010-06-24 20:11

    I found this which is brilliant and really easy to understand

    点赞 评论
  • drunk_1996
    红酒泡绿茶 2010-06-24 17:25

    you will find many code snippets in google searching for "mysql radius search"

    they are a little bit too long for copying here.


    点赞 评论
  • duangan6133
    duangan6133 2010-06-24 17:29

    It sounds like you're looking for a geo-spatial package for mySql. There's a function listed here that allows for distance checking between two lat/long points. Just remember that a regular distance calculation won't work with lat/long because of the Earth's curvature. =)

    点赞 评论
  • dsbj66959
    dsbj66959 2010-06-24 17:37

    The Pythagorean theorem states that the square of the length of the hypotenuse of a right triangle is equal to the sum of the squares of the other two sides.

    C^2 = A^2 + B^2
    C = SQRT(A^2 + B^2)

    In this case, the lengths of the other sides are determined by the difference of your lat and long values. Over short distances this will give you a pretty good approximation. However, this still doesn't give you miles, which is a much more difficult conversion since there is not a fixed relationship between lat/long and miles. Instead, the distance between longitudes varies as a function of latitude.

    点赞 评论