data type of longitude and latitude to store in mysql database?

Question

I'm designing a table in mysql that will store a list of users and a Google Maps co-ordinate (longitude & latitude).

Will I need two fields, or can it be done with 1?

I don't know what I use? what I use float or decimal or GEOMETRY or there is new data type? what are the pros and cons of the best data type to choose it?

possible duplicate of [What is the ideal data type to use when storing latitude / longitudes in a MySQL database?](http://stackoverflow.com/questions/159255/what-is-the-ideal-data-type-to-use-when-storing-latitude-longitudes-in-a-mysql) — Gajus, Jun 15 '15 at 19:26

assaqqaf · Accepted Answer · 2014-04-02T16:54:18.613

5

You can use spatial extensions in mysql the datatype is POINT

It will be faster in search , and many features for geographic operation.

edited Apr 02 '14 at 16:54

answered Dec 13 '12 at 12:21

assaqqaf

1,575
4
21
38

1

this is the best solution for this data type http://stackoverflow.com/questions/9560147/datatype-to-store-longitude-latitude-in-mysql – Ahmed el-Gendy Dec 15 '12 at 12:06

Rene Pot · Answer 2 · 2012-12-13T13:10:03.973

3

A proper way, and fast, is described on this blog:

http://www.rooftopsolutions.nl/blog/229

CREATE TABLE geo (
  longitude DOUBLE,
  latitude DOUBLE,
  idxlong SMALLINT,
  idxlat SMALLINT,
  INDEX (idxlong,idxlat);
);

Part 2 contains a benchmark: http://www.rooftopsolutions.nl/blog/230

method  small   medium  large
plain select        1.73s
index on latitude       0.72s
using point field       9.52s
using point field + spatial index   0.00s   0.73s   18.82s
using morton number     0.78s
index on morton 0.00s   0.65s   3.23s

Also a part 3 with in practice: http://www.rooftopsolutions.nl/blog/231

edited Dec 13 '12 at 13:10

answered Dec 13 '12 at 11:08

Rene Pot

24,681
7
68
92

note that it is quite easy to convert long,lat columns to a 'spatially enabled' column, using: --Add a spatially – Ahmed el-Gendy Dec 13 '12 at 12:57

Salman A · Answer 3 · 2012-12-13T11:27:34.833

2

You can use:

a pair of DECIMAL(11, 8) (3 digits before and 8 digits after decimal)
a pair of FLOAT

Note that a decimal column can store an exact value, where as a float column stores an approximation of the value. For example 1.999999 will be stored as 1.999999 in the decimal column but as 2.0 in the float column.

edited Dec 13 '12 at 11:27

answered Dec 13 '12 at 11:08

Salman A

262,204
82
430
521

data type of longitude and latitude to store in mysql database?

3 Answers3