CODEWITHSUNDEEP
phpmysqlmathgeolocationlamp
Chris G
Chris G

Reputation: 7050

MySQL Calculate Geolocation Distance with variable radiuses

I'm building a geolocation advertising platform and I've run into a snag. I can successfully calculate all of the businesses advertising within a given radius to a user using this (page 8). Now, the client wants to change the radiuses of the advertisers which you can think of as "service areas". A basic advertiser will have a service radius of 100 miles, but other companies who are larger or spend more on advertising might have a service area of 250 or 500 miles, for example. With this change, the previous calculation does not work.

How can I take all of these variable radiuses and distances in order to return all of the advertisers that would be valid for a visitor?

To better illustrate my problem, take a look at: enter image description here

Currently we calculate and return all of the advertisers within a 100 mile radius of a user (companies A, B, and C). With the new change, we need to return all of the companies that have a service area that covers the user, which includes company D. Company E has a smaller service area/radius which doesn't cover the user, so that record should not be returned.

The advertiser's table currently looks similar to:

id
name
lat
lng
radius

Upvotes: 1

Views: 457

Answers (1)

Paolo
Paolo

Reputation: 15827

Given lat/lng of two points distance in miles can be calculated, see:

Find distance between two points using latitude and longitude in mysql

So if the visitor position is given (let's define it as visitor_latitude and visitor_longitude) the query that returns all the advertisers in range is:

SELECT *,
       69.0412  * DEGREES(ACOS(COS(RADIANS(lat))
                * COS(RADIANS(visitor_latitude))
                * COS(RADIANS(lng - visitor_longitude))
                + SIN(RADIANS(lat))
                * SIN(RADIANS(visitor_latitude)))) AS distance
FROM advertisers
WHERE distance <= radius

Upvotes: 2

Related Questions