[sql] What datatype to use when storing latitude and longitude data in SQL databases?

When storing latitude or longitude data in an ANSI SQL compliant database, what datatype would be most appropriate? Should float be used, or decimal, or ...?

I'm aware that Oracle, MySql, and SQL Server have added some special datatypes specifically for handling geo data, but I'm interested in how you would store the information in a "plain vanilla" SQL database.

I would use a decimal with the proper precision for your data.

You should take a look at the new Spatial data-types that were introduced in SQL Server 2008. They are specifically designed this kind of task and make indexing and querying the data much easier and more efficient.

http://msdn.microsoft.com/en-us/library/bb933876(v=sql.105).aspx

We use float, but any flavor of numeric with 6 decimal places should also work.

I think it depends on the operations you'll be needing to do most frequently.

If you need the full value as a decimal number, then use decimal with appropriate precision and scale. Float is way beyond your needs, I believe.

If you'll be converting to/from degºmin'sec"fraction notation often, I'd consider storing each value as an integer type (smallint, tinyint, tinyint, smallint?).

You can easily store a lat/lon decimal number in an unsigned integer field, instead of splitting them up in a integer and decimal part and storing those separately as somewhat suggested here using the following conversion algorithm:

as a stored mysql function:

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)

and back

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

That needs to be stored in an unsigned int(10), this works in mysql as well as in sqlite which is typeless.

through experience, I find that this works really fast, if all you need to to is store coordinates and retrieve those to do some math with.

in php those 2 functions look like

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

and back again:

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

This has some added advantage as well in term of creating for example memcached unique keys with integers. (ex: to cache a geocode result). Hope this adds value to the discussion.

Another application could be when you are without GIS extensions and simply want to keep a few million of those lat/lon pairs, you can use partitions on those fields in mysql to benefit from the fact they are integers:

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 */

Well, you asked how to store Latitude/Longitude and my answer is: Don't, you might consider using the WGS 84 ( in Europe ETRS 89 ) as it is the standard for Geo references.

But that detail aside I used a User Defined Type in the days before SQL 2008 finally include geo support.

In vanilla Oracle, the feature called LOCATOR (a crippled version of Spatial) requires that the coordinate data be stored using the datatype of NUMBER (no precision). When you try to create Function Based Indexes to support spatial queries it'll gag otherwise.