CODEWITHSUNDEEP
phpmysqlsqlgeolocation
Dima K
Dima K

Reputation: 130

SQL: dynamically expand search criteria

i have a table containing local businesses with coordinates (lat, lon) i need to get all the businesses in a given radius, for that purpose i use a query that calculates a distance on the fly

select b.* from `businesses` as b
where
(
1.609344 * 3956 * 2 * ASIN(
    SQRT( 
            POWER(SIN((lat - b.lat) *  pi()/180 / 2), 2) +
            COS(lat * pi()/180) * COS(b.lat * pi()/180) * 
            POWER(SIN((lng -b.lng) * pi()/180 /-2), 2) 
        )
    )
) <= radius

now i need to expand the radius in a case that there too few businesses in that area, so lets say if in the given radius there are only 10 businesses i need to dynamically expand the radius till i get 50

Upvotes: 3

Views: 105

Answers (1)

wvdz
wvdz

Reputation: 16641

Just calculate the distance from a given point and use this to order on, then limit the number of results to 50.

It would get a little bit more tricky if you want more then 50 results if there are immediately more than 50 in that radius.

In this case you could do a clause like 'if distance < ? OR RowNum < 50'. In MySQL the only way to create a row number is by using a variable.

This would look something like this:

WITH t1 AS
(
    SELECT b.*,
    1.609344 * 3956 * 2 * ASIN(
                SQRT( 
                    POWER(SIN((lat - b.lat) *  pi()/180 / 2), 2) +
                    COS(lat * pi()/180) * COS(b.lat * pi()/180) * POWER(SIN((lng -b.lng) * pi()/180 / 2), 2) 
                )
            )
    ) AS distance
    FROM businesses as b
), t2 AS
(
     SELECT *, @rownum := @rownum + 1 AS row_num
     FROM t1, (SELECT @rownum := 0) r
     ORDER BY distance
)
SELECT * FROM t2
WHERE distance < ? OR row_num < ?

Upvotes: 1

Related Questions