What datatype to use when storing latitude and longitude data in SQL databases

Question

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  0   amp  amp   s  gt  gt  31      - 0x7FFFFFFF -   s  amp  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 gt 0  amp  amp   LatitudeSmall gt  gt 31          LatitudeSmall - 0x7FFFFFFF-  LatitudeSmall amp 0x7FFFFFFF  -1     return  float  LatitudeSmall  float 600000      and back again   function LatitudeFloatToSmall  LatitudeFloat       Latitude round  float  LatitudeFloat  float 600000      if  Latitude lt 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

User · Answer

For longitudes use  Decimal 9 6   and latitudes use  Decimal 8 6   If you re not used to precision and scale parameters  here s a format string visual              and

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to database

Examples related to geocoding

Examples related to latitude-longitude