Обсуждение: Storing latitude and longitude
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>
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/
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>
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.