SQL: динамически расширять критерии поиска

#php #mysql #sql #geolocation

#php #mysql #sql #геолокация

Вопрос:

у меня есть таблица, содержащая локальные предприятия с координатами (широта, lon), мне нужно получить все предприятия в заданном радиусе, для этой цели я использую запрос, который вычисляет расстояние на лету

 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

теперь мне нужно расширить радиус в случае, если в этой области слишком мало предприятий,
итак, допустим, если в заданном радиусе есть только 10 предприятий, мне нужно динамически расширять радиус, пока я не получу 50

1. Можете ли вы избавиться от части <= radius в предложении WHERE, переместить то, что у вас есть в WHERE, в ПОРЯДОК ПО, а затем установить ОГРАНИЧЕНИЕ 50?

2. но если их больше 50, мне нужно просмотреть их все.

3. Ооо, понятно… В SQL Server я бы, вероятно, использовал рекурсивный CTE для чего-то подобного, но я не думаю, что в MySQL есть подобная конструкция. Что бы я, вероятно, сделал в вашем случае, так это создал хранимую процедуру с временной таблицей и заполнил ее первыми результатами. Если оно меньше 50, то я бы повторял запрос с увеличивающимся радиусом, пока не наберется 50, а затем просто вернул бы эту временную таблицу.

Ответ №1:

Просто вычислите расстояние от заданной точки и используйте это для упорядочивания, затем ограничьте количество результатов до 50.

Было бы немного сложнее, если бы вы хотели получить более 50 результатов, если в этом радиусе сразу больше 50.

В этом случае вы могли бы сделать предложение типа ‘if distance < ? Или число строк < 50’. В MySQL единственный способ создать номер строки — это использовать переменную.

Это будет выглядеть примерно так:

 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 < ?