Which data type for latitude and longitude? Which data type for latitude and longitude? postgresql postgresql

Which data type 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.


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;


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