[postgresql] Which data type for latitude and longitude?

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?

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

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 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 long and lat fields to DECIMAL(10,6) columns. 6 digits is 10cm precision, should be plenty for most use cases. Then cast your imported data to the correct SRID

The wrong way!

/* try what seems the obvious solution */
DROP TABLE IF EXISTS public.test_geom_bad;
-- Big Ben, London
SELECT ST_SetSRID(ST_MakePoint(-0.116773, 51.510357),4326) AS geom
INTO public.test_geom_bad;

The CORRECT way

/* add the necessary CAST to make it work */
DROP TABLE IF EXISTS public.test_geom_correct;
SELECT ST_SetSRID(ST_MakePoint(-0.116773, 51.510357),4326)::geometry(Geometry, 4326) AS geom
INTO public.test_geom_correct;

Verify SRID is not zero!

/* now observe the incorrect SRID 0 */
SELECT * FROM public.geometry_columns
WHERE f_table_name IN ('test_geom_bad','test_geom_correct');

Validate the order of your long lat parameter using a WKT viewer and

SELECT ST_AsEWKT(geom) FROM public.test_geom_correct

Then index it for best performance

CREATE INDEX idx_target_table_geom_gist
    ON target_table USING gist(geom);

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.

Use Point data type to store Longitude and Latitude in a single column:

CREATE TABLE table_name (
    id integer NOT NULL,
    name text NOT NULL,
    location point NOT NULL,
    created_on timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT table_name_pkey PRIMARY KEY (id)
)

Create an Indexing on a 'location' column :

CREATE INDEX ON table_name USING GIST(location);

GiST index is capable of optimizing “nearest-neighbor” search :

SELECT * FROM table_name ORDER BY location <-> point '(-74.013, 40.711)' LIMIT 10;

Note: The point first element is longitude and the second element is latitude.

For more info check this Query Operators.

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;