Estimate Driving Time and Distance for Logistics with OpenRouteService API

This n8n workflow helps logistics operations by calculating driving time and distance between multiple locations using the OpenRouteService API. It processes a list of addresses from a Google Sheet, enriches them with geographical coordinates, and then calculates the route details.

What it does

1. Triggers Manually: The workflow starts when manually executed.
2. Reads Data from Google Sheets: It fetches a list of addresses from a specified Google Sheet.
3. Prepares Data for Geocoding: It transforms the address data into a format suitable for the OpenRouteService Geocoding API.
4. Geocodes Addresses: For each address, it calls the OpenRouteService Geocoding API to convert the address into latitude and longitude coordinates.
5. Adds Geocoded Data: It combines the original address data with the newly obtained geographical coordinates.
6. Loops Over Items: It processes the geocoded addresses in batches to handle API rate limits or large datasets efficiently.
7. Waits (Optional Delay): A Wait node is included, which can be configured to introduce a delay between API calls to prevent hitting rate limits.
8. Calculates Route: For each pair of locations (origin and destination), it calls the OpenRouteService Directions API to calculate the driving time and distance.
9. Processes Route Results: It extracts relevant driving time and distance information from the API response.
10. Updates Google Sheet (Implicit): Although not explicitly connected in the provided JSON, a typical next step would be to write the calculated driving times and distances back to the Google Sheet or another data store.

Prerequisites/Requirements

• n8n Instance: A running n8n instance.
• Google Sheets Account: Access to a Google Sheet containing the addresses.
• OpenRouteService API Key: An API key for the OpenRouteService platform (available at https://openrouteservice.org/). This will be used for both geocoding and directions.

Setup/Usage

1. Import the Workflow: Import the provided JSON into your n8n instance.
2. Configure Google Sheets Node:
   • Set up your Google Sheets credential.
   • Specify the Spreadsheet ID and Sheet Name where your addresses are located.
3. Configure HTTP Request Nodes (OpenRouteService):
   • Geocoding HTTP Request:
     • Set the URL for the OpenRouteService Geocoding API (e.g., https://api.openrouteservice.org/geocode/search).
     • Add your OpenRouteService API key as a header (e.g., Authorization: Bearer YOUR_ORS_API_KEY).
     • Configure the body to send the address for geocoding.
   • Directions HTTP Request:
     • Set the URL for the OpenRouteService Directions API (e.g., https://api.openrouteservice.org/v2/directions/driving-car).
     • Add your OpenRouteService API key as a header.
     • Configure the body to send the origin and destination coordinates for route calculation.
4. Adjust 'Edit Fields' Node: Ensure the 'Edit Fields' node correctly maps your incoming address fields to the expected input for the OpenRouteService API calls.
5. Adjust 'Loop Over Items' Node: If you have a very large number of addresses, you might want to adjust the batch size to manage API limits.
6. Configure 'Wait' Node: If you encounter API rate limit issues, increase the delay in the Wait node.
7. Add Output Node (Optional but Recommended): Connect the final output of the route calculation to a Google Sheets node (or another database/service) to write back the calculated driving times and distances.
8. Execute the Workflow: Click the "Execute workflow" button to run the process.