Jaki jest idealny typ danych do przechowywania szerokości / długości geograficznej w bazie danych MySQL?

Google udostępnia rozwiązanie PHP/MySQL dla przykładowej aplikacji "Store Locator" z Mapami Google. 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, jakiej potrzebujesz dla swoich lokalizacji. Używając DOUBLE będziesz miał precyzję 3,5 nm. Dziesiętne (8,6)/(9,6) schodzi do 16cm. Pływak ma długość 1,7 m...

Ta bardzo ciekawa 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

Rozszerzenia przestrzenne MySQL są najlepszą opcją, ponieważ masz do dyspozycji pełną listę operatorów przestrzennych i indeksów. Indeks przestrzenny pozwoli na bardzo szybkie wykonywanie obliczeń opartych na odległości. Należy pamiętać, że od wersji 6.0 rozszerzenie przestrzenne jest nadal niekompletne. Nie odkładam MySQL, tylko informuję Cię o pułapkach, zanim za bardzo się w tym zajmiesz.

Jeśli mamy do czynienia wyłącznie z punktami i tylko funkcją odległości, w porządku. Jeśli musisz wykonać obliczenia z wielokątami, liniami lub punktami Buforowanymi, operatory przestrzenne nie dostarczają dokładnych wyników, chyba że użyjesz operatora "relate". Zobacz ostrzeżenie na górze 21.5.6. Relacje takie jak Zawiera, wewnątrz lub przecięcia używają MBR, a nie dokładnego kształtu geometrii (tzn. elipsa jest traktowana jak prostokąt).

Również odległości w MySQL Spatial są w tych samych jednostkach, co pierwsza geometria. Oznacza to, że jeśli używasz Stopnie dziesiętne, następnie pomiary odległości są w stopniach dziesiętnych. Będzie to bardzo trudne do uzyskania dokładnych wyników, jak dostać się dalej od równika.

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

Pływaki i duble nie są tak precyzyjne i mogą powodować błędy zaokrąglania, które mogą być bardzo złe. Nie pamiętam, czy znalazłem jakieś prawdziwe dane, które miały problemy - ale jestem dość pewien, że niemożność przechowywania dokładnie w float lub double może spowodować problemy

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

Rozszerzenia przestrzenne MySQL są dobrą alternatywą, ponieważ podążają za modelem geometrii OpenGIS . Nie używałem ich, ponieważ musiałem zachować 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

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

Podsumowując:

• najbardziej precyzyjną dostępną opcją jest DOUBLE.
• najczęściej używanym typem jest DECIMAL(8,6)/(9,6).

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

Uwaga:

• podczas używania klasy POINT, kolejność argumentów do przechowywania współrzędnych musi wynosić 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 analizy przestrzennej, np. obliczania odległości między dwoma punktami (ST_Distance) i ustalenie, 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 Decimal (9,6) do przechowywania długości i szerokości geograficznej w oddzielne pola.

Użyj DECIMAL(8,6) dla szerokości geograficznej (90 do -90 stopni) i DECIMAL(9,6) dla długości geograficznej (180 do -180 stopni). 6 miejsc po przecinku jest w porządku dla większości zastosowań. Oba powinny być "podpisane", aby umożliwić wartości ujemne.

Nie trzeba daleko iść, według Google Maps, najlepszy jest FLOAT (10,6) dla lat i lng.

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

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

W zupełnie innej i prostszej perspektywie:

• jeśli polegasz na Google za wyświetlanie map, markerów, wielokątów, cokolwiek, to niech obliczenia zostaną wykonane przez Google!
• zapisujesz zasoby na swoim serwerze i po prostu przechowujesz szerokość i długość geograficzną razem jako pojedynczy ciąg znaków (VARCHAR), np..: "-0000.0000001,-0000.000000000000001" (35 długość i jeśli liczba ma więcej niż 7 cyfr dziesiętnych, to jest zaokrąglana);
• Jeśli Google zwróci więcej w przypadku, gdy chcesz wykryć niektóre flees lub microbes w przyszłości ;
• możesz użyć ich macierzy odległości lub biblioteki geometrii do obliczania odległości lub wykrywania punktów w określonych obszarach z wywołaniami tak prostymi jak to: google.maps.geometry.poly.containsLocation(latLng, bermudaTrianglePolygon))
• istnieje wiele interfejsów API po stronie serwera, których możesz użyć (w Pythonie , Ruby on Rails, PHP , CodeIgniter, Laravel, Yii, Zend Framework , itd.), które korzystają z Google Maps API.

