Identify Coordinates and Calculate Driving Time and Duration using BingMaps

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-maps-key

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=Little 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

Identify Coordinates and Calculate Driving Time and Duration using BingMaps - AC.yxmd

Workflow

API

Download

Accepted answers

All comments

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).

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?

<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>

<AuthenticationResultCode>ValidCredentials</AuthenticationResultCode>

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

<Point>

</Point>

</BoundingBox>

<EntityType>RoadBlock</EntityType>

<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>

</Address>

<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.

Updated Driving Direction - Bing.png

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

Updated Workflow is attached.

Identify Coordinates and Calculate Driving Time and Duration using BingMaps - AC.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703