How should I represent a latitude and longitude in Postgres without using PostGIS?

Question

How should I represent a latitude and longitude in Postgres without using PostGIS? The system I am using does not allow SQL passthrough so I cannot use POSTGIS.

Answer 1

You can use the builtin POINT datatype without postgis.

Answer 2

You could also either use separate columns for latitude and longitude or create your own type. Either way it may be good to constrain the allowed values, in this example I also use domains to avoid repeating constraints if the type is used in more than one table:

create domain latitude_t as double precision not null 
                                             check(value>=-90 and value<=90);
create domain longitude_t as double precision not null 
                                              check(value>-180 and value<=180);

create type geocoord_t as (latitude latitude_t, longitude longitude_t);

create table my_table(id serial, geocoord geocoord_t);

insert into my_table(geocoord) values ((31.778175,35.22995));

select id, (geocoord).* from my_table;
 id | latitude  | longitude
----+-----------+-----------
  1 | 31.778175 |  35.22995

Answer 3

For non GIS application I just use columns, as suggested by Jack, though I don't bother with with the check value. It is a good idea to also specify the datum (IE NAD27) in an additional column, as the datum is important for proper interpretation of the values.