nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Get distance from X / Y coordinates from xls Excel file

ChristianBischof

I have a xls Excel file with a startpoint and endpoint column each containing x and y coordinates and I need to get the distance between each start- and endpoint. The result (the calculated distance) should be added as new column into the file.

Can anybody help me out here with python code snippet or a workflow to achieve this?

Thanks in advance

Find more posts tagged with

Accepted answers

All comments

ChristianBischof

Thanks Mehdi Pira‌ !

This is definitely a way in the right direction. Although I forgot to mention, that I need to calculate the distances based on a street network (SMP) and the results from your approach are direct lines between the points, right?

Thank you anyway for the "Excel Hint" this was also very helpful!

If anybody could suggest if I should rather try to implement a solution with ODCM Matrix or CF, maybe just a Route? The only thing we need is the distance in km.

MehdiPira1

Hi Christian Bischof,

Here's the python code which takes an excel file with a startpoint and endpoint column each containing x and y coordinates as input and outputs an excel file with shape_length (distance) calculated in a new column.

Just change the variables' values, sheet name and the spatial_reference accordingly it should work.

<SPAN class="keyword token">import</SPAN> arcpy
<SPAN class="keyword token">import</SPAN> pandas <SPAN class="keyword token">as</SPAN> pd

excelTable <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"D:\temp\XY_Coordinates.xlsx"</SPAN>
csvTable <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"D:\temp\out_csv.csv"</SPAN>
outputFC <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"D:\temp\output.gdb\out_coordinates"</SPAN>
out_Excel <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"D:\temp\XY_Coordinates_output.xlsx"</SPAN>

arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>overwriteOutput <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">True</SPAN>
read_file <SPAN class="operator token">=</SPAN> pd<SPAN class="punctuation token">.</SPAN>read_excel <SPAN class="punctuation token">(</SPAN>excelTable<SPAN class="punctuation token">,</SPAN> sheet_name<SPAN class="operator token">=</SPAN><SPAN class="string token">'Sheet1'</SPAN><SPAN class="punctuation token">)</SPAN>
read_file<SPAN class="punctuation token">.</SPAN>to_csv <SPAN class="punctuation token">(</SPAN>csvTable<SPAN class="punctuation token">,</SPAN> index <SPAN class="operator token">=</SPAN> None<SPAN class="punctuation token">,</SPAN> header<SPAN class="operator token">=</SPAN><SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># Convert x and y coordinates into line</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>XYToLine_management<SPAN class="punctuation token">(</SPAN>
    csvTable<SPAN class="punctuation token">,</SPAN> 
    outputFC<SPAN class="punctuation token">,</SPAN> 
    <SPAN class="string token">'Start_X'</SPAN><SPAN class="punctuation token">,</SPAN> 
    <SPAN class="string token">'Start_Y'</SPAN><SPAN class="punctuation token">,</SPAN> 
    <SPAN class="string token">'End_X'</SPAN><SPAN class="punctuation token">,</SPAN> 
    <SPAN class="string token">'End_Y'</SPAN><SPAN class="punctuation token">,</SPAN> 
    <SPAN class="string token">'NORMAL_SECTION'</SPAN><SPAN class="punctuation token">,</SPAN> 
    '<SPAN class="comment token">#', </SPAN>
    <SPAN class="string token">"PROJCS['GDA_1994_MGA_Zone_50',GEOGCS['GCS_GDA_1994',DATUM['D_GDA_1994',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Transverse_Mercator'],PARAMETER['False_Easting',500000.0],PARAMETER['False_Northing',10000000.0],PARAMETER['Central_Meridian',117.0],PARAMETER['Scale_Factor',0.9996],PARAMETER['Latitude_Of_Origin',0.0],UNIT['Meter',1.0]];-5120900 1900 10000;-100000 10000;-100000 10000;0.001;0.001;0.001;IsHighPrecision"</SPAN>
<SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># Converting gdb feature class attribute to Excel</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>TableToExcel_conversion<SPAN class="punctuation token">(</SPAN>outputFC<SPAN class="punctuation token">,</SPAN> out_Excel<SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Here's a screenshot of the output:

ChristianBischof

My approach would look something like this:

I'd apply the tools in ArcGIS Pro and get the basic Python code from it, but I'm new to ArcGIS Pro and don't know which tools to use.

RobertBorchert

Yep whole new level of complex.

We don't use routing services ourselves.

But what you could do is build the points in the gis and add a hyperlink using Google, Apple, or Bing maps to create the driving directions when you select the hyperlink. It would at least give you the best route based on traffic.

I used just that when creating driving directions into Collector (before Collector had directions built in)

The field techs would click the hyperlink and it would pop up Google or Apple maps to give directions to the site they were going to.

ChristianBischof

Sorry I forgot an important information. The distance should be calculated via a Routing service (Street Map Premium or ArcGIS Online).

RobertBorchert

Basic 10th grade geometry.

a² + b² = c²

Where c is the line between a and b

Or simply create lines from the coordinates and populate your spreadsheet with the shape length resulting from the lines

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

All Communities
Recent Posts
Followed Content
Community Help

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.