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

Question

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?

Answer 1

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.