Использование предложения WHERE для поиска POI в диапазоне расстояний от долготы и широты

Проблема в том, что вы не можете ссылаться на столбец с псевдонимом (в данном случае distance) в предложении select или where. Например, вы не можете сделать это:

select a, b, a + b as NewCol, NewCol + 1 as AnotherCol from table
where NewCol = 2

Это приведет к ошибке как в операторе select при попытке обработать NewCol + 1, так и в операторе where при попытке обработать NewCol = 2.

Есть два способа решить эту проблему:

1) Замените ссылку самим вычисленным значением. Пример:

select a, b, a + b as NewCol, a + b + 1 as AnotherCol from table
where  a + b = 2

2) Используйте внешний оператор select:

select a, b, NewCol, NewCol + 1 as AnotherCol from (
    select a, b, a + b as NewCol from table
) as S
where NewCol = 2

Теперь, учитывая ваш ОГРОМНЫЙ и не очень удобный вычисляемый столбец :) Я думаю, вам следует выбрать последний вариант для улучшения читабельности:

SET @orig_lat=55.4058;  
SET @orig_lon=13.7907; 
SET @dist=10;

SELECT * FROM (
  SELECT 
    *, 
    3956 * 2 * ASIN(SQRT(POWER(SIN((@orig_lat -abs(latitude)) * pi()/180 / 2), 2) 
    + COS(@orig_lat * pi()/180 ) * COS(abs(latitude) * pi()/180) 
    * POWER(SIN((@orig_lon - longitude) * pi()/180 / 2), 2) )) as distance 
  FROM geo_kulplex.sweden_bobo
) AS S
WHERE distance < @dist
ORDER BY distance limit 10;

Изменить: как упоминалось ниже @Kaii, это приведет к полному сканированию таблицы. В зависимости от объема данных, которые вы будете обрабатывать, вы можете избежать этого и выбрать первый вариант, который должен работать быстрее.

Причина, по которой вы не можете использовать свой псевдоним в предложении WHERE, заключается в порядке, в котором MySQL выполняет действия:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

При выполнении вашего предложения WHERE значение псевдонима вашего столбца еще не вычислено. Это хорошо, потому что это приведет к потере большого количества производительности. Представьте себе множество (1 000 000) строк — чтобы использовать ваш расчет в предложении WHERE, каждый из этих 1 000 000 должен быть сначала извлечен и рассчитан, чтобы условие WHERE могло сравнить результаты расчета с вашими ожиданиями.

Вы можете сделать это явно либо

• используя HAVING (это причина, по которой HAVING имеет другое имя как WHERE - это другое)
• используя подзапрос, как показано @MostyMostacho (эффективно сделает то же самое с некоторыми накладными расходами)
• поместите сложный расчет в предложение WHERE (фактически даст тот же результат производительности, что и HAVING)

Все они будут работать почти одинаково плохо: сначала извлекается каждая строка, рассчитывается расстояние и, наконец, фильтруется по расстоянию перед отправкой результата клиенту.

Вы можете значительно (!) повысить производительность, смешав простое предложение WHERE для аппроксимации расстояния (фильтрация строк для извлечения в первую очередь) с более точной формулой Евклида в предложении HAVING.

1. найти строки, которые могут соответствовать условию @distance = 10, используя предложение WHERE на основе простого расстояния по осям X и Y (ограничивающая рамка) — это дешевая операция.
2. отфильтруйте эти результаты, используя формулу евклидова расстояния в предложении HAVING — это дорого операция.

Посмотрите на этот запрос, чтобы понять, что я имею в виду:

SET @orig_lat=55.4058;
SET @orig_lon=13.7907; 
SET @dist=10;
SELECT 
    *, 
    3956 * 2 * ASIN(SQRT(POWER(SIN((@orig_lat -abs(latitude)) * pi()/180 / 2), 2)
    + COS(@orig_lat * pi()/180 ) * COS(abs(latitude) * pi()/180) 
    * POWER(SIN((@orig_lon - longitude) * pi()/180 / 2), 2) )) as distance 
FROM geo_kulplex.sweden_bobo
/* WHERE clause to pre-filter by distance approximation .. filter results 
   later with precise euclidian calculation. can use indexes. */
WHERE 
    /* i'm unsure about geo stuff ... i dont think you want a 
       distance of 10° here, please adjust this properly!! */
    latitude BETWEEN (@orig_lat - @dist) AND (@orig_lat + @dist)
    AND longitude BETWEEN (@orig_lon - @dist) AND (@orig_lon + @dist)
/* HAVING clause to filter result using the more precise euclidian distance */
HAVING distance < @dist 
ORDER BY distance limit 10;

Для тех, кому интересна константа:

• 3956 — это радиус Земли в милях, поэтому полученное расстояние измеряется в милях.
• 6371 — это радиус Земли в километрах, поэтому используйте эту константу для измерения расстояния в километрах.

Дополнительную информацию о формуле Хаверсина см. в вики о формуле Хаверсина.