Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 13 down vote accepted

You can use the builtin POINT datatype without postgis.

share|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
share|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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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