What MySQL data type should be used for Latitude/Longitude with 8 decimal places?
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));
What is the ideal data type to use when storing latitude / longitude in a MySQL database?
Use MySQL's spatial extensions with GIS.
What datatype to use when storing latitude and longitude data in SQL databases?
For latitudes use: Decimal(8,6)
, and longitudes use: Decimal(9,6)
If you're not used to precision and scale parameters, here's a format string visual:
Latitude and Longitude##.######
and ###.######
To 6 decimal places should get you to around ~10cm of accuracy on a coordinate.
What is the best mysql datatype for storing latitudes and Longitudes?
based on my experience if you need only lat and lng as seprated values an otimal solution is based on data type
decimal(16,12)
with this format you can localize point on maps with a precision very near to millimeter, and this for normal maps application is more than enough
What datatype to use when storing latitude and longitude data in DB2?
Below is a list of all Numeric Datatypes for DB2.
http://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.intro/src/tpc/db2z_numericdatatypes.dita
I would use a decimal or decimal float because they will :
1) Provide the ability to enter decimals
2) Give you a datatype that can hold large amounts ( more of the memory would be allocated to the datatype)
Latitude and Longitude MySQL Field Type
Both are wrong! The google document you have linked to is from 2009, mysql has come a long way since then. Particularly the introduction of JSON and wait for it .... spatial data types in mysql 5.7
Since it's introduction, the correct way to store a location is as a POINT field. This also open up a range of spatial data functions for you. Things that you would otherwise need to write by hand as indeed developers had to do before it's introduction.
The built in geospatial functions are capable of using indexes while home made functions are often slower due to being unable to make proper use of indexes.
SQL Server latitude and longitude data type
DECIMAL(9,6)
is what I normally use for both. If you need more precision, then try DECIMAL(12,9)
.
You could also use the GEOGRAPHY
data type, but it will take more space, and I would only recommend this if you need SQL Servers spatial features (indexing etc).
For the actual conversion, try this:
CAST(@YourLat AS DECIMAL(12,9))
Related Topics
How to Update Top 100 Records in SQL Server
How to Return Rows with a Specific Value First
Finding Similar Strings with Postgresql Quickly
Could Not Find Stored Procedure 'Dbo.Aspnet_Checkschemaversion'
Create Table If Not Exists Equivalent in SQL Server
Which SQL Query Is Faster? Filter on Join Criteria or Where Clause
Copy Rows from One Table to Another, Ignoring Duplicates
Why Use Select Top 100 Percent
Detect Consecutive Dates Ranges Using SQL
Calculate Business Hours Between Two Dates
Getting Dates Between a Range of Dates