Search code examples
mysqldatabase-performancescaling

Performance wise, what's the optimal way to store lon and lat GEO data in mysql?

In the process of teaching myself how to optimally design and build a database I've stumbled on the following question: what's the optimal way to store GEO-locations for addresses?

After doing some research I've found out there are two possibilities. Using either the MySQL geometry function and storing them as a point(lon, lat) in a single column.

Or storing them in 2 seperate columns as lon float(10,6) and float(10,6).

However I've found little information on how many bytes a geometry function would use to store information.

Sample create table script

CREATE TABLE lonlatAsGeometry (
    ID INT,
    lonlat GEOMETRY
);

INSERT INTO lonlatAsGeometry VALUES (1, point(38.34886, -130.42156));

Sample #2

CREATE TABLE lonlatAsFloat (
    ID INT,
    lon FLOAT(10,6),
    lat FLOAT(10,6)
);

INSERT INTO lonlatAsFloat VALUES (1, 38.34886, -130.42156);
Solution

  • I've found out that a float uses 4 bytes to store data,

    whilst a point uses up to 25 bytes depending on the length of the values in it.

    Source = https://dev.mysql.com/doc/refman/5.7/en/gis-data-formats.html