What MySQL data type should be used for Latitude Longitude with 8 decimal places

Question

I m working with map data  and the Latitude Longitude extends to 8 decimal places  For example   Latitude 40 71727401 Longitude -74 00898606   I saw in the Google document which uses     lat FLOAT  10  6   NOT NULL    lng FLOAT  10  6   NOT NULL   however  their decimal places only go to 6  Should I use FLOAT 10  8  or is there another method to consider for storing this data so it s precise  It will be used with map calculations  Thanks

User · Answer

I believe the best way to store Lat Lng in MySQL is to have a POINT column  2D datatype  with a SPATIAL index    CREATE TABLE  cities       zip  varchar 8  NOT NULL     country  varchar  2  GENERATED ALWAYS AS  SUBSTRING  zip   1  2   STORED     city  varchar 30  NOT NULL     centre  point NOT NULL    PRIMARY KEY   zip      KEY  country    country      KEY  city    city      SPATIAL KEY  centre    centre     ENGINE InnoDB    INSERT INTO  cities    zip    city    centre   VALUES   CZ-10000    Prague   POINT 50 0755381  14 4378005

User · Answer

Using migrate ruby on rails  class CreateNeighborhoods  lt  ActiveRecord  Migration 5 0    def change     create table  neighborhoods do  t        t string  name       t decimal  latitude  precision  15  scale  13       t decimal  longitude  precision  15  scale  13       t references  country  foreign key  true       t references  state  foreign key  true       t references  city  foreign key  true        t timestamps     end   end end

User · Answer

MySQL now has support for spatial data types since this question was asked  So the the current accepted answer is not wrong  but if you re looking for additional functionality  like finding all points within a given polygon then use POINT data type    Checkout the Mysql Docs on Geospatial data types and the spatial analysis functions

User · Answer

Do not use float    It will round your coordinates  resulting in some strange occurrences   Use decimal

User · Answer

MySQL supports Spatial data types and Point is a single-value type which can be used  Example  CREATE TABLE  buildings       coordinate  POINT NOT NULL       Even from v5 7 5 you can define an index for it      SPATIAL INDEX  SPATIAL    coordinate     ENGINE InnoDB      then for insertion you can    INSERT INTO  buildings     coordinate    VALUES  POINT 40 71727401 -74 00898606

User · Answer

in laravel used decimal column type for migration    table- gt decimal  latitude   10  8    table- gt decimal  longitude   11  8     for more information  see available column type

User · Answer

Additionally  you will see that float values are rounded       e g  given values 41 0473112 29 0077011  float 11 7    decimal 11 7  --------------------------- 41 0473099    41 0473112 29 0077019    29 0077011

User · Answer

You can set your data-type as signed integer  When you storage coordinates to SQL you can set as lat 10000000 and long 10000000  And when you selecting with distance radius you will divide storage coordinates to 10000000  I was test it with 300K rows  query response time is good    2 x 2 67GHz CPU  2 GB RAM  MySQL 5 5 49

[mysql] What MySQL data type should be used for Latitude/Longitude with 8 decimal places?

Examples related to mysql

Examples related to types

Examples related to floating-point