duancong7573
2016-04-11 21:07 阅读 40
已采纳

返回基于MYSQL中lat / lng匹配距离查询的行数?

I'm using this query to find providers within a certain distance, I know it's not an ideal way to do this and it only returns a semi-accurate distance as the crow flies, but the distance is going to have to be calculated far too many times to use any third party API's.

SELECT ( 3959 * acos( cos( radians('28.65') ) * cos( radians( lat ) ) * cos( radians( lon ) - radians('-81.21') ) + sin( radians('28.65') ) * sin( radians( lat ) ) ) ) AS distance FROM providers HAVING distance < 10

That works fine and returns all providers within 10 miles, however I want to return a count of the results, something like this:

SELECT COUNT(providers.id) AS rowcount, ( 3959 * acos( cos( radians('28.65') ) * cos( radians( lat ) ) * cos( radians( lon ) - radians('-81.21') ) + sin( radians('28.65') ) * sin( radians( lat ) ) ) ) AS distance FROM providers HAVING distance < 10

That, unfortunately, does not work.

Is there any other way to do this within the query or do I have to count the rows afterwards with PHP?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    doumao8803 doumao8803 2016-04-11 23:23

    You can use a subquery:

    SELECT COUNT(*) FROM (
        SELECT (3959 * ACOS(COS(RADIANS('28.65')) * COS(RADIANS(`lat`)) * COS(RADIANS(`lon`) - RADIANS('-81.21')) + SIN(RADIANS('28.65')) * SIN(RADIANS(lat)))) AS distance
        FROM providers
        HAVING distance < 10
    )
    

    The inner query is your original one, and then the outer query counts the results.

    As mentioned in comments, you should alter your database to use MySQL's spatial data types, which will allow you to do this automatically and with indexes to make it faster.

    点赞 评论 复制链接分享

相关推荐