1

I am getting data from a SQL Server, with values for longitude and latitude stored as "raw" values i.e. there are no negative signs in front of longitudes, and no decimals in either longitudes or latitudes. For example, a point in Colorado is given long/lat values in the database as:

Longitude: 104905936 Latitude: 3980741

These should be: -104.905936 and 39.80741

Is there a way to change these values within ArcMap after I bring the table in as a database file? If this must be done in Excel, can anyone recommend an efficient way of adding the negative sign and the decimal point in the correct spots?

  • 1
    do you have any hint to decide that it is not 10.4905936 ? For instance, is there a value indicaring that it should be in Colorado ? – radouxju Jul 19 '17 at 19:09
  • Are ALL coordinates exactly 9 and 7 digits long? – jbchurchill Jul 19 '17 at 19:31
  • @radouxju It's all data related to a specific county in Colorado – Steven C Jul 19 '17 at 19:39
  • @jbchurchill Yes, all are exactly 9 and 8 digits long (I accidentally truncated the last digit on the Latitude) – Steven C Jul 19 '17 at 19:42
3

the easiest way in ArcMap if all your digits are formatted (9 and 8 digits, in other words 6 decimal places) is to use the field calculator(with Python syntax below) :

!longitude!/(-1000000)

!latitude!/(1000000)

just make sure that they are stored in new fields of "float" type, otherwise they will be rounded to the closest integer.

  • Looks easier than my solution and if it worked for you, you should accept this as the answer by checking the box! – jbchurchill Aug 9 '17 at 12:45
0

If you are absolutely sure that is the way those numbers are formatted, you could do this pretty easily using Excel.

To create the table I'm showing here in Excel I started with two columns of numbers similar to yours. In the 3rd and 4th columns I used the following formulas ...

=LEFT(A1, 3)
=RIGHT(A1, 6)

Then for Columns E & F The formulas are similar

=LEFT(B1, 2)
=RIGHT(B1, 5)

EDIT: Make the 5 a six to address your comment above that the number is 8 digits long.

And I left the formula that I used to concatenate them together showing. For Column G it is ...

="-"&C1&"."&D1

and for column H it is

=E1&"."&F1

Then you are going to want to copy and paste the values over the formulas (or paste them elsewhere as values) so that you can format them as a number.

Table of Numbers

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.