CODEWITHSUNDEEP
databasepostgresqlgpsstore
plewas
plewas

Reputation: 248

Deg Min Secs in database

What is the best data format to store in database gps position like this:

38°57'33.804"
95°15'55.739"

I can't convert this to other formats.

DB: PozstgrSQL 9.4

Upvotes: 0

Views: 695

Answers (6)

paxdiablo
paxdiablo

Reputation: 881403

The best way to store data depends entirely on what you want to do with the data.

For example, if you are storing the GPS coordinates of towns in the world and simply want to look them up, then you can store it exactly as is. There is absolutely no need to manipulate or compare the data in that case.

However, if you want to compare the data, such as finding nearby towns, you are best off storing it in a way that allows this comparison rather easily. Given that the values are sexagesimal (base 60), probably the easiest way to do that is to convert as follows:

38°57'33.804" (38 degrees, 57 minutes, 33.804 seconds)
= (38 * 60 + 57) * 60 + 33) * 1000 + 804
= 140,253,804

For the maximum range -180..180, you would end up with a value ± 324,000,000 which easily fits in a 32-bit signed integer.

Upvotes: 1

Chris Travers
Chris Travers

Reputation: 26464

Here would be my recommendation. It's a little clunky so bear with me. You could also probably do this in C quite a bit better. However here is a SQL-based approach. What you lose in not going with a C-based approach is an ability to quickly and easily convert this into a specific output format.

 CREATE TYPE degminsec AS (
       deg int,
       min int,
       sec numeric
 );
 CREATE OR REPLACE FUNCTION decdeg_to_degminsec(numeric) RETURNS degminsec 
 LANGUAGE SQL AS $$
    SELECT floor($1), 
           floor(($1 - floor($1)) * 60), 
           ($1 * 60 - floor($1 * 60)) * 60;
 $$;

 CREATE OR REPLACE FUNCTION display(degminsec) RETURNS text
 LANGUAGE SQL AS  $$
 SELECT $1.deg || '°' || $1.min || '''' || $1.sec || '"';
 $$;

The major disadvantage of this approach is you would have a type that would be problematic to index and you'd have to convert it on output. For anything other than btree indexing this is just the beginning of the project. And if you don't convert on output, instead of 38°57'33.804" you would get (38,57,33.804)

Now, you may decide this is too much trouble, but note that if you continue to store decimal coordinates you can still convert it on the way out as a display format, which would likely solve your problems. With the above, if you arent formatting a lot of these at once, you could just:

 SELECT display(decdeg_to_degminsec(mycoord)) from mytable;

But it would be easier to write another function that would be syntatically easier to read for that purpose.

Upvotes: 0

AlexWien
AlexWien

Reputation: 28727

The best format is to
1) convert to decimal degrees: e.g -73,123456
2) then multiply with 1E7 (10000000)
3) cast or round to integer

This then is a signed int which needs only 4 bytes, coordinate granularity is then in range of some milimeters.

But it also depends what your goal is: Mass data storage with minimum space? See above.

Or data accessible via queries:
Then I would use that format that the Postgres provides for coordinates

Upvotes: 0

Frank Heikens
Frank Heikens

Reputation: 127086

Did you check PostGIS? It's made for dat like this

Upvotes: 2

LarsN
LarsN

Reputation: 33

It depends on what you want to do with the data.

If you just want to store it I recommend using varchar. Escape the string using $$ on each side instead of single quotes.

If you want to work with the data you could create an own user defined datatype (UDT).

Upvotes: 0

Joseph Payton
Joseph Payton

Reputation: 1

My first thought would be to store the position as decimal degrees.

If that is unacceptable and you need to store DMS, you could store it either as a VARCHAR, or even "convert" it all to decimal thousandths of a second and store it as an integer, that way you keep your precision.

Upvotes: 0

Related Questions