Re: Storing latitude and longitude

On Thu, Feb 03, 2005 at 06:31:45PM -0600, Rodolfo J. Paiz wrote:
>
> One of my tables will store latitude and longitude information. I've
> checked and I cannot see a datatype for that (which is no surprise, and
> no loss). However, I can think of three ways to store the data:
>
> * Array field (degrees, minutes, seconds)
> * Three integer fields
> * Three numeric fields with up to 2 decimals each

Other possibilities:

* One field of numeric or other floating-point type, storing degrees
and fractions thereof. You could write functions to convert between
DDD.DDDD and DMS; you could also use domains to constrain the allowed
values to, say, -90 to 90 for latitude and -180 to 180 for longitude
(or whatever values make sense for your application).

* Composite type storing degrees and minutes as integers and seconds
as an integer or floating-point. PostgreSQL 8.0 allows tables to
have columns of a composite type.

See also the contrib/earthdistance module.

> Having one-second precision is good enough, so three integers will do.

Using a numeric with a scale of 3 (DDD.DDD) would provide 3.6 seconds
(1/1000 degree) of precision; a scale of 4 (DDD.DDDD would provide
0.36 seconds (1/10000 degree) of precision.

> Of course, ideally I'd have two integers and a numeric for the seconds
> so I could have fractional seconds too.

In PostgreSQL 8.0 you could do that with a composite type.

> Between separate fields and an array, first is an array possible? Can I
> store that? If so, is there any performance or other benefit I should
> know about?

Do you have a reason to favor DMS over DDD.DDDD? The latter is
easy to use in arithmetic expressions, such as formulae for calculating
the distance between two points (haversine, law of cosines, etc.).

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/