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.
For latitudes use: Decimal(8,6), and longitudes use: Decimal(9,6)
If you're not used to precision and scale parameters, here's a format string visual:
Latitude and Longitude
##.###### and ###.######
To 6 decimal places should get you to around ~10cm of accuracy on a coordinate.
We use float, but any flavor of numeric with 6 decimal places should also work.
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
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 */
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.
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?).
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.