W ten sposób nie musisz się martwić o indeksowanie liczb i wszystkich innych problemów związanych z typami danych, które mogą zepsuć twoje współrzędne.

TL; DR

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

Aby w pełni odpowiedzieć na twoje pytanie, musisz wziąć pod uwagę 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.446° N 79.982° w
• stopnie dziesiętne 2: -32.60875, 21.27812
• jakiś inny domowy format? Nikt nie zabrania ci tworzenia własnego systemu współrzędnych zorientowanych na dom i przechowywania go jako pozycji i odległości od domu. Może to mieć sens w przypadku konkretnych problemów, nad którymi pracujesz.

Tak więc pierwsza część odpowiedzi brzmiałaby - możesz zapisać współrzędne w formacie , którego używa Twoja aplikacja , aby uniknąć ciągłych konwersji w przód iw tył i tworzyć prostsze zapytania SQL.

Najbardziej prawdopodobnie używasz Google Maps lub OSM do wyświetlania danych, a GMaps używają formatu "decimal degrees 2". Dzięki temu łatwiej będzie przechowywać współrzędne w tym samym formacie.

Precision

Następnie, chcesz zdefiniować precyzję, której potrzebujesz. 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 robisz satelitów, rakiet lub samolotów trajektorie, powinno być dobrze z kilku metrów dokładności.

Powszechnie używany format to 5 cyfr po kropkach, co daje dokładność 50cm.

przykład: między X jest odległość 1cm,21.2780818 I X,21.2780819. Tak więc 7 cyfr po kropce daje dokładność 1 / 2cm, a 5 cyfr po kropce daje dokładność 1/2 metra (ponieważ minimalna odległość między różnymi punktami wynosi 1m, więc błąd zaokrąglania nie może być większy niż połowa). Dla większości celów cywilnych powinno wystarczyć.

Format minut dziesiętnych stopni (40° 26.767 'N 79° 58.933' W) daje dokładnie taką samą dokładność jak 5 cyfr po kropce

Oszczędne miejsce przechowywania

Jeśli wybrałeś format dziesiętny, twoja współrzędna jest parą (-32.60875, 21.27812). Oczywiście wystarczy 2 x (1 bit dla znaku, 2 cyfry dla stopni i 5 cyfr dla wykładnika).

Więc tutaj chciałbym wesprzeć Alix Axel z komentarzy mówiących, że sugestia Google aby zapisać go w FLOAT(10,6) jest naprawdę ekstra, ponieważ nie potrzebujesz 4 cyfr dla głównej części (ponieważ znak jest oddzielony i szerokość jest ograniczona do 90 i długość jest ograniczona do 180). Możesz łatwo użyć pływaka (8,5) dla precyzji 1 / 2m lub pływaka (9,6) dla precyzji 50 / 2cm. Albo można nawet przechowywać lat i long w oddzielonych typach, ponieważ FLOAT (7,5) jest wystarczający dla lat. Zobacz Typy float MySQL reference . Każdy z nich będzie podobny do zwykłego FLOAT ' a i będzie równy 4 bajtom.

Zazwyczaj spacja jest nie jest to obecnie problem, ale jeśli chcesz naprawdę zoptymalizować pamięć z jakiegoś powodu( Zastrzeżenie: nie rób wstępnej optymalizacji), możesz skompresować lat (nie więcej niż 91 000 wartości + znak) + long(nie więcej niż 181 000 wartości + znak) do 21 bitów, co jest znacznie mniej niż 2xFLOAT (8 bajtów == 64 bity)

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

