Best Way to Handle Different Latitude and Longitude Formats

[Ecologist_Guy]

Hi everybody,



I am trying to create a database that can work with different formats of latitude and longitude. Specifically, I want users to be able to enter one of these three formats on a form and for the database automatically convert among the three formats:

Degrees-Minutes-Seconds: DDD ^o MM' SS.S"
Degrees-Decimal Minutes: DDD ^o MM.MMMM'
Decimal Degrees: DDD.DDDDD ^o

I am fairly new to Access so I am curious how others would handle this situation. I was thinking of having each format listed in my table and the associated form. Users would only enter coordinates in one format and Access would automatically calculate the other formats and auto-fill them. Is this possible? What is the best approach to doing something like this? The mathematical conversions are not hard but I'm not sure of the best approach to take when coding this. Perhaps there is a better way to approach this.

In case anybody is curious, the reason we don’t use one standard format is because this is a citizen science project marking sea turtle nest sites. Volunteers in the community collect data and they could be using smart phone apps or GPS units to mark their location. Some of the participants can’t change their units or don’t know how. Last year we realized that many made conversion errors while submitting their data. From my perspective, it seems like it would be easier to let them use the format they are familiar with and let access handle the conversions. The easier we make the process for them the more participation we get and the more thorough our survey is.

Thanks for your help.

[aytee111]

Interesting question! The safest way I would guess is to store them in three separate fields - D/M/S, each being Integer with no decimals. Another way I have seen it done is with one field (double) with degrees as the whole number and minutes/seconds as the decimals. Conversion would be a simple math formula in any situation.

[ranman256]

i would just have a text field, just store the coords in it , As Is.
just like google, any app should be able to read the text in ANY format,
deg-min-sec
decimals,
etc.

[Bulzie]

and for the database automatically convert among the three formats:

Can you explain that part? So they enter the data in one of those 3 formats and you want the program to format the other 2? Can you give a sample of what data they would enter and what the formats would look like?

[Ecologist_Guy]

Can you explain that part? So they enter the data in one of those 3 formats and you want the program to format the other 2? Can you give a sample of what data they would enter and what the formats would look like?

I'll try to explain it a little better. Surveyors collect data for each nest. In reality they collect quite a bit of information but for the sake of this post I left a lot of the fields out. A critical piece of information is the exact location of the nest. Which is typically collected in three formats. All of these examples below represent the same location but are expressed in the different formats. I'll just use a latitude to illustrate the point. Normally, one would also have a longitude but I'm just using latitude as an example. Longitude will almost be identical so I'll deal with that after I figure the latitude out.
1. Degrees, Minutes, Seconds = 29^o 35' 14.73"
2. Degrees, Decimal, Minutes = 29^o 35.2455
3. Decimal Degrees = 29.587425

Following aytee111's advice I separated the coordinates into their parts in my table (see below). I think this will make it easier to code. The math is straightforward and I explained it below in case it will help somebody help me, but perhaps my question wasn't clear. I am wondering how to use code to program Access to recognize that one of the formats has been entered in it's own field and then convert the it to the other formats and fill in the rest of the fields.


There are 60 seconds in a minute and 60 minutes in a degree. So to convert from degrees, minutes, seconds to degrees decimal, minutes you convert the seconds into a decimal and add it to the minutes, E.g. 14.73"/60 = .2455 thus
29^o 35' 14.73" = 29^o 35' + 14.73"/60' = 29^o 35.2455'

Similarly, one can convert the Degrees Decimal minutes to decimal degrees by converting the minutes to a degree. E.g.
35.2455/60 = .587425, thus 29^o 35.2455 = 29+ .587425 =29.587425.

I hope the math lesson isn't too much but it might help explain what I am trying to do.

Because volunteers are collecting the data in multiple formats I am hoping their is a way to use come VBA code so they can enter any one of the formats in a form. Then Access will recognize that a coordinate has been entered and convert it to the other formats and fill in the rest of the fields.

For example, in the table below the latitude was entered in different formats. I am hoping I can use some code so access will autofill the fields in yellow.



The tricky part is I don't know which format will be entered and which ones will be left blank. It will depend on the user.

At the moment, I am thinking that it might be more trouble than it is worth. It may easier to train as many volunteers as I can to use the standard decimal degrees. I could manually convert any data that wasn't submitted in decimal degrees. On the other hand, figuring this out could be a good learning experience but I'm not even sure where to begin.

[aytee111]

Have separate fields on your form giving the user a choice of how to enter the information. Such as (1) three separate fields D/M/S (2) two fields D/M.dd and (3) one field for decimal D. Then you won't have to figure out which format has been used and can calculate the other fields easily. Your Lat_Dec_Deg value above looks incorrect, shouldn't it be 29.something?

[orange]

Don't confuse data entry with data storage.
Hopefully you don't work directly with tables.

I recommend you store the data in some consistent format.

We need to know more about your data entry process. Perhaps you could lead us through the process with a couple of examples.
You can have a routine to determine the incoming format and act accordingly (at least in theory) and store the info consistently.

Here is a link to Chip Pearson's site re Lat/Long and Great Circle info.