Which data type for latitude and longitude?

postgresql types latitude-longitude postgis
user1008404 picture user1008404 · Nov 16, 2011 · Viewed 116k times · Source

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?

Answer

Erwin Brandstetter picture Erwin Brandstetter · Nov 16, 2011

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.

Related questions

Difference between text and varchar (character varying)

What's the difference between the text data type and the character varying (varchar) data types? According to the documentation If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension. …

Read More
How to concatenate columns in a Postgres SELECT?

I have two string columns a and b in a table foo. select a, b from foo returns values a and b. However, concatenation of a and b does not work. I tried : select a || b from foo and select …

Read More
Difference between timestamps with/without time zone in PostgreSQL

Are timestamp values stored differently in PostgreSQL when the data type is WITH TIME ZONE versus WITHOUT TIME ZONE? Can the differences be illustrated with simple test cases?

Read More