Which data type for latitude and longitude

Question

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

User · Answer

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);

User · Answer

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

User · Answer

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  lt - gt  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

User · Answer

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

User · Answer

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  lt - gt   SRID 4326 POINT lon lat    limit 5

User · Answer

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

[postgresql] Which data type for latitude and longitude?

Examples related to postgresql

Examples related to types

Examples related to latitude-longitude

Examples related to postgis