Decimal or Point Data Type for storing Geo location data in MySQL

I want to store Geo location for each Address in my MySQL(Innodb) "tblAddress" table and I think I have 2 Options:

1) 2 columns for Each Address:
Latitude DECIMAL(10, 8), 
Longitude DECIMAL(11, 8)

2) One Column for Each Address:
GeoLocation POINT

My program need to find all Address that fall under 1 Kilometer Radius from current Location (Mobile Client).

Also below command does not work for me, not sure what I need to have for this.

CREATE SPATIAL INDEX GeoLocation ON tblAddress (GeoLocation);

So what is the suggested approach to store data for faster calculation and retrieval here. Please advise.

You could use MySQL's spatial extensions with GIS.

In a code example from Google displaying points on a map, they state:


When you create the MySQL table, you want to pay particular attention
to the lat and lng attributes. With the current zoom capabilities of
Google Maps, you should only need 6 digits of precision after the
decimal.


To keep the storage space required for our table at a minimum, you can
specify that the lat and lng attributes are floats of size (10,6).
That will let the fields store 6 digits after the decimal, plus up to
4 digits before the decimal, e.g. -123.456789 degrees I wouldn't worry
about performance differences between numeric types. Decent indices
will have a far greater effect.

You could also try DECIMAL as @gandaliter suggest.


DECIMAL is the MySQL data-type for
exact arithmetic. Unlike FLOAT its precision is fixed for any size of
number, so by using it instead of FLOAT you might avoid precision
errors when doing some calculations. If you were just storing and
retrieving the numbers without calculation then in practice FLOAT
would be safe, although there's no harm in using DECIMAL. With
calculations FLOAT is still mostly ok, but to be absolutely sure of
8d.p. precision you should use DECIMAL.


Latitudes range from -90 to +90 (degrees), so DECIMAL(10,8) is ok for
that, but longitudes range from -180 to +180 (degrees) so you need
DECIMAL(11,8). The first number is the total number of digits stored,
and the second is the number after the decimal point.
In short: lat DECIMAL(10,8) NOT NULL, lng DECIMAL(11,8) NOT NULL
This explains how MySQL works with floating-point data-types.

Related information:

• MySQL and GIS, GeoIP: FAQs, How-To, Articles, Blogs

• Creating a Store Locator with PHP, MySQL & Google Maps

• Using the new spatial functions in MySQL 5.6 for geo-enabled applications

StackExchange Database Administrators Q#107089, answer score: 12