Identify Coordinates and Calculate Driving Time and Duration using BingMaps

[alibink]

Problem Statement:

You have addresses and you need to identify GPS co-ordinates and calculate the driving time and duration between two points. Using the Alteryx distance tool, you get the straight line distance instead of the drive route. You can use Google Maps API to calculate the distance but it has a limitation of certain number of requests per day on its free account that’s when Bing Map API comes in.

Pre-requisites:

• Data file with addresses (From Address and To Address)
• Alteryx Desktop

Bing API Key which can be obtained from https://www.bingmapsportal.com/  

Information is available on https://docs.microsoft.com/en-us/bingmaps/getting-started/bing-maps-dev-center-help/getting-a-bing-m...

Solution:

If you have addresses and you want to get the distance and driving time between these address.

DepartFrom	ArriveAt
You Yangs Regional Park, Branch Rd, Little River VIC 3211, Australia	Kinglake National Park, Kinglake West VIC 3757, Australia

Step 1: Prepare the data

Prepare your data in the following format for ease

Step 2: Identify the Lat/Long of the addresses

Create a bing API request in the following format.

"http://dev.virtualearth.net/REST/v1/Locations?countryRegion="+[countryRegion]+"&adminDistrict="+[State]+"&locality="+[Locality]+"&postalCode="+[Postcode]+"&addressLine="+[addressLine]+"&maxResults=1&o=xml&key="+[bingmaps.key]

Where;

[countryRegion]

[State]

[Locality]

[Postcode]

[addressLine]

[bingmaps.key]

are the variables

Field Name	Field Value
RecordID	1
DepartFrom	You Yangs Regional Park, Branch Rd, Little River VIC 3211, Australia
addressLine	You Yangs Regional Park
Locality	Little River
countryRegion	Australia
Postcode	3211
State	VIC
bingmaps.key	<your bing API key>
pointURL	http://dev.virtualearth.net/REST/v1/Locations?countryRegion=Australia&adminDistrict=VIC&locality=Lit... River&postalCode=3211&addressLine=You Yangs Regional Park&maxResults=1&o=xml&key=<your bing API key>

Download the alteryx workflow to understand how I created an API request. Repeat the same steps for Destination.

I used formula tool to create these fields and download tool to send the API request.

Step 3: Calculate the driving time and driving duration between the two GPS locations

Once you have the Origin/Destination Lats/Longs, send the API request to get the distance and duration using the following URL request.

"https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+[Origin]+"&destinations="+[Destination]+"&travelMode="+[travelMode]+"&o=xml&key="+[bingmaps.key]

Where;

[Origin]

[Destination]

[travelMode]

[bingmaps.key]

are variables.

Next use the XML parse tool to read Distance and Duration

[DiganP]

@alibink good write up but you can get the drivetime (and the straight line) in the distance tool, This drive time is based on the Tom Tom dataset.

[alibink]

Hi @DiganP,

Thats right. Distance Tool gives you straight line distance which is not the driving distance. And for TomTom, you need Alteryx Geospatial License. 

[AMackay]

Is this process possible to use for batches of address data? ie thousands of addresses, or is there a limit allowed with the Bing API key?

Also, when creating the Bing Key, what application type should be set? There is Mobile, Wesbite, DevTest or Windows Application

[alibink]

Hi @AMackay:

I have tried with 50k+ addresses and it worked for me. Use Dev/Test (Key type: Basic / Dev/Test). 

[Rraj29]

Hi @alibink

I am trying to use your tool but I am able to download only Latitude value and no long. value as shown below. 

I observed that, usage type is missing. could you please help in fixing this?

[Aimanj2]

hi Alibink

did you have a solution for the issue that was faced by Raj from below?basically the download tool brings the whole html, but when its parsed in the html tool only the latitude is parsed, also when I look inside of the xml being outputted the usage type is actually "Display" in all the cases the usage type is not parsed in the xml tool either.

any one knows why?

<Response xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/search/local/ws/rest/v1">

<Copyright>Copyright © 2023 Microsoft and its suppliers. All rights reserved. This API cannot be accessed and the content and any results may not be used, reproduced or transmitted in any manner without express written permission from Microsoft Corporation.</Copyright>

<BrandLogoUri>http://dev.virtualearth.net/Branding/logo_powered_by.png</BrandLogoUri>

<StatusCode>200</StatusCode>

<StatusDescription>OK</StatusDescription>

<AuthenticationResultCode>ValidCredentials</AuthenticationResultCode>

<TraceId>a952b6c6338443cca4d47e11de0dfb9c|PUS000DE1D|0.0.0.1|Ref A: C81FB3FA2B054F1294CCF539C9526423 Ref B: SEL20EDGE0213 Ref C: 2023-06-02T09:56:55Z</TraceId>

<ResourceSets>

<ResourceSet>

<EstimatedTotal>1</EstimatedTotal>

<Resources>

<Location>

<Name>Little River Rd, Little River, Victoria 3211, Australia</Name>

<Point>

<Latitude>-37.96648327</Latitude>

<Longitude>144.52424979</Longitude>

</Point>

<BoundingBox>

<SouthLatitude>-37.970345983245174</SouthLatitude>

<WestLongitude>144.51771730572096</WestLongitude>

<NorthLatitude>-37.962620548103821</NorthLatitude>

<EastLongitude>144.53078227310704</EastLongitude>

</BoundingBox>

<EntityType>RoadBlock</EntityType>

<Address>

<AddressLine>Little River Rd</AddressLine>

<AdminDistrict>Victoria</AdminDistrict>

<AdminDistrict2>Wyndham</AdminDistrict2>

<CountryRegion>Australia</CountryRegion>

<FormattedAddress>Little River Rd, Little River, Victoria 3211, Australia</FormattedAddress>

<Locality>Little River</Locality>

<PostalCode>3211</PostalCode>

</Address>

<Confidence>High</Confidence>

<MatchCode>Good</MatchCode>

<GeocodePoint>

<Latitude>-37.96648327</Latitude>

<Longitude>144.52424979</Longitude>

<CalculationMethod>Interpolation</CalculationMethod>

<UsageType>Display</UsageType>

</GeocodePoint>

</Location>

</Resources>

</ResourceSet>

</ResourceSets>

</Response>

[alibink]

@Aimanj2 & @Rraj29 

Sorry for the delayed response. I have had a look. Seems like there has been a change in the and the response received. I have updated the workflow and it works fine now. 

If you have any questions, please feel free to reach out.

Updated Workflow is attached.