What data type should I use to store latitude and longitude?

[Nigel]

I want to create a table storing the latitude and longitude of airports.
What data type should I use to store a latitude or longitude ... such as 34°
3' 59" N, 118° 7' 28" W, the location of Los Angeles Airport (LAX)?

Should I convert the angles to decimal and use Single?

All help appreciated.

Cheers

Nige'

 

[Fred]

If you are going to store them where the use will be humans looking at them
(in reports, forms, queries etc.) , you could just a text field.

If you are going to have a program use them in an automated fashion, you'll
need to store them in the way required for that program.

BTW, if the areas of interest is local enough to not span multiple "blocks"
if you can get the raw data in UTM coordinate form, it will be much simpler
to manipulate, basically X & Y coordinates in meters. Just though I'd
mention this piece of information which is irrelevant because you are dealing
with larger areas.

If it were me I'd make up 8 fields for your 8 pieces of data shown, e.g.

N (text)
34 (integter)
3 (integer)
59 (single precision number)

W (text)
118 (integter)
7 (integer)
28 (single precision number)

That way you preserve all of your options, and aren't losing any resolution
or creating false resolution in doing conversions.



Sincerely,

Fred

 

[Keith Wilby]

I want to create a table storing the latitude and longitude of airports.
What data type should I use to store a latitude or longitude ... such as
34°
3' 59" N, 118° 7' 28" W, the location of Los Angeles Airport (LAX)?

Should I convert the angles to decimal and use Single?

All help appreciated.

Cheers

Nige'

Just off the top of my head I'd be tempted to store four separate entities
there: degrees, minutes, seconds and orientation ... but then I've never had
to design such a system so have never put it to the test.

Keith.
www.keithwilby.co.uk

 

[Maarkr]

You may want to store it as Number...Double. I've designed many db using
this and doing flight calculations. Use postive numbers for N and E,
negative for S & W. Data will be stored like -68.8281388888889 instead of
LatLong values to make computing easier. I forget it now, but there's a
conversion routine to show Lat/Long. The FAA and ICAO databases use the same
method and for GPS use, if you can find their db and import data, ie,
airfield info. If you want to show N,S,E,W, you'll need to use an unbound
field and show N for the + lat values, etc. Use the others' suggestions if
you're storing it in strictly Lat/Long and not computing anything.

 

[Keith Wilby]

If it were me I'd make up 8 fields for your 8 pieces of data shown, e.g.

N (text)
34 (integter)
3 (integer)
59 (single precision number)

W (text)
118 (integter)
7 (integer)
28 (single precision number)

That way you preserve all of your options, and aren't losing any
resolution
or creating false resolution in doing conversions.

Hi Fred. I would have thought that four fields would be sufficient in a
table at the many end of a 1:M since each location has two grid references.

Keith.

 

[Fred]

Hello Keith,

I'm sure that both would work fine.

If you consider latitude and longitude data to both be instances of
"coordinate data" then normalization would require your method. If you
consider them to be two different types of data, then mine is OK. Mine is
lower tech, and I'm a low tech kinda guy.

Fred

 

[Fred]

Hello Maarkr,

This is more for a fun discussion than anything, but one could argue that
your conversion is a calculation best done from the data at time of need.
And by doing the conversion before storing:

- if you use lots of decimal places (e.g. your solution) you lose the
record of the resolution of the individual number = implying resolution
("accuracy") that didn't exist in the original number.

- if you use fewer decimal places, you lose accuracy that was available.

BTW I think it would be decimal degrees = [degrees] + [minutes]/60 +
[seconds]/3600

Plus carry the N/S/E/W data through separately or as a sign

 

[Nigel]

Well bummer! I'd been searching everywhere for a data type to store this
information. As we live in a 3D world you would have thought that the bright
boys at Microsoft would have come up with a data type for storing "polar"
coordinates ... but then I guess that a crt monitor projection is only in 2D.
Still, a lot of modern games process 3D geometry...

So ... what is the data going to be used for? Well, it will be input by the
user, so it needs a friendly input Form. Secondly, it will be displayed on
the screen whenever the user selects an airport, so it needs to be formatted
as text. Finally, it will be used mathematically to calculate the distance
between two airports, so I'll need to convert it to a number.

