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 ideal data type to use when storing latitude / longitude in a MySQL database?
Use MySQL's spatial extensions with GIS.
What's the best way to store co-ordinates (longitude/latitude, from Google Maps) in SQL Server?
Take a look at the new Spatial data-types that were introduced in SQL Server 2008. They are designed for this kind of task and make indexing and querying much easier and more efficient.
More information:
MS TechNet: SQL Server 2008 Spatial Data Types,
MSDN: Working with Spatial Data (Database Engine).
How to store longitude & latitude as a geography in sql server 2014?
How can i use the longitute and latitute to store location within a geography column?(because it's supposed to be only one geographic point not two right? not one for longitute and one for latitute?)
You can use geography::STPointFromText
/ geography::Point
to store longitude and latitude in a geography datatype.
SELECT geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
or
SELECT geography::Point(Latitude, Longitude , 4326)
Reference Link:
Update Geography column in table
Now that I've got the geography points, how can i select all the rows within a specific distance(in my case 2km)?
You can use STDistance
like this.
DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POINT(-122.35900 47.65129)', 4326);
SET @h = geography::STGeomFromText('POINT(-122.34720 47.65100)', 4326);
SELECT @g.STDistance(@h);
Reference Link:
Distance between two points using Geography datatype in sqlserver 2008?
Insert Query
DECLARE @GeoTable TABLE
(
id int identity(1,1),
location geography
)
--Using geography::STGeomFromText
INSERT INTO @GeoTable
SELECT geography::STGeomFromText('POINT(-122.35900 47.65129)', 4326)
--Using geography::Point
INSERT INTO @GeoTable
SELECT geography::Point(47.65100,-122.34720, 4326);
Get Distance Query
DECLARE @DistanceFromPoint geography
SET @DistanceFromPoint = geography::STGeomFromText('POINT(-122.34150 47.65234)', 4326);
SELECT id,location.Lat Lat,location.Long Long,location.STDistance(@DistanceFromPoint) Distance
FROM @GeoTable;
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))
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
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.
Related Topics
Finding the Count of Characters and Numbers in a String
Meaning of "Select Tables Optimized Away" in MySQL Explain Plan
Bulk Insert with Variable File Name
How to Use a SQL Update Statement to Add 1 Year to a Datetime Column
How to Have Multiple Pivots Using the Same Pivot Column Using SQL Server
Using 'Like' in Ssrs Expressions
How to Copy SQL Server 2008 R2 Database from One MAChine to Another
SQL Script to Find Foreign Keys to a Specific Table
Sql: Do You Need an Auto-Incremental Primary Key for Many-Many Tables
Index Autoincrement for Microsoft SQL Server 2008 R2
Sql: Group by on Consecutive Records
SQL Query to Translate a List of Numbers Matched Against Several Ranges, to a List of Values
Why Can Pl/Pgsql Functions Have Side Effect, While SQL Functions Can'T
Which Is the Best Way to Form the String Value Using Column from a Table with Rows Having Same Id
How to Fix Ora-01427 Single-Row Subquery Returns More Than One Row in Select