patternsqlMinor

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

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

withouthowrepresentpostgrespostgislatitudeusingshouldandlongitude

Problem

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.

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#3751, answer score: 6

Revisions (0)

No revisions yet.