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

Я использую следующий sql-код, чтобы узнать " все " poi, наиболее близкие к заданным координатам, но я хотел бы узнать конкретный poi, а не все из них. Когда я пытаюсь использовать предложение where, я получаю ошибку, и она не работает, и это то, где я в настоящее время застрял, так как я использую только одну таблицу для всех координат всех poi. 

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
HAVING distance < @dist 
ORDER BY distance limit 10;
2 4
sql mysql gps geometry

2 ответа:

Проблема заключается в том, что вы не можете ссылаться на столбец с псевдонимами (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;

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

5

Причина, по которой вы не можете использовать свой псевдоним в предложении 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-это радиус Земли в километрах, поэтому используйте эту константу для измерения расстояния в километрах

Дополнительные сведения см. в Вики про формула Гаверсинуса

3