Какой идеальный тип данных использовать для хранения широты / долготы в базе данных MySQL?

Google предоставляет начало для завершения PHP/MySQL-решения для примера приложения "Store Locator" с Google Maps. В этом примере они сохраняют значения lat/lng как "Float" с длиной "10,6"

http://code.google.com/apis/maps/articles/phpsqlsearch.html

В основном это зависит от точности, необходимой для ваших местоположений. Используя DOUBLE, вы получите точность 3,5 нм. DECIMAL (8,6)/(9,6) сокращается до 16 см. FLOAT составляет 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

Надеюсь, что это поможет.

MySQL Spatial Extensions - лучший вариант, потому что у вас есть полный список пространственных операторов и индексов в вашем распоряжении. Пространственный индекс позволит вам выполнять дистанционные вычисления очень быстро. Имейте в виду, что с 6,0 Пространственное расширение еще не завершено. Я не ставил MySQL Spatial, давая вам знать о подводных камнях, прежде чем вы слишком далеко продвигаетесь вперед.

Если вы имеете дело строго с точками и только функцией DISTANCE, это нормально. Если вам нужно делать какие-либо вычисления с помощью полигонов, линий или буферизованных точек, пространственные операторы не дают точных результатов, если вы не используете оператор "связать". См. Предупреждение в верхней части 21.5.6. Отношения, такие как содержит, внутри или пересекаются, используют MBR, а не точную форму геометрии (т.е. Эллипс рассматривается как прямоугольник).

Кроме того, расстояния в MySQL Spatial находятся в тех же единицах, что и ваша первая геометрия. Это означает, что если вы используете десятичные градусы, то ваши измерения расстояния находятся в десятичных градусах. Это очень затруднит получение точных результатов по мере того, как вы получите фуфур от экватора.

Когда я сделал это для базы данных навигации, построенной из ARINC424, я провел довольно много испытаний и оглядываясь на код, я использовал DECIMAL (18,12) (на самом деле NUMERIC (18,12), потому что это был firebird).

Поплавки и удвоения не так точны и могут привести к ошибкам округления, что может быть очень плохо. Я не могу вспомнить, нашел ли я какие-либо реальные данные, которые имели проблемы, но я вполне уверен, что невозможность аккумулировать память в float или double может вызвать проблемы.

Дело в том, что при использовании градусов или радиан мы знаем диапазон значений - и дробная часть нуждается в большинстве цифр.

MySQL Spatial Extensions являются хорошей альтернативой, потому что они следуют 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).

Основываясь на этой статье wiki http://en.wikipedia.org/wiki/Decimal_degrees#Accuracy соответствующий тип данных в MySQL является десятичным (9,6) для хранения долготы и широты в отдельные поля.

Используйте DECIMAL(8,6) для широты (от 90 до -90 градусов) и DECIMAL(9,6) для долготы (от 180 до -180 градусов). 6 десятичных знаков отлично подходят для большинства приложений. Оба должны быть "подписаны", чтобы допускать отрицательные значения.

Не нужно далеко ходить, согласно Google Maps, лучшим является FLOAT (10,6) для lat и lng.

Мы храним широту/долготу X 1,000,000 в нашей базе данных оракула как ЧИСЛО, чтобы избежать ошибок округления с удвоениями.

Учитывая, что широта/долгота до шестого десятичного знака была 10-сантиметровой точностью, это было все, что нам нужно. Многие другие базы данных также хранят lat/long до шестого знака после запятой.

В совершенно другой и более простой перспективе:

• если вы полагаетесь на 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.

Таким образом вам не нужно беспокоиться об индексах и всех других проблемах, связанных с типами данных, которые могут испортить ваши координаты.

Пока он не является оптимальным для всех операций, если вы делаете картографические плитки или работаете с большим количеством маркеров (точек) только с одной проекцией (например, Mercator, например, Карты Google и многие другие слабые карты), я нашли то, что я называю "Vast Coordinate System", действительно, очень удобно. В основном, вы сохраняете координаты x и y пикселя каким-то образом - увеличенным - я использую уровень масштабирования 23. Это имеет несколько преимуществ:

