Convert Coordinates

[Guest]

I do not know how to perform the necessary math in a query for the following
situation.

I have a table in which I store coordinates for various places. The
coordinates, unfortunately, are stored in DD MM.MMM format. All the
coordinates are for North America. They are stored in two fields such as:
38° 37.501
090° 11.085

I need to convert them to DDD.DDDDD, which would yield:
38.625017
-90.18475

The formula to do that is:
split the degrees away from the decimals (38° as one string, 37.501 as
another)
divide the decimal part by 60 (37.501 / 60 = 625017)
combine it with the degree part with a decimal (38.625017)

A couple of considerations:
The west coordinate has three digits in the degrees, but the leading digit
is often a zero. Not certain that is a stumbling block, but wanted to point
it out.

Also, it is customary to indicate the west coordinate with a leading
negative symbol to distinguish it from the eastern hemisphere. No test is
needed to determine hemisphere; all will be west.

Can anyone lend a hand as to how to perfom this calculation in a query?

Thank you.

 

[Guest]

You need three functions to make this happen:

Left()
Right()
InStr()

In the query - assuming the field name of your coordinate value is
"Coordinate", this would be the formula (I gave the calculated field a name
of Test):

Test:
Left([Coordinate],(InStr([Coordinate],".")-3))+(Right([Coordinate],(Len([Coordinate])-InStr([Coordinate],".")+3))/60)


This will work whether you have 1, 2 or 3 digits for degrees.

What line of work are you in? I used to do land surveying and still write
access apps for that company.

Good luck,

Seth

 

[Guest]

Test:
Left([Coordinate],(InStr([Coordinate],".")-3))+(Right([Coordinate],(Len([Coordinate])-InStr([Coordinate],".")+3))/60)

I have:
SELECT TableName.PlaceName, TableName.NCoordinates, TableName.WCoordinates
FROM TableName
TEST:
Left([TableName.NCoordinates],(InStr([TableName.NCoordinates],".")-3))+(Right([TableName.NCoordinates],(Len([TableName.NCoordinates])-InStr([TableName.NCoordinates],".")+3))/60);

This returns "Syntax error in FROM clause".

Also, once I get this to work, I don't know how to add the second
calculation for the West coordinates. I know just enough to get myself in
trouble.

What line of work are you in? I used to do land surveying and still write
access apps for that company.

I am a school teacher. This is purely for recreation.

Thank you for the help.

 

[Guest]

Ok - with the method you used, you introduced a new problem into the equation.

First - you do not need to have the table name along with the field name,
but if you choose to use that method you must use this syntax

[TableName].[FieldName]

For the west coordinate just create a new calculated field in the next
available column on the design grid. Once you get the formula for the north
coordinate working, copy that formula into the next available column, then
change all references to the north coordinate field to be the west coordinate
field.

You are almost there,


Seth

Test:
Left([Coordinate],(InStr([Coordinate],".")-3))+(Right([Coordinate],(Len([Coordinate])-InStr([Coordinate],".")+3))/60)

I have:
SELECT TableName.PlaceName, TableName.NCoordinates, TableName.WCoordinates
FROM TableName
TEST:
Left([TableName.NCoordinates],(InStr([TableName.NCoordinates],".")-3))+(Right([TableName.NCoordinates],(Len([TableName.NCoordinates])-InStr([TableName.NCoordinates],".")+3))/60);

This returns "Syntax error in FROM clause".

Also, once I get this to work, I don't know how to add the second
calculation for the West coordinates. I know just enough to get myself in
trouble.

What line of work are you in? I used to do land surveying and still write
access apps for that company.

I am a school teacher. This is purely for recreation.

Thank you for the help.

 

[Guest]

You are almost there,

I am making progress. I was working in SQL view instead of Design view. I
got rid of the table name. All seems well and I am able to run the query.
But everything that shows up in the new column comes back #Error.

Thoughts?

 

[Guest]

Did you change the name of the field from Test to maybe Test1?

Also, you could be dealing with a typo. One character will throw the whole
thing in the ditch.

Seth

 

[Guest]

Did you change the name of the field from Test to maybe Test1?

Also, you could be dealing with a typo. One character will throw the whole
thing in the ditch.

Neither. Although, couldn't the field be named 'Test1'?

