SQL-запрос для расчета близости координат

Я использую эту формулу для расчета расстояния между записями в моей базе данных (My)SQL, которые имеют поля широты и долготы в десятичном формате:

6371 * ACOS(SIN(RADIANS( %lat1% )) * SIN(RADIANS( %lat2% )) + 
COS(RADIANS( %lat1% )) * COS(RADIANS( %lat2% )) * COS(RADIANS( %lon2% ) - 
RADIANS( %lon1% )))

Заменив %lat1% и %lat2% соответствующим образом, его можно использовать в предложении WHERE для поиска записей в пределах определенного радиуса от другой записи, используя его в предложении ORDER BY вместе с LIMIT, можно найти ближайшие x записей и т. д.

Я пишу это в основном как заметку для себя, но улучшения всегда приветствуются. :)

Примечание. Как упоминает Валерион ниже, это рассчитывается в километрах. Замените 6371 на подходящий альтернативный номер, чтобы использовать метры, мили и т. д.


sql geocoding
person deceze♦    schedule 18.09.2008    source источник

Ответы (4)


arrow_upward
7
arrow_downward

Для баз данных (таких как SQLite), которые не поддерживают тригонометрические функции, вы можете использовать теорему Пифагора.

Это более быстрый метод, даже если ваша база данных поддерживает тригонометрические функции, со следующими оговорками:

  • вам нужно хранить координаты в сетке x, y вместо (или также) lat, lng;
  • расчет предполагает «плоскую землю», но это нормально для относительно локальных поисков.

Вот пример из проекта Rails, над которым я работаю (важным является SQL в середине):

class User < ActiveRecord::Base
  ...
  # has integer x & y coordinates
  ...

  # Returns array of {:user => <User>, :distance => <distance>}, sorted by distance (in metres).
  # Distance is rounded to nearest integer.
  # point is a Geo::LatLng.
  # radius is in metres.
  # limit specifies the maximum number of records to return (default 100).
  def self.find_within_radius(point, radius, limit = 100)

    sql = <<-SQL
      select id, lat, lng, (#{point.x} - x) * (#{point.x} - x) + (#{point.y} - y) * (#{point.y} - y) d 
      from users where #{(radius ** 2)} >= d 
      order by d limit #{limit}
    SQL
    
    users = User.find_by_sql(sql)
    users.each {|user| user.d = Math.sqrt(user.d.to_f).round}
    return users
  end
person David    schedule 23.09.2008
comment
Есть ли способ преобразовать долготу и широту в координаты x, y? - person Panagiotis Korros; 20.10.2008

arrow_upward
2
arrow_downward

Я правильно понимаю, что это формула Хаверсина?

person Adam Hopkinson    schedule 18.09.2008
comment
Я нашел это как формулу расстояния по большому кругу, но, глядя на нее, да, вполне может быть. - person deceze♦; 18.09.2008

arrow_upward
1
arrow_downward

Я использую точно такой же метод в приложении для отслеживания транспортных средств и делаю это уже много лет. Он отлично работает. Быстрая проверка какого-то старого кода показывает, что я умножаю результат на 6378137, что, если мне не изменяет память, преобразуется в метры, но я очень давно к нему не прикасался.

Я полагаю, что в SQL 2008 есть новый тип пространственных данных, который, как мне кажется, позволяет проводить такие сравнения, не зная этой формулы, а также позволяет использовать пространственные индексы, которые могут быть интересны, но я не изучал их.

person Valerion    schedule 18.09.2008

arrow_upward
1
arrow_downward

Я использовал это, хотя забыл, где я это взял.

SELECT n, SQRT(POW((69.1 * (n.field_geofield_lat - :lat)) , 2 ) + POW((53 * (n.field_geofield_lon - :lon)), 2)) AS distance FROM field_revision_field_geofield n ORDER BY distance ASC
person 2pha    schedule 23.07.2012