Clay Geocoding Tutorial

Using the OpenCage Geocoding API in Clay

Clay is a popular data enrichment service. In this tutorial we show how you can geocode your data within Clay using our API.

In this example we assume you have a CSV file with coordinate data (latitude, longitude).

Create new table / Import from CSV

Step 1 is to create a new table and upload the data.

Now we have a table with two columns: "Latitude" and "Longitude".

Create a new column

We need to turn the latitude and longitude into a single column that will be the query for the geocoding API

Click '+ Add column', then 'Formula' and in the 'Formula Generator' write your own formula: {{Latitude}} + "," + {{Longitude}}

Similarly if you were forward geocoding (address to coordinates) you could do something like {{street}} + "," + {{postcode}} + "," + {{city}} + "," + {{country}}

Create a new enrichtment column

Now we create the column where the output of the geocoding API call will go. You may want to read the Clay help page about requesting external data.

• Click 'Add enrichment', search for 'HTTP API'
• Set
  • Method: GET
  • Endpoint: https://api.opencagedata.com/geocode/v1/json
  • Query String:
    • key : YOUR-API-KEY
    • q : click on the icon and enter a formula. Type / and select the query column
    • For more options see the API docs for various optional parameters
• Click 'Save' and rename the column, for example 'OpenCage result'

Getting the data you need

When you run the enrichment the column will contain a text '200' That's the HTTP code for 'ok'. If you see 400 then the query was probably empty, missing or contained invalid coordinates. 402 indicates out of quota. For more codes see the relevant section of our API documentation.

Click on one result. A panel named "Cell details" will open displaying the result data structure as a tree of field names. Select 'Results' -> '0' -> 'Components' -> 'Normalized City'. You'll see a button 'Add as column'. Repeat for any output you need.

You should now be able to get the columns of data you need.

Other options

Another possibility is to do the geocoding outside of Clay and then just upload the geocoded data into Clay. We offer a spreadsheet geocoding service which you may find useful.