What I have is:
Test:
Left([NCoordinates],(InStr([NCoordinates],".")-3))+(Right([NCoordinates],(Len([NCoordinates])-InStr([NCoordinates],".")+3))/60)

There is indeed a field called NCoordinates and it is selected in this query.

When the query is run, each value for 'Test' is '#ERROR'.

 

[Guest]

What is the data type of the field (assigned in the underlying table)?

Are you storing the degree and minute symbols? If you are that is a big
problem. What you will need to do instead is create a custom or input mask
and only store the raw digits and decimal.

Let me know,

Seth

Did you change the name of the field from Test to maybe Test1?

Also, you could be dealing with a typo. One character will throw the whole
thing in the ditch.

Neither. Although, couldn't the field be named 'Test1'?

What I have is:
Test:
Left([NCoordinates],(InStr([NCoordinates],".")-3))+(Right([NCoordinates],(Len([NCoordinates])-InStr([NCoordinates],".")+3))/60)

There is indeed a field called NCoordinates and it is selected in this query.

When the query is run, each value for 'Test' is '#ERROR'.

 

[Guest]

What is the data type of the field (assigned in the underlying table)?

Are you storing the degree and minute symbols? If you are that is a big
problem. What you will need to do instead is create a custom or input mask
and only store the raw digits and decimal.

NCoordinates and Wcoordinates are both stored as text in the table.
Changing that is not realistic. If I were going to go back and change the
several thousand waypoints, I would just input them in the format I now
prefer.

Is there any way to handle this through the query?

 

[Guest]

It all depends. What is actually stored? Are you storing the degree
character and a space?

If you are you can change your records in about 30 seconds and it doesn't
matter how many you have.

Open your table where the data is stored and place the cursor in the first
coordinate field. Then press these keys Ctrl + H (Find and Replace). In
Find What type a degree symbol (Alt + 0176) and then the spacebar. Leave the
Replace With field empty. Then choose Replace All. Viola - all the degree
symbols and following spaces are gone, then the formula will work.

If you decide to do this, make a copy of your table first so if you make a
mistake we both don't have to live with the guilt.

Seth

PS - I am going to bed, so I will catch up with you tomorrow if you have
questions.

 

[John Vinson]

I do not know how to perform the necessary math in a query for the following
situation.

I have a table in which I store coordinates for various places. The
coordinates, unfortunately, are stored in DD MM.MMM format. All the
coordinates are for North America. They are stored in two fields such as:
38° 37.501
090° 11.085

I need to convert them to DDD.DDDDD, which would yield:
38.625017
-90.18475

You say below that the degrees are all three digits but in your first
example there are two - is this correct?

Just as an alternative to Seth's suggestions: *IF* this is all stored
in one field (you say two fields but it appears that this is one Text
field for Access), and *IF* you always and consistantly have the
degree sign, this expression should do the job:

Val(Left([NCoordinates],(InStr([NCoordinates],"°")-1))) +
Val(Mid([NCoordinates],(InStr([NCoordinates],"°")+1))) / 60.


John W. Vinson[MVP]

 

[Guest]

You say below that the degrees are all three digits but in your first
example there are two - is this correct?

All North coordinates have two-digit degrees.
Ex. 38° 37.501
Your formula below accurately converted this to 38.6250166667.

The West coordinates have three-digit degrees. Also, a leading negative
sign needs to be placed before the converted West coordinates.
Ex. 090° 11.085
This needs to be converted to -90.18475.

I modified the formula you provided to:

-(Val(Left([WCoordinates],(InStr([WCoordinates],"°")-0)))+Val(Mid([WCoordinates],(InStr([WCoordinates],"°")+1)))/60)

and that seemed to do what I wanted.

I cannot thank you and Seth enough for all your help. When I awoke this
morning I convinced myself to get ready to do a massive cut-and-paste job. I
actually need the text field with the degree sign for one dump of data (this
is what the software for my GPS needs). So, I was going to convert all these
in Excel and get them back in.

Anyhow, as I settled in to begin that tonight, I thought I would re-read
this thread and saw your post. Thank you.

 

[John Vinson]

Anyhow, as I settled in to begin that tonight, I thought I would re-read
this thread and saw your post. Thank you.

You're welcome! Glad to have been of assistance.

John W. Vinson[MVP]