Trying to sort a list of addresses in an optimized pick up order using an external API integration

Ask a question
1

What I have is a list of addresses sourced from a Squarespace API import.

What I am trying to do is add the latitude and longitude for each address using an external API such as Geocodio, and then take that data with the addresses and their coordinates and integrate it with a route optimization software called Flightmap.

I have the API Keys and API endpoint URLs, but do not know what to fill into the body text to make this work. I have very little knowledge of coding, but have read the introduction to API in parabola’s guide.

Is it possible to integrate these two into the same flow, such that the response from the first is used as an input for the second API export? And where do I find the code to input in the “body text” section?

2

Hey Jonathan,

Happy to help you out!

I think you are going to want to use an API Enrichment to call the first API to get the coordinates.

Then, you can use those in an API Export to your final API.

The body fields should be defined in the docs for the APIs. Can you post links to their docs here? I can show you from there.

3

Thank you so much for helping me. These are the links to the API docs:

Geocodio: https://www.geocod.io/docs/?shell#batch-geocoding

Flightmap: https://flightmapapi.docs.apiary.io/#introduction/instructions

4

Okay so assuming that you will be using the Geocodio API as the first one. Let’s get that working and then we can do the next one.

First, you want your address in a single column. If it is not, then use a Text Merge step to create the address with commas between the appropriate parts. It does not need to be on multiple lines like you would write it out normally.

Use an API Enrichment and set its URL to this:

https://api.geocod.io/v1.5/geocode?q={Address Column}&api_key=YOUR_API_KEY

{Address Column} should say whatever the name of your column is. If you column is called Address to encode, then you would put {Address to encode} in that URL.

Similarly, replace YOUR_API_KEY with your actual API key.

In the advanced settings for the API Enrichment, click the checkbox to “Encode URLs”.

I pulled that from the https://www.geocod.io/docs/?shell#single-address page since each row will be sent one at a time, we can use the single address endpoint.

5

I entered the link with the correct column title and API key and then checked Ecode URLs, however I received a 403 - Forbidden Error.

This is what it says:
{
“_warnings”: [
“Ignoring parameter “q” as it was not expected. See full list of valid parameters here: https://www.geocod.io/docs/”
],
“error”: “Invalid API key”
}

I am not sure how to go about fixing the parameter issue, and don’t know why the error says the API key is invalid because I copied and pasted it from Geocodio.

Thank you so much for continuing to help me.

6

Can you remove the username and password that you have, and set your auth back to None?

Also change the URL from v1.5 to v1.6

7

It seems like this address is causing issues:

1506 Henry Ave #3018, Mamaroneck, NY 10543, USA

8

Is there a way you can remove any addresses that have things like unit or building numbers? Anything that would be in Address Line 2 of a form? I think that API doesn’t handle those well.

9

Okay I got rid of the Address Line 2 data that was creating issues and got rid of the username authentication, however it is still returning the same error:

{
“_warnings”: [
“Ignoring parameter “q” as it was not expected. See full list of valid parameters here: https://www.geocod.io/docs/”
],
“error”: “Invalid API key”
}

10

Never mind, it actually worked, however it did not return coordinates.

13

I now see that it did work and returned the correct coordinates.

My question now is, how can I organize its response to have a neat column for longitude, and another for latitude instead of a very long and weirdly formatted response?

Thank you again.

14

In your API Enrichment, use the Top Level Key option to add one for api.results and that will give you columns per item in the results.

Some of your results are coming back with multiple guesses by the API. So you will need to use a Dedupe step or Row Filter to make sure you only have 1 row per address

15

Fantastic! This is working well. I would now like to input this data into the flightmap route optimization API. I have an API Key, and I sent the link to the API doc above, but here it is again: https://flightmapapi.docs.apiary.io/#introduction/instructions

What is the next step I should take?

Thank you

16

Seems like you want to have the data from that API returned as well right? As in you want to see what the results are from the API call?

To be clear, you want to set up the Route Optimization V2 feature?

17

Yes, essentially what I want is for it to return its data such that I can make a spreadsheet with all of the same original data, just in a different order (an optimized route).

If it doesn’t work that way then I could have the data just go straight to flightmap. As long as I can access the optimized route from their platform, that is okay as well.

18

Makes sense.

You will have to help me with this one since I am not familiar with what this API does, and the data that it can be sent is pretty complex.

Take a look at this: https://flightmapapi.docs.apiary.io/#reference/0/route-optimization-v2/request

If you click on the box that says request, it should show you a request on the right. Change the programming language dropdown to say RAW (preferable) or cURL (also works).

That will show you something like this sample payload:

{
  "trip_duration_limit": 60,
  "maxVisits": 2,
  "speed": 60,
  "detour": "1",
  "visits": [
    {
      "id": "39037",
      "customer_id": "Abc",
      "load": 2,
      "date": "2019-02-05",
      "pickup": {
        "duration": 5,
        "start_time": 800,
        "end_time": 1000,
        "location": {
          "lat": 30.718739,
          "lng": 76.810573,
          "address": "Sindhi's, Sector 28, Chandigarh, India"
        }
      },
      "dropoff": {
        "duration": 10,
        "start_time": 900,
        "end_time": 1100,
        "location": {
          "lat": 30.752535,
          "lng": 76.8101038,
          "address": "H. no 101, Sector 10, chandigarh, near post office"
        }
      },
      "phone": "9955668855"
    },
    {
      "id": "39038",
      "customer_id": "Xzy",
      "load": 1,
      "date": "2019-02-05",
      "pickup": {
        "duration": 5,
        "start_time": 900,
        "end_time": 1100,
        "location": {
          "lat": 30.7466677,
          "lng": 76.7942403,
          "address": "Raunak, Sector 20, Panchkula, India"
        }
      },
      "dropoff": {
        "duration": 10,
        "start_time": 1100,
        "end_time": 1200,
        "location": {
          "lat": 30.7547789,
          "lng": 76.7873895,
          "address": "Flat 36, Sector 12, Chandigarh, near hospital"
        }
      },
      "phone": "7856985685"
    }
  ],
  "fleets": [
    {
      "id": "78277",
      "capacity": 10,
      "start_location": {
        "lat": 30.7188978,
        "lng": 76.8102981,
        "name": "CDCL, Madhya Marg, 28B, Sector 28B, Chandigarh, India"
      },
      "return_to_Start": false,
      "end_location": {
        "lat": 30.70549299999999,
        "lng": 76.8012561,
        "name": "Elante Mall, Purv Marg, Industrial Area Phase I, Chandigarh, India"
      },
      "shiftEnd": 2345
    },
    {
      "id": "78278",
      "capacity": 10,
      "start_location": {
        "lat": 30.7188978,
        "lng": 76.8102981,
        "name": "CDCL, Madhya Marg, 28B, Sector 28B, Chandigarh, India"
      },
      "return_to_Start": false,
      "end_location": {
        "lat": 30.70549299999999,
        "lng": 76.8012561,
        "name": "Elante Mall, Purv Marg, Industrial Area Phase I, Chandigarh, India"
      },
      "shiftEnd": 2345
    }
  ]
}

That is showing what you could sent them.

Now if you look at the middle panel of the docs, you will see that each field has a description. The only field not marked optional is the Fleets array, so we have to build that our for sure. But do you know which of those fields is also required for your use case?

19

I think we will also have to construct visits. Out of the entire visits field, the only optional things we will have to fill out is phone, and of course all of the required ones.

To clarify exactly what I am trying to do: We have a start and finish location that is always the same, and all addresses in the data in the json application at the end of my parabola editor right now have to be visited in between these start and finish locations. Duration, start time, end time, capacity all do not matter. I just need it to give the fastest route to visit all 63 locations in between the start and finish points.

20

Okay so looking at the Visits Array, it is composed of a list of objects. Each object looks like this:

"visits": [
    {
      "id": "39037",
      "customer_id": "Abc",
      "load": 2,
      "date": "2019-02-05",
      "pickup": {
        "duration": 5,
        "start_time": 800,
        "end_time": 1000,
        "location": {
          "lat": 30.718739,
          "lng": 76.810573,
          "address": "Sindhi's, Sector 28, Chandigarh, India"
        }
      },
      "dropoff": {
        "duration": 10,
        "start_time": 900,
        "end_time": 1100,
        "location": {
          "lat": 30.752535,
          "lng": 76.8101038,
          "address": "H. no 101, Sector 10, chandigarh, near post office"
        }
      },
      "phone": "9955668855"
    }

You are going to want to construct one object per row of data - so that each row you have has a visit logged.

Each visit has a pickup and dropoff. Do you know if you need both? What if we just give it a pickup? Or are you saying that everything will have the same dropoff location?

You will use a Text Merge step to create the object.

I find it easiest for complex things like this to just paste in the full example JSON blob into the Text Merge step, and then start deleting lines that are optional and I am not going to send data for. Just be careful that if you are cutting out a full nested field, that you remove its child-fields, and its closing } to keep your JSON valid.

Then you can start changing the values that are being set to either typed-in values that will always be used, or {column references} to merge in column data like you did in the API Enrichment.

Start there and let me know if you need more help setting those objects up.

1 Like
21

Realized I forgot to edit one part out - remove the "visits": [ part from the merge. It should start with {

22

Okay I gave that a shot. I did the replace text, deleted lines, referenced columns, and inputted fixed values. It merged the text and the column reference worked. What is the next step?