• Вы делаете дорогостоящие преобразования lat/lng в пиксель пикселя меркатора один раз, а не каждый раз, когда вы обрабатываете точку
• Получение координаты плитки из записи с учетом уровня масштабирования занимает один сдвиг вправо.
• Получение координаты пикселя из записи занимает один правый сдвиг и побитовое значение AND.
• Сдвиги настолько легки, что их можно делать в SQL, что означает, что вы можете сделать DISTINCT, чтобы вернуть только одну запись на пиксель, которая сократит количество записей, возвращаемых бэкэнд, что означает меньше обработка на переднем конце.

Я рассказывал обо всем этом в недавнем сообщении в блоге:  http://blog.webfoot.com/2013/03/12/optimizing-map-tile-generation/

в зависимости от вашего приложения, я предлагаю использовать FLOAT (9,6)

пространственные ключи предоставят вам больше возможностей, но по производственным эталонам поплавки намного быстрее, чем пространственные ключи. (0,01 VS 0,001 в AVG)

Я очень удивлен некоторыми ответами/комментариями.

Почему бы вам не захотеть добровольно "преуменьшить" точность, а затем выполнить вычисления по худшим цифрам? Звучит глупо.

Если источник имеет 64-битную точность, то, конечно, было бы немым, чтобы вовремя исправить масштаб, например. 6 десятичных знаков и ограничить точность до 9 значащих цифр (что происходит с обычно предлагаемым десятичным форматом 9.6).

Естественно, что один хранит данные с точностью, которую имеет исходный материал. Единственной причиной снижения точности было бы ограниченное пространство для хранения.

• Сохранить исходные данные с оригинальной точностью
• Сохранять цифры, рассчитываемые из источника, в точности, которую выполняет расчет (например, если код приложения использует удвоение, сохраняйте результаты как удвоенные)

Десятичный 9.6-формат вызывает феномен привязки к сетке. Это должен быть последний шаг, если это вообще произойдет.

Я не приглашал бы накопленные ошибки в свое гнездо.

MySQL использует double для всех поплавков... Поэтому используйте тип double. Использование float приведет к непредсказуемым округленным значениям в большинстве ситуаций.

Пространственные функции в PostGIS гораздо более функциональны (т.е. не ограничены операциями BBOX), чем функции пространственных функций MySQL. Проверьте это: текст ссылки

TL; DR

Используйте FLOAT (8,5), если вы не работаете в НАСА/военных и не создаете навигационные системы самолетов.

Чтобы полностью ответить на ваш вопрос, вам нужно рассмотреть несколько вещей:

Формат

• градусы минут секунд: 40 ° 26 '46 "N 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 см.

Пример: расстояние 1 см между X, 21.278081 8 и X, 21.278081 9. Таким образом, 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 цифр для экспоненты).

Поэтому я хотел бы поддержать Alix Axel от комментариев, говорящих, что предложение 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 бит)

1. Широты варьируются от -90 до +90 (градусы), поэтому DECIMAL (10, 8) подходит для этого

2. долготы варьируются от -180 до +180 (градусы), поэтому вам нужно DECIMAL (11, 8).

Примечание. Первое число - это общее количество сохраненных цифр, а второе - число после десятичной точки.

Короче: lat DECIMAL(10, 8) NOT NULL, lng DECIMAL(11, 8) NOT NULL

Расчеты Lat Long требуют точности, поэтому используйте тип десятичного типа и сделайте точность не менее чем на 2 выше, чем число, которое вы будете хранить для выполнения математических вычислений. Я не знаю о моих типах данных sql, но в SQL-сервере люди часто используют float или real вместо десятичного числа и попадают в неприятности, потому что это оценочные числа, которые не являются реальными. Поэтому просто убедитесь, что тип данных, который вы используете, является истинным десятичным типом, а не плавающим десятичным типом, и вы должны быть в порядке.

A FLOAT должен предоставить вам всю необходимую точность и быть лучше для функций сравнения, чем хранить каждую координату в виде строки или тому подобного.

Если ваша версия MySQL более ранняя, чем 5.0.3, вам, возможно, придется учитывать некоторые ошибки сравнения с плавающей запятой.

До MySQL 5.0.3 столбцы DECIMAL хранят значения с точной точностью, потому что они представлены в виде строк, но вычисления значений DECIMAL выполняются с использованием операций с плавающей запятой. Начиная с версии 5.0.3 MySQL выполняет операции DECIMAL с точностью до 64 десятичных цифр, что должно решить наиболее распространенные проблемы неточности, когда дело доходит до столбцов DECIMAL.