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

Question

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.

Answer 1

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