Jakiego typu danych najlepiej użyć do przechowywania szerokości i długości geograficznej w bazie danych MySQL?

Biorąc pod uwagę, że będę wykonywać obliczenia dla par lat / long, jaki typ danych najlepiej nadaje się do użycia z bazą danych MySQL?

Używaj rozszerzeń przestrzennych MySQL z GIS.

Google zapewnia początek ukończenia rozwiązania PHP / MySQL dla przykładowej aplikacji „Store Locator” z Google Maps. W tym przykładzie przechowują wartości lat / lng jako „Float” o długości „10,6”

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

Zasadniczo zależy to od precyzji potrzebnej do lokalizacji. Używając DOUBLE uzyskasz precyzję 3,5 nm. DECIMAL (8,6) / (9,6) spada do 16 cm. FLOAT ma 1,7 m ...

Ta bardzo interesująca tabela ma bardziej kompletną listę: 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

Mam nadzieję że to pomoże.

Rozszerzenia przestrzenne MySQL są najlepszą opcją, ponieważ masz do dyspozycji pełną listę operatorów przestrzennych i indeksów. Indeks przestrzenny pozwoli bardzo szybko wykonać obliczenia oparte na odległości. Należy pamiętać, że od wersji 6.0 rozszerzenie przestrzenne jest nadal niekompletne. Nie odkładam MySQL Spatial, tylko informuję o pułapkach, zanim dojdziesz do tego zbyt daleko.

Jeśli masz do czynienia wyłącznie z punktami i tylko z funkcją ODLEGŁOŚCI, to jest w porządku. Jeśli konieczne jest wykonanie jakichkolwiek obliczeń za pomocą wielokątów, linii lub punktów buforowanych, operatory przestrzenne nie zapewniają dokładnych wyników, chyba że użyje się operatora „powiązania”. Zobacz ostrzeżenie na górze 21.5.6 . Relacje, takie jak zawiera, wewnątrz lub przecina, wykorzystują MBR, a nie dokładny kształt geometrii (tzn. Elipsa jest traktowana jak prostokąt).

Ponadto odległości w MySQL Spatial są w tych samych jednostkach, co Twoja pierwsza geometria. Oznacza to, że jeśli używasz stopni dziesiętnych, wówczas twoje pomiary odległości są w stopniach dziesiętnych. Utrudni to uzyskanie dokładnych wyników, gdy zdobędziesz jeszcze więcej od równika.

Kiedy zrobiłem to dla bazy danych nawigacji zbudowanej z ARINC424, przeprowadziłem sporo testów i patrząc wstecz na kod, użyłem DECIMAL (18,12) (Właściwie NUMERYCZNY (18,12), ponieważ był firebird).

Liczba zmiennoprzecinkowa i liczba podwójna nie są tak precyzyjne i mogą powodować błędy zaokrąglania, co może być bardzo złe. Nie pamiętam, czy znalazłem jakieś rzeczywiste dane, które miałyby problemy - ale jestem całkiem pewien, że niemożność dokładnego przechowywania w liczbach zmiennoprzecinkowych lub podwójnych może powodować problemy

Chodzi o to, że używając stopni lub radianów znamy zakres wartości - a część ułamkowa potrzebuje jak najwięcej cyfr.

W MySQL Spatial Rozszerzenia są dobrą alternatywą, ponieważ idą OpenGIS geometrii modelu . Nie korzystałem z nich, ponieważ musiałem mieć przenośną bazę danych.

Zależy od wymaganej precyzji.

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

Od: http://mysql.rjweb.org/doc.php/latlng

Podsumować:

• Najbardziej precyzyjna dostępna opcja to DOUBLE.
• Najczęściej stosowanym typem jest DECIMAL(8,6)/(9,6).

Począwszy od MySQL 5.7 , rozważ użycie typów danych przestrzennych (SDT), szczególnie POINTdo przechowywania pojedynczej współrzędnej. Przed wersją 5.7 SDT nie obsługuje indeksów (z wyjątkiem 5.6, gdy typem tabeli jest MyISAM).

Uwaga:

• Podczas korzystania z POINTklasy kolejność argumentów do przechowywania współrzędnych musi być następująca POINT(latitude, longitude).
• Istnieje specjalna składnia do tworzenia indeksu przestrzennego .
• Największą zaletą korzystania z SDT jest to, że masz dostęp do funkcji analiz przestrzennych , np. Obliczania odległości między dwoma punktami ( ST_Distance) i określania, czy jeden punkt jest zawarty w innym obszarze ( ST_Contains).

Na podstawie tego artykułu wiki http://en.wikipedia.org/wiki/Decimal_degrees#Accuracy odpowiednim typem danych w MySQL jest liczba dziesiętna (9,6) do przechowywania długości i szerokości geograficznej w osobnych polach.

Użyj DECIMAL(8,6)dla szerokości geograficznej (90 do -90 stopni) i DECIMAL(9,6)długości geograficznej (180 do -180 stopni). W przypadku większości aplikacji wystarczy 6 miejsc po przecinku. Oba powinny być „podpisane”, aby uwzględnić wartości ujemne.

Według Google Maps nie trzeba daleko się posuwać, najlepiej FLOAT (10,6) dla lat i lng.

Przechowujemy szerokość / długość geograficzną X 1 000 000 w naszej bazie danych Oracle jako LICZBY, aby uniknąć błędów zaokrąglania z podwójnymi.

Biorąc pod uwagę, że szerokość / długość geograficzna do 6. miejsca po przecinku wynosiła 10 cm dokładności, to było wszystko, czego potrzebowaliśmy. Wiele innych baz danych również przechowuje długość / szerokość do 6 miejsc po przecinku.

W zupełnie innej i prostszej perspektywie:

• jeśli polegasz na Google w wyświetlaniu swoich map, znaczników, wielokątów, cokolwiek, to pozwól, aby obliczenia zostały wykonane przez Google!
• oszczędzasz zasoby na serwerze i po prostu przechowujesz szerokość i długość geograficzną razem jako jeden ciąg ( VARCHAR), np .: „ -0000.0000001, -0000.000000000000001 ” (długość 35, a jeśli liczba ma więcej niż 7 cyfr dziesiętnych, wówczas zostanie zaokrąglona);
• jeśli Google zwróci więcej niż 7 cyfr dziesiętnych na liczbę, możesz mimo to uzyskać te dane zapisane w ciągu, na wypadek gdybyś chciał w przyszłości wykryć niektóre ucieczki lub mikroby ;
• możesz użyć ich macierzy odległości lub biblioteki geometrii do obliczania odległości lub wykrywania punktów w niektórych obszarach za pomocą wywołań tak prostych:google.maps.geometry.poly.containsLocation(latLng, bermudaTrianglePolygon))
• istnieje wiele interfejsów API „po stronie serwera”, z których można korzystać (w Pythonie , Ruby on Rails , PHP , CodeIgniter , Laravel , Yii , Zend Framework itp.), które używają interfejsu API Google Maps.

W ten sposób nie musisz się martwić indeksowaniem liczb i wszystkimi innymi problemami związanymi z typami danych, które mogą popsuć twoje współrzędne.

w zależności od zastosowania sugeruję użycie FLOAT (9,6)

klucze przestrzenne dadzą ci więcej funkcji, ale w testach wydajności pływaki są znacznie szybsze niż klucze przestrzenne. (0,01 VS 0,001 w systemie AVG)

MySQL używa double dla wszystkich pływaków ... Więc użyj double. Użycie liczby zmiennoprzecinkowej doprowadzi do nieprzewidywalnych zaokrąglonych wartości w większości sytuacji

Chociaż nie jest to optymalne dla wszystkich operacji, jeśli tworzysz kafelki mapy lub pracujesz z dużą liczbą znaczników (kropek) za pomocą tylko jednej projekcji (np. Mercator, jak Google Maps i wiele innych śliskich ram map), znalazłem to, co Nazywam „Ogromny Układ Współrzędnych”, aby był naprawdę bardzo przydatny. Zasadniczo przechowujesz współrzędne pikselowe xiy przy pewnym zbliżeniu - używam poziomu powiększenia 23. Ma to kilka zalet:

• Kosztowną transformację pikseli na linii mercator wykonujesz jeden raz, a nie za każdym razem, gdy sobie poradzisz
• Uzyskanie współrzędnej kafelka z rekordu przy danym poziomie powiększenia wymaga jednego przesunięcia w prawo.
• Uzyskanie współrzędnej piksela z rekordu wymaga jednego przesunięcia w prawo i jednego bitowego AND.
• Przesunięcia są tak lekkie, że praktyczne jest wykonywanie ich w SQL, co oznacza, że ​​możesz wykonać DISTINCT, aby zwrócić tylko jeden rekord na lokalizację piksela, co zmniejszy liczbę rekordów zwracanych przez backend, co oznacza mniej przetwarzania na przód.

O tym wszystkim mówiłem w niedawnym wpisie na blogu: http://blog.webfoot.com/2013/03/12/optimizing-map-tile-generation/

Jestem bardzo zaskoczony niektórymi odpowiedziami / komentarzami.

Dlaczego, u licha, ktoś miałby dobrowolnie „wstępnie zmniejszyć” precyzję, a następnie wykonać obliczenia na gorszych liczbach? Brzmi ostatecznie głupio.

Jeśli źródło ma 64-bitową precyzję, na pewno głupio byłoby dobrowolnie naprawić skalę np. 6 miejsc po przecinku i ogranicz precyzję do maksymalnie 9 cyfr znaczących (co dzieje się w przypadku powszechnie proponowanego formatu dziesiętnego 9,6).

Oczywiście dane są przechowywane z precyzją, jaką ma materiał źródłowy. Jedynym powodem zmniejszenia precyzji byłoby ograniczone miejsce do przechowywania.

• Przechowuj dane źródłowe z oryginalną dokładnością
• Przechowuj liczby obliczone ze źródła z dokładnością do obliczeń (np. Jeśli kod aplikacji używa podwójnych, zapisz wyniki jako podwójne)

Dziesiętny format 9,6 powoduje zjawisko przyciągania do siatki. To powinien być ostatni krok, jeśli w ogóle się wydarzy.

Nie zapraszam do mojego gniazda skumulowanych błędów.

TL; DR

Użyj FLOAT (8,5), jeśli nie pracujesz w NASA / wojsku i nie tworzysz systemów nawigacyjnych samolotów.

Aby w pełni odpowiedzieć na twoje pytanie, musisz rozważyć kilka rzeczy:

Format

• stopnie minuty sekundy : 40 ° 26 ′ 46 ″ N 79 ° 58 ′ 56 ″ W
• stopnie dziesiętne minuty : 40 ° 26,767 ′ N 79 ° 58,933 ′ W
• stopnie dziesiętne 1 : 40,466 ° N 79,982 ° W
• stopnie dziesiętne 2 : -32,60875, 21,27812
• Jakiś inny domowy format? Nikt nie zabrania ci tworzenia własnego układu współrzędnych domu i przechowywania go jako kierunku i odległości od domu. Może to mieć sens w przypadku niektórych konkretnych problemów, nad którymi pracujesz.

Tak więc pierwsza część odpowiedzi brzmi - możesz przechowywać współrzędne w formacie używanym przez twoją aplikację, aby uniknąć ciągłych konwersji tam iz powrotem i uprościć zapytania SQL.

Najprawdopodobniej używasz Map Google lub OSM do wyświetlania danych, a GMaps używają formatu „stopnie dziesiętne 2”. Dzięki temu łatwiej będzie przechowywać współrzędne w tym samym formacie.

Precyzja

Następnie chcesz zdefiniować potrzebną precyzję. Oczywiście możesz przechowywać współrzędne, takie jak „-32.608697550570334,21.278081997935146”, ale czy kiedykolwiek dbałeś o milimetry podczas nawigacji do punktu? Jeśli nie pracujesz w NASA i nie wykonujesz trajektorii satelitów, rakiet lub samolotów, powinieneś być w porządku z dokładnością do kilku metrów.

Powszechnie stosowanym formatem jest 5 cyfr po kropkach, co daje dokładność 50 cm.

Przykład : odległość 1 cm między X 21,278081 8 a X 21,278081 9 . Tak więc 7 cyfr po kropce daje dokładność 1/2 cm, a 5 cyfr po kropce daje precyzję 1/2 metra (ponieważ minimalna odległość między odrębnymi punktami wynosi 1 m, więc błąd zaokrąglenia nie może przekraczać połowy). W większości celów cywilnych powinno wystarczyć.

