Qual é o tipo de dados ideal para usar ao armazenar latitude / longitude em um database MySQL?

Tendo em mente que realizarei cálculos em pares lat / long, qual tipo de dados é mais adequado para uso com um database MySQL?

   

Use as extensões espaciais do MySQL com o GIS.

O Google fornece um começo para concluir a solução PHP / MySQL para um exemplo de aplicativo “Store Locator” com o Google Maps. Neste exemplo, eles armazenam os valores lat / lng como “Float” com um comprimento de “10,6”

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

Basicamente, depende da precisão que você precisa para seus locais. Usando o DOUBLE você terá uma precisão de 3.5nm. DECIMAL (8,6) / (9,6) desce para 16cm. FLOAT é de 1,7 m …

Esta tabela muito interessante tem uma lista mais completa: 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 

Espero que isto ajude.

As Extensões Espaciais do MySQL são a melhor opção porque você tem a lista completa de operadores e índices espaciais à sua disposição. Um índice espacial permitirá que você realize cálculos baseados em distância com muita rapidez. Tenha em mente que, a partir de 6.0, a Extensão Espacial ainda está incompleta. Eu não estou colocando para baixo o MySQL Spatial, apenas deixando você saber das armadilhas antes de você ir muito longe nisso.

Se você está lidando estritamente com pontos e apenas com a function DISTANCE, tudo bem. Se você precisar fazer cálculos com Polígonos, Linhas ou Pontos de Buffer, os operadores espaciais não fornecerão resultados exatos, a menos que você use o operador “relacionar”. Veja o aviso no topo do 21.5.6 . Relacionamentos como contém, dentro ou interseções estão usando o MBR, não a forma exata da geometry (isto é, um Elipse é tratado como um Retângulo).

Além disso, as distâncias no MySQL Spatial estão nas mesmas unidades de sua primeira geometry. Isso significa que, se você estiver usando Graus Decimais, suas medidas de distância estarão em Graus Decimais. Isso tornará muito difícil obter resultados exatos à medida que você se afasta do equador.

Quando fiz isso para um database de navegação construído a partir do ARINC424, fiz uma boa quantidade de testes e, olhando para o código, usei um DECIMAL (18,12) (na verdade, um NUMERIC (18,12) porque era firebird).

Floats e duplas não são tão precisos e podem resultar em erros de arredondamento, o que pode ser uma coisa muito ruim. Não me lembro se encontrei algum dado real que tivesse problemas – mas tenho quase certeza de que a incapacidade de armazenar com precisão em um float ou em um duplo poderia causar problemas

O ponto é que ao usar graus ou radianos sabemos o intervalo dos valores – e a parte fracionária precisa da maioria dos dígitos.

As MySQL Spatial Extensions são uma boa alternativa porque seguem o OpenGIS Geometry Model . Eu não os usei porque precisava manter meu database portátil.

Depende da precisão que você precisa.

 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 

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

Para resumir:

  • A opção mais precisa disponível é DOUBLE .
  • O tipo mais comum usado é DECIMAL(8,6)/(9,6) .

A partir do MySQL 5.7 , considere o uso de Tipos de Dados Espaciais (SDT), especificamente o POINT para armazenar uma única coordenada. Antes de 5.7, o SDT não suporta índices (com exceção de 5.6 quando o tipo de tabela é MyISAM).

Nota:

  • Ao usar a class POINT , a ordem dos argumentos para armazenar coordenadas deve ser POINT(latitude, longitude) .
  • Existe uma syntax especial para criar um índice espacial .
  • A maior vantagem de usar o SDT é que você tem access às Funções de Análise Espacial , por exemplo, calculando a distância entre dois pontos ( ST_Distance ) e determinando se um ponto está contido em outra área ( ST_Contains ).

Com base neste artigo da wiki http://en.wikipedia.org/wiki/Decimal_degrees#Accuracy, o tipo de dados apropriado no MySQL é Decimal (9,6) para armazenar a longitude e a latitude em campos separados.

Use DECIMAL(8,6) para latitude (90 a -90 graus) e DECIMAL(9,6) para longitude (180 a -180 graus). 6 casas decimais são boas para a maioria das aplicações. Ambos devem ser “assinados” para permitir valores negativos.

Não há necessidade de ir longe, de acordo com o Google Maps, o melhor é FLOAT (10,6) para lat e lng.

Nós armazenamos latitude / longitude X 1.000.000 em nosso database oracle como NUMBERS para evitar erros de arredondamento com duplas.

