Which data type for latitude and longitude?

latitude and longitude data type in postgresql
datatype for latitude and longitude c#
data type for latitude and longitude java
latitude and longitude data type mysql
hive latitude data type
redshift latitude longitude data type
best data type for lat long
data type for location in sql

I am newbie to PostgreSQL and PostGIS. I want to store latitude and longitude values in PostgreSQL 9.1.1 database table. I will calculate distance between two points, find nearer points by using this location values.

Which data type should I use for latitude and longitude?

You can use the data type point - combines (x,y) which can be your lat / long. Occupies 16 bytes: 2 float8 numbers internally.

Or make it two columns of type float (= float8 or double precision). 8 bytes each. Or real (= float4) if additional precision is not needed. 4 bytes each. Or even numeric if you need absolute precision. 2 bytes for each group of 4 digits, plus 3 - 8 bytes overhead.

Read the fine manual about numeric types and geometric types.


The geometry and geography data types are provided by the additional module PostGIS and occupy one column in your table. Each occupies 32 bytes for a point. There is some additional overhead like an SRID in there. These types store (long/lat), not (lat/long).

Start reading the PostGIS manual here.

What datatype to use when storing latitude and longitude data in , . A JSON object containing the U.S. address. This may be null. Latitude and longitude are used to locate points on the globe as a pair of numbers. Latitude represents how far north/south the point is and longitude represents how far east/east the point is. The numbers are often expressed in degrees because the earth is approximately a sphere.

In PostGIS, for points with latitude and longitude there is geography datatype.

To add a column:

alter table your_table add column geog geography;

To insert data:

insert into your_table (geog) values ('SRID=4326;POINT(longitude latitude)');

4326 is Spatial Reference ID that says it's data in degrees longitude and latitude, same as in GPS. More about it: http://epsg.io/4326

Order is Longitude, Latitude - so if you plot it as the map, it is (x, y).

To find closest point you need first to create spatial index:

create index on your_table using gist (geog);

and then request, say, 5 closest to a given point:

select * 
from your_table 
order by geog <-> 'SRID=4326;POINT(lon lat)' 
limit 5;

Location Datatype, How do you store latitude and longitude in a database? Latitude: -7.39755000 Longitude: 107.80627000. If data type decimal, it won't save because SQL Server said: cannot convert numeric data to numeric. as I remembered. But if type data was float, SQL Server will convert 0 so the latitude will be -7.39755000.

I strongly advocate for PostGis. It's specific for that kind of datatype and it has out of the box methods to calculate distance between points, among other GIS operations that you can find useful in the future

Which comes first, latitude or longitude, is written with a number, followed by either “east” or “west” depending on whether it is located east or west of the Prime Meridian. Geography Point data type requires longitude and latitude as a float, but suggested data type is decimal.

If you do not need all the functionality PostGIS offers, Postgres (nowadays) offers an extension module called earthdistance. It uses the point or cube data type depending on your accuracy needs for distance calculations.

You can now use the earth_box function to -for example- query for points within a certain distance of a location.

What is latitude and longitude?, in terms of meridians, which are half-circles running from pole to pole. data = [ (48.82694828196076,2.367038433387592), (48.82694828196076,2.367038433387592) ] The tuple can - of course - be replaced by a list at any point. Especially since it only stores 2 floats, the access to it is not that costly, you won't really notice the difference at all. This would then yield to a nested list:

In PostGIS Geometry is preferred over Geography (round earth model) because the computations are much simpler therefore faster. It also has MANY more available functions but is less accurate over very long distances.

Import your CSV to long lat fields to DECIMAL(10,6) columns. 6 digits is 10cm precision, should be plenty for most use cases.

Then cast your your imported data

SELECT 
    --ST_SetSRID(ST_Point(long, lat),4326) geom -- the wrong way because SRID not set in geometry_columns table
    ST_Point(long, lat)::geometry(Geometry, 4326) geom
INTO target_table
FROM source_table;

Verify SRID is not zero!

SELECT * FROM public.geometry_columns WHERE f_table_name = 'target_table';

Validate the order of your long lat parameter using a WKT viewer and ST_AsEWKT(target_table.geom).

Then index it for best performance

CREATE INDEX idx_target_table_geom_gist
    ON target_table USING gist(geom);

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  1 In database you should use "sql spatial data type" to store longitude and latitude – azhar_SE_nextbridge Jan 21 '15 at 13:16 8 Note that the.NET BCL itself uses doubles in its GeoCoordinate class, which is a strong indication that the precision might be sufficient.

Latitude and Longitude should use DECIMAL datatype instead of , Recently we encountered problem with accurate lat/long storing when being passed by request parameter. It looks like it's connected strictly  SQL Server type: float. CLR type: SqlDouble. Remarks. In the OpenGIS model, Lat is defined only on geography instances composed of a single point. This property will return NULL if geography instances contain more than a single point. This property is precise and read-only. Examples. This example creates a point and returns the latitude of the

Latitude and Longitude with the Geography Data Type, The Geography data type is used in many different SQL Server databases. It contains Latitude Duration: 1:55 Posted: Aug 30, 2017 As of MySQL 5.7, consider using Spatial Data Types (SDT), specifically POINT for storing a single coordinate. Prior to 5.7, SDT does not support indexes (with exception of 5.6 when table type is MyISAM). Note: When using POINT class, the order of the arguments for storing coordinates must be POINT(latitude, longitude).

LatitudeLongitudeAltitude, Basic geo-vocabulary is a candidate which presents the notation for this. The latter will be identified as a latitude longitude data type. A lot of specifications 

Comments
  • If you're doing two points (2D lat/lon map) I'd use the Geometry data type. If you need to introduce altitude or curvature of the earth in your distance calcs, Geography is where you want to go.
  • Did any of the answers below address your question? If so, I encourage you to select one as the answer :)
  • is there an answer for this?
  • I would not recommend using the float datatype. It makes calculation with the coordinates very complicated. You should use PostGIS and the geography datatype for such calculations.
  • It really is a fine manual isn't it? A shining example in documentation.
  • Would it be faster to store long, lat, and geog over trying parse geog for the original long lat?
  • @Dan: Depends. Please ask a new question with details. You can always link to this one for context. Comments are not the place for new questions.
  • Clarification the SRID 4326 wants latitude longitude in that order. But PostGIS's interpretation of SRID 4326 wants longitude latitude in that order. Example is correct for PostGIS use. postgis.net/2013/08/18/tip_lon_lat