Here's what I propose. I like Fred's idea of storing it as a simple text
field. I'm thinking of a format that is quick and easy to enter, say,
-34d56'25.8". The minus (-) will cover South for latitude and West for
longitude. When the user selects OK, I'll parse the string and replace the
degrees (d) with the ° character and replace the minus (-) with S or W as
required, so that it displays correctly: 34°56'25.8" S. I've just checked
and the Text data type will hold the ° character. That way the data will be
stored in a format that matches the way we typically see it in real life.

When I want to use it for calculations I'll convert the data to decimal
degrees. I'll take Maarkr's advice on this one and use the Number/Double
data type. While I could do this at the time it is input it means that I am
storing more (duplicate) data and a greater chance of error. I won't need to
do many calculations at any time as I'll be comparing two airports only. IF
I was going to do batch operations on hundreds of coordinates I would store
the data as decimal degrees.

It should be fun writing the functions to do this.

Fred, your idea of 8 fields was interesting ... but I can't imagine how it
would be beneficial. While it simplifies converting to decimal it must be
harder to enter ...

Truely, I am surprised that there is no dedicated data type for this ... I
mean, the Date data type is pretty specific ... so why not Polar ?!?

Any more advice is greatly appreciated.

Cheers

Nige'

 

[John W. Vinson]

Well bummer! I'd been searching everywhere for a data type to store this
information. As we live in a 3D world you would have thought that the bright
boys at Microsoft would have come up with a data type for storing "polar"
coordinates ... but then I guess that a crt monitor projection is only in 2D.
Still, a lot of modern games process 3D geometry...

So ... what is the data going to be used for? Well, it will be input by the
user, so it needs a friendly input Form. Secondly, it will be displayed on
the screen whenever the user selects an airport, so it needs to be formatted
as text. Finally, it will be used mathematically to calculate the distance
between two airports, so I'll need to convert it to a number.

Here's what I propose. I like Fred's idea of storing it as a simple text
field. I'm thinking of a format that is quick and easy to enter, say,
-34d56'25.8". The minus (-) will cover South for latitude and West for
longitude. When the user selects OK, I'll parse the string and replace the
degrees (d) with the ° character and replace the minus (-) with S or W as
required, so that it displays correctly: 34°56'25.8" S. I've just checked
and the Text data type will hold the ° character. That way the data will be
stored in a format that matches the way we typically see it in real life.

The downside of your suggestion is that searching for text strings requires
either ' or " as a string delimiter; strings including either ' or " are
harder to search, and strings containing BOTH delimiters - as you suggest -
are hardest of all. It can be done but it's a PITA.

The four fields idea is not quite as bad as you postulate. You can have four
textboxes (actually a List of Values combo for NS) cheek by jowel on the form,
and have the tab order and input masks and AutoTab properties set so that you
can simply type 0345625.8S (without any tabs or punctuation other than the
period) to enter all of the values. You could use either four table fields and
bound textboxes, or (my preference) unbound textboxes and a separate,
invisible, bound textbox bound to a Single or Double number field, with code
to parse the numbers into a numeric value (and corresponding code in the
form's Current event to parse the number into the four textboxes.

One advantage of this would be that you could do validity checking in the
parsing so that users couldn't enter suspect data such as 42d84'93"S.

 

[Fred]

Nigel,

I think that your main topic is pretty well covered. Couple of extra fun
notes on your last post.

Microsoft create a standard? That's the opposite of what they do....they
work tirelessly to keep standards from emerging by creating their own
corrupted, fuzzy versions of any that are emerging.

I have / learn from friends who have to deal with true 3D co-ordinate
systems for the planet.....it is 100 times as complicated than one would
think and enough to make my head explode. Including that the earth is not a
sphere, and neither is the imaginary datum of "sea level".

Polar coordinates are for a flat (2d) surface.

To make it managable for us mere mortals, we treat the earth's surface as a
2D object, albeit wrapped in 3D space. And there are various co-ordinate
systems available for doing that, the most popular being longitude and
latitude, with UTM the probable runner up.

A distance calculation from the longitude and latitude of two points can be
done but it's a really complicated equation.

Databases store nearly every type of information on the planet and so I
would expect for there to be field types specialized to individual fields of
interest.

Sounds like you have a cool project. Have fun!

 

[Fred]

Correction on last paragraph ......and so I would NOT expect there to be
field types specialized to individual fields of interest