CODEWITHSUNDEEP
phpmysql
Ahmed el-Gendy
Ahmed el-Gendy

Reputation: 436

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

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?

Upvotes: 8

Views: 5215

Answers (3)

assaqqaf
assaqqaf

Reputation: 1585

You can use spatial extensions in mysql the datatype is POINT

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

Upvotes: 5

Rene Pot
Rene Pot

Reputation: 24815

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

Upvotes: 3

Salman Arshad
Salman Arshad

Reputation: 272086

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.

Upvotes: 2

Related Questions