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.


You can use the builtin POINT datatype without postgis.

| improve this answer | |

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
| improve this answer | |

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.