Thread: Storing latitude and longitude

Storing latitude and longitude

From
"Rodolfo J. Paiz"
Date:
Hi:

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

Having one-second precision is good enough, so three integers will do.
Of course, ideally I'd have two integers and a numeric for the seconds
so I could have fractional seconds too.

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?

Clearly this is not an important consideration (in fact I already
created the table with two integers and a numeric field), but I thought
I'd ask for the educational value of the discussion.

Cheers,

--
Rodolfo J. Paiz <rpaiz@simpaticus.com>


Re: Storing latitude and longitude

From
Michael Fuhr
Date:
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/

Re: Storing latitude and longitude

From
"Rodolfo J. Paiz"
Date:
On Thu, 2005-02-03 at 18:31 -0700, Michael Fuhr wrote:
> 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.).
>

You are entirely correct mathematically. I do prefer DMS, however, based
on human factors:

   1. I am the only one who will input data, and likely for fewer than
40-50 records in total. Some inconvenience in data input is OK.

   2. DMS is how my handheld GPS presents data, and is easier for me to
remember and find intuitively on a chart when actually looking for an
airport.

   3. There will be little or no need for formulaic manipulation. I have
less interest in direct or great-circle distances than I do in actual
distance *flown*, and I intend to input the actual routes and distances
(again, as measured by charts and/or GPS).

I'll do some research on the 8.0 composite types, although I'll be stuck
on 7.x for a while yet. I'll also check out the contrib/earthdistance
module... thanks for your comments on both. This is the kind of thing I
was hoping to learn from this discussion.

On a more trivial note, I find myself now wondering what linear distance
over land is actually represented by one arc-second of latitude or
longitude... will have to figure out the math involved. :-)

Cheers,

--
Rodolfo J. Paiz <rpaiz@simpaticus.com>


Re: Storing latitude and longitude

From
Bruno Wolff III
Date:
On Thu, Feb 03, 2005 at 18:31:45 -0600,
  "Rodolfo J. Paiz" <rpaiz@simpaticus.com> wrote:
> Hi:
>
> 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:

The earthdistance contrib module has a domain defined over the base type
cube (and contrib module) that can be used to store points on the surface
of a 3D sphere. The coordinates are stored in double precision, which should
be good enough for your requirements/

It shouldn't be too hard to have your application convert from DMS to
fractional degrees on input and output.