format minut po przecinku (40 ° 26,767 ′ N 79 ° 58,933 ′ W) daje dokładnie taką samą dokładność jak 5 cyfr po kropce

Energooszczędne miejsce do przechowywania

Jeśli wybrałeś format dziesiętny, twoją współrzędną jest para (-32.60875, 21.27812). Oczywiście wystarczą 2 x (1 bit na znak, 2 cyfry na stopnie i 5 cyfr na wykładnik).

Więc tutaj chciałbym wesprzeć Alix Axel z komentarzy mówiących, że sugestia Google, aby przechowywać go w FLOAT (10,6) jest naprawdę dodatkowa, ponieważ nie potrzebujesz 4 cyfr dla głównej części (ponieważ znak jest oddzielony, a szerokość geograficzna jest ograniczona do 90, a długość geograficzna jest ograniczona do 180). Możesz z łatwością użyć FLOAT (8,5) dla dokładności 1 / 2m lub FLOAT (9,6) dla dokładności 50 / 2cm. Lub możesz nawet przechowywać lat i long w osobnych typach, ponieważ FLOAT (7,5) wystarcza dla lat. Zobacz odwołanie do typów pływaków MySQL . Każdy z nich będzie podobny do normalnego FLOAT i będzie równy 4 bajtom.

Zwykle przestrzeń nie jest obecnie problemem, ale jeśli chcesz naprawdę zoptymalizować pamięć z jakiegoś powodu (Oświadczenie: nie rób wstępnej optymalizacji), możesz skompresować lat (nie więcej niż 91 000 wartości + znak) + długi (nie ponad 181 000 wartości + znak) do 21 bitów, czyli znacznie mniej niż 2xFLOAT (8 bajtów == 64 bitów)

Funkcje przestrzenne w PostGIS są znacznie bardziej funkcjonalne (tj. Nie są ograniczone do operacji BBOX) niż funkcje przestrzenne MySQL. Sprawdź to: link do tekstu

1. Szerokości geograficzne wynoszą od -90 do +90 (stopni), więc DECIMAL (10, 8) jest do tego odpowiedni

2. długości geograficzne wynoszą od -180 do +180 (stopni), więc potrzebujesz DECIMAL (11, 8).

Uwaga: Pierwsza liczba to całkowita liczba zapisanych cyfr, a druga to liczba po przecinku.

W skrócie: lat DECIMAL(10, 8) NOT NULL, lng DECIMAL(11, 8) NOT NULL

Sugeruję użycie typu danych Float dla SQL Server.

Długie obliczenia wymagają precyzji, więc używaj pewnego rodzaju typu dziesiętnego i zwiększ dokładność co najmniej 2 razy więcej niż liczba, którą zapiszesz, aby wykonać obliczenia matematyczne. Nie wiem o moich typach danych SQL, ale na serwerze SQL ludzie często używają liczb zmiennoprzecinkowych lub rzeczywistych zamiast dziesiętnych i wpadają w kłopoty, ponieważ są to szacunkowe liczby, a nie liczby rzeczywiste. Upewnij się więc, że używany typ danych jest prawdziwym typem dziesiętnym, a nie zmiennoprzecinkowym, i wszystko powinno być w porządku.

A FLOATpowinno dać ci całą precyzję, jakiej potrzebujesz, i być lepsze dla funkcji porównawczych niż przechowywanie każdej współrzędnej jako łańcucha lub podobnego.

Jeśli Twoja wersja MySQL jest wcześniejsza niż 5.0.3, może być konieczne zwrócenie uwagi na niektóre błędy porównania zmiennoprzecinkowego .

W wersjach wcześniejszych niż MySQL 5.0.3 kolumny DECIMAL przechowują wartości z dokładną dokładnością, ponieważ są one reprezentowane jako ciągi, ale obliczenia wartości DECIMAL są wykonywane przy użyciu operacji zmiennoprzecinkowych. Od wersji 5.0.3 MySQL wykonuje operacje DECIMAL z dokładnością do 64 cyfr dziesiętnych, co powinno rozwiązać najczęstsze problemy z niedokładnością w przypadku kolumn DECIMAL