Klawisze przestrzenne dają więcej funkcji, ale w testach produkcyjnych pływaki są znacznie szybsze niż klawisze przestrzenne. (0,01 VS 0,001 w AVG)

MySQL używa podwójnego dla wszystkich floatów ... Więc użyj typu double. Użycie float prowadzi do nieprzewidywalnych zaokrąglonych wartości w większości sytuacji

Chociaż nie jest to optymalne dla wszystkich operacji, jeśli robisz kafelki map lub pracujesz z dużą liczbą znaczników (kropek) z tylko jedną projekcją (np. Mercator, jak Google Maps i wiele innych frameworków slippy maps), odkryłem, że to, co nazywam "rozległym układem współrzędnych", jest naprawdę, naprawdę przydatne. Zasadniczo zapisujesz współrzędne x i y pikseli w jakiś sposób-zoomed-in -- używam zoom level 23. Ma to kilka zalet:

Robisz drogie lat/lng do mercator pixel transformacja raz zamiast za każdym razem, gdy zajmujesz się punktem
• Uzyskanie współrzędnych kafelka z rekordu o poziomie powiększenia zajmuje jedno prawe przesunięcie.
• Uzyskanie współrzędnych piksela z rekordu wymaga jednego przesunięcia w prawo i jednego bitowego i.
• przesunięcia są tak lekkie, że praktyczne jest ich wykonywanie w SQL, co oznacza, że możesz zrobić odrębny, aby zwrócić tylko jeden rekord na lokalizację piksela, co zmniejszy liczbę rekordów zwracanych przez backend, co oznacza mniej przetwarzanie z przodu.

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

Funkcje przestrzenne w PostGIS są znacznie bardziej funkcjonalne (tzn. nie są ograniczone do operacji BBOX) niż te w funkcjach przestrzennych MySQL. Sprawdź: link text

1. Szerokość geograficzna waha się od -90 do +90 (stopni), więc dziesiętne(10, 8) jest ok dla tego

2. Długość geograficzna waha się od -180 do +180 (stopni), więc potrzebujesz dziesiętnych(11, 8).

Uwaga: pierwsza liczba jest całkowitą liczbą zapisanych cyfr, a druga jest liczbą po przecinku.

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

Proponuję użyć Float datatype dla SQL Server.

Idealny typ danych do przechowywania wartości długich Lat to decimal(9,6)

Jest to z dokładnością około 10cm, przy użyciu tylko 5 bajtów pamięci.

Np. CAST (123.456789 as decimal(9,6))

Długie obliczenia wymagają precyzji, więc użyj pewnego rodzaju typu dziesiętnego i zrób precyzję co najmniej 2 wyższą od liczby, którą będziesz przechowywać w celu wykonania obliczeń matematycznych. Nie wiem jak moje typy danych SQL, ale w SQL serverze ludzie często używają float lub real zamiast dziesiętnych i wpadają w kłopoty, ponieważ są to liczby szacunkowe, a nie rzeczywiste. Więc upewnij się, że typ danych, którego używasz, jest prawdziwym typem dziesiętnym, a nie zmiennym typem dziesiętnym i powinieneś być dobrze.

A FLOAT powinno dać ci całą precyzję, jakiej potrzebujesz, i być lepsze dla funkcji porównawczych niż przechowywanie każdego koordynatu jako ciągu znaków lub tym podobnych.

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

Przed MySQL 5.0.3 kolumny dziesiętne przechowują wartości z dokładną precyzją, ponieważ są reprezentowane jako ciągi znaków, ale obliczenia wartości dziesiętnych są wykonywane za pomocą zmiennoprzecinkowych szef. Począwszy od wersji 5.0.3, MySQL wykonuje operacje dziesiętne z dokładnością 64 cyfr dziesiętnych, które powinny rozwiązać najczęstsze problemy z nieścisłością, jeśli chodzi o kolumny dziesiętne