2

I have a table in excel format with the following two columns:

Now, I need to import this table to ArcMap and calculate the distance between the two points for each row and write the calculated distance (kilometer) automatically in a new column "C". Firstly, I use the tool “Excel to table”, but then:

What is the quickest way to convert the two columns in a format to use the “x,y, coordinate tool”? and has ArcMap a tool to calculate the distance between the two points of each row, when I do not integrate the whole street network?

I guess that I need to iterate through the table, but I have no straightforward idea which tools are the best to use.

I am working with ArcMap 10.5.1.

Improve this question
1
  • Split in 2 columns as per answer below. Ad column, call it I'd and populate using row(). Convert 1st pair to points, second pair to points. Merge, project and use points to line, using I'd as point id.
    – FelixIP
    Commented Jan 19, 2018 at 9:59

1 Answer 1

Reset to default
1

Firstly, divide those columns in Lat,Long (Data - Text to Columns), so you will have 4 columns Point A (Latitude, Longitude) and Point B (Latitude, Longitude).

Than convert Decimal Degree to metric coordinate (maybe from WGS84 to UTM) i dont know what systems do you use.

Than if you have four columns representing metric coordinate system, calculate distance directly in excel by using math formula sqrt((x2-x1)^2+(y2-y1)^2).

This would be my solution

Extension for converting into metric coordinates.

1- Format columns as numbers and insert the table into arcmap, create you first points by "right click - Display X,Y Data" chose Point A fields (Y-Latitude,X-Longitude), and chose the geographic coordinate system (regarding you comment perhaps is "GCS_Deutsches_Hauptdreiecksnetz").

2- with first step arcmap will create a temporary feature, right-click and save it somewhere (default geodatabase, no matter).

3- transform feature class into metric system, expand toolbox/data management/projections and transformation and chose project. Chose you feature (drag and drop by arcmap) and chose Projected Coordinate System (you case DHDN_3_Degree_Gauss_Zone_3). New feature will be created (projected)

4- Now open table of contents of new feature and calculate projected coordinates. By adding two double fields (PointA_X,PointA_Y). Right click on PointA_X name and chose Calculate Geometry, in new table chose projected coordinate system and units Meters. Do same for Y coordinate.

5- Export your Table into new Excel Sheet, toolbox/conversions/table to excel. In new excel you will have 6 columns, your 4 old columns and two extended in arcmap.

6- Do same steps for PointsB fields from begin. And in the end you will have a table with 8 columns, 4 with geographic coordinates and 4 with metric coordinates. You can easily apply formula that i have mention above.

FelixIP have mentioned another away above, to create two shapefiles, merge and to convert to line, is almost the same just in this case you calculate in excel no need to create lines. Whatever you chose, i suggest to calculate distance from projected coordinates.

Improve this answer
2
  • Thanks Florjan for answering. I divide those two columns in four. However, I am struggling with converting the result to the following coordinate system DHDN_3_Degree_Gauss_Zone_3 (31467) in excel in order to use your advised formula sqrt((x2-x1)^2+(y2-y1)^2). Do you have a tip?? Thanks
    – nora
    Commented Jan 19, 2018 at 14:40
  • Hi Nora, above i have edited my question, i little long but i shared steps one by one.
    – riflo
    Commented Jan 19, 2018 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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