Dado que a latitude / longitude para a sexta casa decimal foi de 10 cm de precisão que era tudo o que precisávamos. Muitos outros bancos de dados também armazenam lat / long para a 6ª casa decimal.

Em uma perspectiva completamente diferente e mais simples:

  • Se você está contando com o Google para mostrar seus mapas, marcadores, polígonos, o que for, deixe os cálculos serem feitos pelo Google!
  • você economiza resources em seu servidor e simplesmente armazena a latitude e a longitude juntas como uma única string ( VARCHAR ), ex: ” -0000.0000001, -0000.000000000000001 ” (35 comprimento e se um número tiver mais de 7 dígitos decimais, então ele será arredondado) ;
  • Se o Google retornar mais de 7 dígitos decimais por número, você poderá obter os dados armazenados na sua string de qualquer maneira, caso deseje detectar algumas falhas ou micróbios no futuro ;
  • você pode usar sua matriz de distância ou sua biblioteca de geometry para calcular distâncias ou detectar pontos em determinadas áreas com chamadas tão simples como esta: google.maps.geometry.poly.containsLocation(latLng, bermudaTrianglePolygon))
  • existem muitas APIs “server-side” que você pode usar (em Python , Ruby on Rails , PHP , CodeIgniter , Laravel , Yii , Zend Framework , etc.) que usam a API do Google Maps.

Dessa forma, você não precisa se preocupar com números de indexação e todos os outros problemas associados a tipos de dados que podem estragar suas coordenadas.

dependendo da sua aplicação, sugiro usar FLOAT (9,6)

as chaves espaciais lhe darão mais resources, mas nos benchmarks de produção os floats são muito mais rápidos que as chaves espaciais. (0,01 VS 0,001 na AVG)

Embora não seja ideal para todas as operações, se você estiver criando blocos de mapas ou trabalhando com um grande número de marcadores (pontos) com apenas uma projeção (por exemplo, Mercator, como o Google Maps e muitos outros frameworks de mapas escorregadios), Eu chamo “Vast Coordinate System” para ser muito, muito útil. Basicamente, você armazena as coordenadas dos pixels x e y de alguma forma com zoom – eu uso o nível de zoom 23. Isso tem vários benefícios:

  • Você faz o caro lat / lng para mercator transformação de pixels uma vez em vez de cada vez que você lida com o ponto
  • Obtendo a coordenada do bloco de um registro dado um nível de zoom leva um turno à direita.
  • Obtendo a coordenada de pixel de um registro leva um deslocamento à direita e um bit a bit e.
  • Os turnos são tão leves que é prático fazê-los em SQL, o que significa que você pode fazer um DISTINCT para retornar apenas um registro por local de pixel, o que reduzirá os registros de número retornados pelo backend, o que significa menos processamento no a parte dianteira.

Eu falei sobre tudo isso em um post recente: http://blog.webfoot.com/2013/03/12/optimizing-map-tile-generation/

As funções espaciais no PostGIS são muito mais funcionais (isto é, não restritas a operações BBOX) do que aquelas nas funções espaciais do MySQL. Confira: texto do link

O MySQL usa double para todos os floats … Então, use type double. Usar float levará a valores arredondados imprevisíveis na maioria das situações

Estou muito surpreso com algumas respostas / comentários.

Por que diabos alguém estaria disposto a voluntariamente “pré-diminuir” a precisão e, mais tarde, realizar cálculos com números piores? Soa finalmente estúpido.

Se a fonte tiver precisão de 64 bits, certamente seria estupidez ajustar voluntariamente a escala para, por exemplo. 6 decimais e limite a precisão a um máximo de 9 dígitos significativos (o que acontece com o formato decimal 9,6 comumente proposto).

Naturalmente, armazena-se os dados com a precisão que o material de origem possui. O único motivo para diminuir a precisão seria o espaço de armazenamento limitado.

  • Armazene figuras de origem com precisão original
  • Armazena os valores calculados da fonte na precisão em que o cálculo acontece (por exemplo, se o código da aplicação usa duplas, armazene os resultados como duplos)

O formato decimal 9.6 causa um fenómeno do snap à grade. Esse deve ser o último passo, se é que tudo vai acontecer.

Eu não iria convidar erros acumulados para o meu ninho.

TL; DR

Use FLOAT (8,5) se você não estiver trabalhando na NASA / militar e não fazendo sistemas de navegação de aeronaves.


Para responder sua pergunta completamente, você precisa considerar várias coisas:

Formato

  • graus minutos segundos : 40 ° 26 ′ 46 ″ N 79 ° 58 ′ 56 ″ W
  • graus decimais minutos : 40 ° 26.767 ′ N 79 ° 58.933 ′ W
  • graus decimais 1 : 40.446 ° N 79.982 ° W
  • graus decimais 2 : -32.60875, 21.27812
  • Algum outro formato caseiro? Ninguém o proíbe de criar seu próprio sistema de coordenadas centralizado em casa e armazená-lo como rumo e distância de sua casa. Isso pode fazer sentido para alguns problemas específicos em que você está trabalhando.

Portanto, a primeira parte da resposta seria: você pode armazenar as coordenadas no formato que seu aplicativo usa para evitar conversões constantes e fazer consultas SQL mais simples.

Muito provavelmente você usa o Google Maps ou o OSM para exibir seus dados, e o GMaps está usando o formato “graus decimais 2”. Por isso, será mais fácil armazenar coordenadas no mesmo formato.

Precisão

Então, você gostaria de definir a precisão que você precisa. Claro que você pode armazenar coordenadas como “-32,608697550570334,21.278081997935146”, mas você já se preocupou com milímetros enquanto a navegação para o ponto? Se você não está trabalhando na NASA e não está fazendo satélites, foguetes ou trajetórias de aviões, você deve estar bem com vários metros de precisão.

O formato comumente usado é de 5 dígitos depois de pontos, o que lhe dá 50cm de precisão.

Exemplo : existe uma distância de 1 cm entre X, 21,278081 8 e X, 21,278081 9 . Assim, 7 dígitos após o ponto dão a precisão de 1 / 2cm e 5 dígitos depois do ponto darão a precisão de 1/2 (porque a distância mínima entre pontos distintos é 1m, portanto o erro de arredondamento não pode ser mais do que a metade). Para a maioria dos fins civis, deveria ser suficiente.

graus decimais minutos formato (40 ° 26.767 ′ N 79 ° 58.933 ′ W) dá-lhe exactamente a mesma precisão que 5 dígitos após ponto

Armazenamento com espaço eficiente

Se você selecionou o formato decimal, então sua coordenada é um par (-32.60875, 21.27812). Obviamente, 2 x (1 bit para sinal, 2 dígitos para graus e 5 dígitos para expoente) serão suficientes.

Então, aqui eu gostaria de apoiar Alix Axel de comentários dizendo que a sugestão do Google para armazená-lo no FLOAT (10,6) é realmente extra, porque você não precisa de 4 dígitos para a parte principal (já que o sinal é separado e a latitude é limitada 90 e longitude é limitado a 180). Você pode facilmente usar FLOAT (8,5) para precisão de 1 / 2m ou FLOAT (9,6) para precisão de 50 / 2cm. Ou você pode até armazenar lat e long em tipos separados, porque FLOAT (7,5) é suficiente para lat. Veja referência de tipos flutuantes do MySQL. Qualquer um deles será como o FLOAT normal e igual a 4 bytes.

Normalmente o espaço não é um problema hoje em dia, mas se você quiser realmente otimizar o armazenamento por algum motivo (Aviso: não faça pré-otimização), você pode comprimir lat (não mais do que 91.000 valores + sinal) + longo (não mais de 181 000 valores + sinal) para 21 bits que é significativamente menor que 2xFLOAT (8 bytes == 64 bits)

Os cálculos de Lat Long requerem precisão, portanto use algum tipo de tipo decimal e faça a precisão pelo menos 2 maior do que o número que você armazenará para realizar cálculos matemáticos. Eu não sei sobre os tipos de dados my sql, mas no SQL Server as pessoas costumam usar float ou real, em vez de decimal e entrar em apuros, porque estes são estimados números não reais. Portanto, apenas certifique-se de que o tipo de dados que você usa é um tipo decimal verdadeiro e não um tipo decimal flutuante e você deve estar bem.

Um FLOAT deve dar a você toda a precisão que você precisa, e ser melhor para funções de comparação do que armazenar cada coordenada como uma string ou algo parecido.

Se sua versão do MySQL é anterior a 5.0.3, você pode precisar tomar cuidado com certos erros de comparação de ponto flutuante .

Antes do MySQL 5.0.3, as colunas DECIMAL armazenam valores com precisão exata, porque eles são representados como strings, mas os cálculos em valores DECIMAL são feitos usando operações de ponto flutuante. A partir do 5.0.3, o MySQL executa operações DECIMAIS com uma precisão de 64 dígitos decimais, o que deve resolver os problemas de imprecisão mais comuns quando se trata de colunas DECIMAL