What is the ideal data type to use when storing latitude / longitude in a MySQL database?
Учитывая, что я буду выполнять вычисления для пар широта / длинна, какой тип данных лучше всего подходит для использования с базой данных MySQL?
23 ответа
Используйте пространственные расширения MySQL вместе с ГИС.
В основном это зависит от точности, которая вам нужна для вашего местоположения. Используя DOUBLE, вы получите точность 3,5 нм. DECIMAL(8,6)/(9,6) опускается до 16см. Плавание составляет 1,7 м...
Эта очень интересная таблица имеет более полный список: http://mysql.rjweb.org/doc.php/latlng:
Datatype Bytes Resolution
Deg*100 (SMALLINT) 4 1570 m 1.0 mi Cities
DECIMAL(4,2)/(5,2) 5 1570 m 1.0 mi Cities
SMALLINT scaled 4 682 m 0.4 mi Cities
Deg*10000 (MEDIUMINT) 6 16 m 52 ft Houses/Businesses
DECIMAL(6,4)/(7,4) 7 16 m 52 ft Houses/Businesses
MEDIUMINT scaled 6 2.7 m 8.8 ft
FLOAT 8 1.7 m 5.6 ft
DECIMAL(8,6)/(9,6) 9 16cm 1/2 ft Friends in a mall
Deg*10000000 (INT) 8 16mm 5/8 in Marbles
DOUBLE 16 3.5nm ... Fleas on a dog
Надеюсь это поможет.
Google предоставляет начало для завершения решения PHP/MySQL для примера приложения "Локатор магазина" с помощью Google Maps. В этом примере они хранят значения lat / lng как "Float" с длиной "10,6".
Пространственные расширения MySQL - лучший вариант, потому что у вас есть полный список пространственных операторов и индексов. Пространственный индекс позволит вам очень быстро выполнять расчеты на основе расстояния. Пожалуйста, имейте в виду, что по состоянию на 6.0 Пространственное расширение все еще не завершено. Я не опускаю MySQL Spatial, а лишь сообщаю вам о подводных камнях, прежде чем вы слишком далеко продвинетесь в этом.
Если вы имеете дело только с точками и только с функцией DISTANCE, это нормально. Если вам нужно выполнить какие-либо расчеты с полигонами, линиями или буферизованными точками, пространственные операторы не будут давать точных результатов, если вы не используете оператор "связать". См. Предупреждение в верхней части 21.5.6. Отношения, такие как содержит, внутри или пересекаются, используют MBR, а не точную геометрическую форму (то есть эллипс рассматривается как прямоугольник).
Кроме того, расстояния в MySQL Spatial указаны в тех же единицах, что и ваша первая геометрия. Это означает, что если вы используете десятичные градусы, то ваши измерения расстояния в десятичных градусах. Это очень затруднит получение точных результатов, поскольку вы получаете фуртур от экватора.
Когда я сделал это для навигационной базы данных, построенной из ARINC424, я провел большое количество тестов и, оглядываясь на код, использовал DECIMAL(18,12) (на самом деле NUMERIC(18,12), потому что это была firebird).
Плавающие и двойные числа не так точны и могут привести к ошибкам округления, что может быть очень плохо. Я не могу вспомнить, нашел ли я какие-либо реальные данные, у которых были проблемы - но я вполне уверен, что неспособность точно хранить в плавающем или двойном числе может вызвать проблемы
Дело в том, что при использовании градусов или радиан мы знаем диапазон значений - а дробная часть нуждается в наибольшем количестве цифр.
Пространственные расширения MySQL являются хорошей альтернативой, поскольку они следуют модели геометрии OpenGIS. Я не использовал их, потому что мне нужно было сохранять базу данных переносимой.
Зависит от точности, которая вам требуется.
Datatype Bytes resolution
------------------ ----- --------------------------------
Deg*100 (SMALLINT) 4 1570 m 1.0 mi Cities
DECIMAL(4,2)/(5,2) 5 1570 m 1.0 mi Cities
SMALLINT scaled 4 682 m 0.4 mi Cities
Deg*10000 (MEDIUMINT) 6 16 m 52 ft Houses/Businesses
DECIMAL(6,4)/(7,4) 7 16 m 52 ft Houses/Businesses
MEDIUMINT scaled 6 2.7 m 8.8 ft
FLOAT 8 1.7 m 5.6 ft
DECIMAL(8,6)/(9,6) 9 16cm 1/2 ft Friends in a mall
Deg*10000000 (INT) 8 16mm 5/8 in Marbles
DOUBLE 16 3.5nm ... Fleas on a dog
От: http://mysql.rjweb.org/doc.php/latlng
Чтобы подвести итог:
- Самый точный доступный вариант
DOUBLE, - Самый распространенный вид
DECIMAL(8,6)/(9,6),
Начиная с MySQL 5.7, рассмотрите возможность использования пространственных типов данных (SDT), в частности POINT для хранения одной координаты. До версии 5.7 SDT не поддерживает индексы (за исключением 5.6, когда тип таблицы - MyISAM).
Замечания:
- Когда используешь
POINTкласс, порядок аргументов для хранения координат должен бытьPOINT(latitude, longitude), - Существует специальный синтаксис для создания пространственного индекса.
- Самым большим преимуществом использования SDT является то, что у вас есть доступ к функциям пространственного анализа, например, для расчета расстояния между двумя точками (
ST_Distance) и определение, содержится ли одна точка в другой области (ST_Contains).
Основываясь на этой статье вики http://en.wikipedia.org/wiki/Decimal_degrees, соответствующий тип данных в MySQL - Decimal(9,6) для хранения долготы и широты в отдельных полях.
Использование DECIMAL(8,6) для широты (от 90 до -90 градусов) и DECIMAL(9,6) для долготы (от 180 до -180 градусов). 6 знаков после запятой хорошо для большинства приложений. Оба должны быть "подписаны", чтобы учесть отрицательные значения.
Не нужно далеко ходить, по данным Google Maps, лучше всего FLOAT(10,6) для широты и долготы.
Мы храним широту / долготу X 1000 000 в нашей базе данных Oracle как NUMBERS, чтобы избежать ошибок округления с двойными числами.
Учитывая, что широта / долгота до 6-го знака после запятой составляет 10 см, это все, что нам нужно. Многие другие базы данных также хранят широту / долготу до 6-го знака после запятой.
TL;DR
Используйте FLOAT(8,5), если вы не работаете в NASA / военные и не производите навигационные системы самолетов.
Чтобы полностью ответить на ваш вопрос, вам нужно рассмотреть несколько вещей:
Формат
- градусы минуты секунды: 40° 26′ 46″ с.ш. 79° 58′ 56″ з.д.
- градусы десятичные минуты: 40 ° 26,767 'N 79° 58,933' W
- десятичные градусы 1: 40,446° N 79,982° W
- десятичные градусы 2: -32.60875, 21,27812
- Какой-то другой домашний формат? Никто не запрещает вам создавать собственную систему координат, ориентированную на дом, и сохранять ее как курс и расстояние от вашего дома. Это может иметь смысл для некоторых конкретных проблем, над которыми вы работаете.
Итак, первая часть ответа будет такова: вы можете хранить координаты в формате, используемом вашим приложением, чтобы избежать постоянных преобразований назад и вперед и выполнять более простые запросы SQL.
Скорее всего, вы используете Google Maps или OSM для отображения своих данных, а GMaps используют формат "десятичные градусы 2". Так будет проще хранить координаты в одном формате.
точность
Затем вы хотите определить необходимую точность. Конечно, вы можете хранить координаты, такие как "-32.608697550570334,21.278081997935146", но вас когда-нибудь волновали миллиметры при навигации к точке? Если вы не работаете в НАСА и не выполняете спутники, ракеты или траектории самолетов, вам должно быть хорошо с точностью до нескольких метров.
Обычно используемый формат состоит из 5 цифр после точек, что дает точность 50 см.
Пример: расстояние между X, 21.2780818 и X, 21.2780819 составляет 1 см. Таким образом, 7 цифр после точки дают вам точность в 1/2 см, а 5 цифр после точки дают вам точность в 1/2 метра (поскольку минимальное расстояние между точками составляет 1 м, поэтому ошибка округления не может превышать половины). Для большинства гражданских целей этого должно быть достаточно.
Формат в градусах и десятичных минутах (40° 26,767 'N 79° 58,933' W) дает вам точно такую же точность, как 5 цифр после точки
Пространственное хранение
Если вы выбрали десятичный формат, то ваша координата - пара (-32.60875, 21.27812). Очевидно, 2 x (1 бит для знака, 2 цифры для степени и 5 цифр для показателя степени) будет достаточно.
Поэтому здесь я бы хотел поддержать Аликс Акселя из комментариев о том, что предложение Google сохранить его в FLOAT(10,6) действительно лишнее, потому что вам не нужны 4 цифры для основной части (так как знак отделен и широта ограничена до 90 и долгота ограничена до 180). Вы можете легко использовать FLOAT(8,5) для точности 1 / 2м или FLOAT(9,6) для точности 50/2см. Или вы можете даже хранить lat и long в отдельных типах, потому что FLOAT(7,5) достаточно для lat. См. MySQL справочник типов плавания. Любой из них будет как обычный FLOAT и в любом случае будет равен 4 байта.
Обычно пространство не является проблемой в настоящее время, но если вы по какой-то причине действительно хотите оптимизировать хранилище (Отказ от ответственности: не выполняйте предварительную оптимизацию), вы можете сжать lat(не более 91 000 значений + знак) + long(нет более 181 000 значений + знак) до 21 бита, что значительно меньше 2xFLOAT (8 байт == 64 бита)
В совершенно другой и более простой перспективе:
- если вы полагаетесь на Google для показа своих карт, маркеров, полигонов и т. д., тогда пусть Google сделает расчеты!
- вы сохраняете ресурсы на своем сервере и просто сохраняете широту и долготу вместе как одну строку (
VARCHAR), Например: " -0000.0000001, -0000.000000000000001 " (длина 35, и если число имеет более 7 десятичных цифр, оно округляется); - если Google возвращает более 7 десятичных цифр на число, вы в любом случае можете сохранить эти данные в вашей строке, на случай, если в будущем вы захотите обнаружить некоторые блохи или микробы;
- Вы можете использовать их матрицу расстояний или их геометрическую библиотеку для расчета расстояний или обнаружения точек в определенных областях с помощью вызовов, таких как:
google.maps.geometry.poly.containsLocation(latLng, bermudaTrianglePolygon)) - Есть много "серверных" API-интерфейсов, которые вы можете использовать (в Python, Ruby on Rails, PHP, CodeIgniter, Laravel, Yii, Zend Framework и т. д.), которые используют API Карт Google.
Таким образом, вам не нужно беспокоиться об индексации чисел и обо всех других проблемах, связанных с типами данных, которые могут испортить ваши координаты.
Широты варьируются от -90 до +90 (градусов), поэтому DECIMAL(10, 8) подходит для этого
долготы варьируются от -180 до +180 (градусов), поэтому вам нужно DECIMAL(11, 8).
Примечание. Первое число - это общее количество сохраненных цифр, а второе - число после десятичной точки.
Короче: lat DECIMAL(10, 8) NOT NULL, lng DECIMAL(11, 8) NOT NULL
Хотя это не оптимально для всех операций, если вы создаете листы карты или работаете с большим количеством маркеров (точек) только с одной проекцией (например, Mercator, как Google Maps и многие другие фреймворки скользких карт), я обнаружил, что Я называю "Обширную систему координат" очень удобной. По сути, вы сохраняете координаты пикселей x и y при некотором увеличении - я использую уровень масштабирования 23. Это имеет несколько преимуществ:
- Вы выполняете дорогостоящее преобразование пикселей в латы / мергаты один раз, а не каждый раз, когда обрабатываете точку
- Получение координаты тайла из записи при заданном уровне масштабирования занимает один сдвиг вправо.
- Для получения координаты пикселя из записи требуется один сдвиг вправо и одно побитовое AND.
- Сдвиги настолько легки, что практично делать их в SQL, что означает, что вы можете сделать DISTINCT, чтобы вернуть только одну запись на пиксельное местоположение, что сократит количество записей, возвращаемых бэкэндом, что означает меньшую обработку на внешний интерфейс.
Я говорил обо всем этом в недавнем сообщении в блоге: http://blog.webfoot.com/2013/03/12/optimizing-map-tile-generation/
Я очень удивлен некоторыми ответами / комментариями.
С какой стати кто-то захотел бы добровольно "предварительно уменьшить" точность, а затем выполнить вычисления на худших числах? Звучит в конечном итоге глупо.
Если источник имеет 64-битную точность, конечно, было бы глупо добровольно фиксировать масштаб, например. 6 десятичных знаков и ограничивают точность максимум 9 значащими цифрами (что происходит с обычно предлагаемым десятичным форматом 9,6).
Естественно, каждый хранит данные с точностью, которой обладает исходный материал. Единственной причиной снижения точности будет ограниченное пространство для хранения.
- Хранить исходные данные с оригинальной точностью
- Сохраняйте цифры, рассчитанные из источника, в точности, в которой происходит вычисление (например, если код приложения использует удваивается, сохраняйте результаты как двойные)
Десятичный формат 9,6 вызывает явление привязки к сетке. Это должен быть самый последний шаг, если это вообще произойдет.
Я не хотел бы приглашать накопленные ошибки в мое гнездо.
В зависимости от вашего приложения, я предлагаю использовать FLOAT(9,6)
Пространственные ключи дадут вам больше возможностей, но в производственных тестах поплавки намного быстрее, чем пространственные ключи. (0,01 против 0,001 в AVG)
Пространственные функции в PostGIS намного более функциональны (т.е. не ограничены операциями BBOX), чем функции в пространственных функциях MySQL. Проверьте это: текст ссылки
MySQL использует double для всех типов с плавающей запятой... Так что используйте тип double. Использование float приведет к непредсказуемым округленным значениям в большинстве ситуаций
GeoLocationCoordinates возвращает тип данных double, представляющий широту и долготу положения в десятичных градусах. Вы можете попробовать использовать double.
Идеальный тип данных для хранения значений широты и долготы - десятичный (9,6).
Это с точностью примерно 10 см при использовании всего 5 байтов памяти.
например, CAST(123,456789 как десятичное (9,6))
Я предлагаю вам использовать тип данных Float для SQL Server.
Lat Long вычисления требуют точности, поэтому используйте какой-либо тип десятичного типа и сделайте точность как минимум на 2 больше, чем число, которое вы будете хранить для выполнения математических вычислений. Я не знаю о моих типах данных sql, но в SQL-сервере люди часто используют float или real вместо десятичной и сталкиваются с проблемами, потому что это приблизительные числа, а не реальные. Так что просто убедитесь, что тип данных, который вы используете, является истинным десятичным типом, а не плавающим десятичным типом, и у вас все будет хорошо.
FLOAT должен дать вам всю необходимую точность и быть лучше для функций сравнения, чем сохранять каждую координату в виде строки или тому подобного.
Если ваша версия MySQL более ранняя, чем 5.0.3, вам может потребоваться учесть некоторые ошибки сравнения с плавающей запятой.
До MySQL 5.0.3 столбцы DECIMAL сохраняли значения с точной точностью, потому что они представлены в виде строк, но вычисления значений DECIMAL выполняются с использованием операций с плавающей запятой. Начиная с 5.0.3, MySQL выполняет операции DECIMAL с точностью до 64 десятичных цифр, что должно решить наиболее распространенные проблемы неточности, когда речь идет о столбцах DECIMAL.