topic Re: Get distance from X / Y coordinates from xls Excel file in ArcGIS API for Python Questions

Get distance from X / Y coordinates from xls Excel file

ChristianBischof — Mon, 22 Jun 2020 12:49:18 GMT

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

Re: Get distance from X / Y coordinates from xls Excel file

RobertBorchert — Mon, 22 Jun 2020 13:05:46 GMT

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

Re: Get distance from X / Y coordinates from xls Excel file

ChristianBischof — Mon, 22 Jun 2020 13:14:54 GMT

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

Re: Get distance from X / Y coordinates from xls Excel file

RobertBorchert — Mon, 22 Jun 2020 13:21:53 GMT

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.

Re: Get distance from X / Y coordinates from xls Excel file

ChristianBischof — Mon, 22 Jun 2020 13:39:28 GMT

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.

Re: Get distance from X / Y coordinates from xls Excel file

RobertBorchert — Mon, 22 Jun 2020 14:18:07 GMT

just use the same tools you would use for ArcMap

Re: Get distance from X / Y coordinates from xls Excel file

MehdiPira1 — Sun, 12 Dec 2021 07:37:46 GMT

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.

import arcpy
import pandas as pd

excelTable = r"D:\temp\XY_Coordinates.xlsx"
csvTable = r"D:\temp\out_csv.csv"
outputFC = r"D:\temp\output.gdb\out_coordinates"
out_Excel = r"D:\temp\XY_Coordinates_output.xlsx"

arcpy.env.overwriteOutput = True
read_file = pd.read_excel (excelTable, sheet_name='Sheet1')
read_file.to_csv (csvTable, index = None, header=True)

# Convert x and y coordinates into line
arcpy.XYToLine_management(
    csvTable, 
    outputFC, 
    'Start_X', 
    'Start_Y', 
    'End_X', 
    'End_Y', 
    'NORMAL_SECTION', 
    '#', 
    "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"
)

# Converting gdb feature class attribute to Excel
arcpy.TableToExcel_conversion(outputFC, out_Excel)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Here's a screenshot of the output:

Re: Get distance from X / Y coordinates from xls Excel file

ChristianBischof — Tue, 07 Jul 2020 08:32:01 GMT

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.