Quel type de données utiliser pour stocker des données de latitude et de longitude dans des bases de données SQL ?

Question

Lors du stockage de données de latitude ou de longitude dans une base de données conforme à la norme ANSI SQL, quel type de données serait le plus approprié ? Est-ce que float être utilisé, ou decimal ou ... ?

Je sais qu'Oracle, MySql et SQL Server ont ajouté des types de données spécifiques pour traiter les données géographiques, mais je suis intéressé par la façon dont vous stockez les informations dans une base de données SQL "classique".

Answer 1

Pour les latitudes, utilisez : Decimal(8,6) et les longitudes sont utilisées : Decimal(9,6)

Si vous n'êtes pas habitué aux paramètres de précision et d'échelle, voici un visuel de la chaîne de format :

Latitude et longitude ##.###### y ###.######

Une précision de 6 décimales devrait vous permettre d'atteindre une précision d'environ 10 cm sur une coordonnée.

Answer 2

Nous utilisons des flottants, mais n'importe quel type de numérique avec 6 décimales devrait également fonctionner.

Answer 3

Vous pouvez facilement stocker un nombre décimal de lat/lon dans un champ entier non signé, au lieu de les diviser en une partie entière et une partie décimale et de les stocker séparément comme cela est quelque peu suggéré ici en utilisant l'algorithme de conversion suivant :

comme une fonction mysql stockée :

CREATE DEFINER=`r`@`l` FUNCTION `PositionSmallToFloat`(s INT) 
RETURNS decimal(10,7)
DETERMINISTIC
RETURN if( ((s > 0) && (s >> 31)) , (-(0x7FFFFFFF - 
(s & 0x7FFFFFFF))) / 600000, s / 600000)

et retour

CREATE DEFINER=`r`@`l` FUNCTION `PositionFloatToSmall`(s DECIMAL(10,7)) 
RETURNS int(10)
DETERMINISTIC
RETURN s * 600000

Cela doit être stocké dans un int non signé (10) Cela fonctionne aussi bien dans mysql que dans sqlite qui est sans typologie.

Par expérience, je trouve que cela fonctionne très rapidement, si tout ce dont vous avez besoin est de stocker des coordonnées et de les récupérer pour faire des calculs.

en php ces 2 fonctions ressemblent à

function LatitudeSmallToFloat($LatitudeSmall){
   if(($LatitudeSmall>0)&&($LatitudeSmall>>31)) 
     $LatitudeSmall=-(0x7FFFFFFF-($LatitudeSmall&0x7FFFFFFF))-1;
   return (float)$LatitudeSmall/(float)600000;
}

et retour :

function LatitudeFloatToSmall($LatitudeFloat){
   $Latitude=round((float)$LatitudeFloat*(float)600000);
   if($Latitude<0) $Latitude+=0xFFFFFFFF;
   return $Latitude;
}

Cela présente également un avantage supplémentaire en termes de création, par exemple, de clés uniques memcached avec des entiers. (ex : pour mettre en cache un résultat de géocode). J'espère que cela ajoutera de la valeur à la discussion.

Une autre application pourrait être lorsque vous n'avez pas d'extensions SIG et que vous voulez simplement conserver quelques millions de ces paires lat/lon, vous pouvez utiliser des partitions sur ces champs dans mysql pour bénéficier du fait qu'ils sont des entiers :

Create Table: CREATE TABLE `Locations` (
  `lat` int(10) unsigned NOT NULL,
  `lon` int(10) unsigned NOT NULL,
  `location` text,
  PRIMARY KEY (`lat`,`lon`) USING BTREE,
  KEY `index_location` (`locationText`(30))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY KEY ()
PARTITIONS 100 */

Answer 4

Vous avez demandé comment stocker la latitude et la longitude et ma réponse est la suivante : Ne le faites pas, vous pourriez envisager d'utiliser la fonction WGS 84 ( en Europe ETRS 89 ) car il s'agit de la norme pour les références Geo.

Mais ce détail mis à part, j'ai utilisé un type défini par l'utilisateur avant que SQL 2008 n'inclue finalement le support géographique.

Answer 5

Dans vanilla Oracle, la fonction appelée LOCATOR (une version handicapée de Spatial) exige que les données de coordonnées soient stockées en utilisant le type de données NUMBER (sans précision). Lorsque vous essayez de créer des index basés sur des fonctions pour prendre en charge les requêtes spatiales, le système se bloque sinon.