If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
What data type should I use to store latitude and longitude?
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' |
#2
|
|||
|
|||
What data type should I use to store latitude and longitude?
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 |
#3
|
|||
|
|||
What data type should I use to store latitude and longitude?
"Nigel" wrote in message
... 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 the 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 |
#4
|
|||
|
|||
What data type should I use to store latitude and longitude?
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. "Nigel" wrote: 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' |
#5
|
|||
|
|||
What data type should I use to store latitude and longitude?
"Fred" wrote in message
... 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. |
#6
|
|||
|
|||
What data type should I use to store latitude and longitude?
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 |
#7
|
|||
|
|||
What data type should I use to store latitude and longitude?
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 |
#8
|
|||
|
|||
What data type should I use to store latitude and longitude?
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' "Nigel" wrote: 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' |
#9
|
|||
|
|||
What data type should I use to store latitude and longitude?
On Mon, 30 Mar 2009 15:47:01 -0700, Nigel
wrote: 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. -- John W. Vinson [MVP] |
#10
|
|||
|
|||
What data type should I use to store latitude and longitude?
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! |
|
Thread Tools | |
Display Modes | |
|
|