Add latitude and longitude to each record using webservice

1

I have a CSV of addresses

Name,addess,city,state,zip

I want to use a webservice to get coordinates
Name,addess,city,state,zip,latitude,longitude,accuracy

My web service uses this format with json output

https://mywebservice/geocode?q=ADDRESS+CITY+STATE+ZIP&apikey=myapikey

https://api.geocod.io/v1.6/geocode?q=20%20W%2034th%20St,%20New%20York,%20NY%2010001&api_key=myapikey

here’s a sample output
{“input”:{“address_components”:{“number”:“20”,“predirectional”:“W”,“street”:“34th”,“suffix”:“St”,“formatted_street”:“W 34th St”,“city”:“New York”,“state”:“NY”,“zip”:“10001”,“country”:“US”},“formatted_address”:“20 W 34th St, New York, NY 10001”},“results”:[{“address_components”:{“number”:“20”,“predirectional”:“W”,“street”:“34th”,“suffix”:“St”,“formatted_street”:“W 34th St”,“city”:“New York”,“county”:“New York County”,“state”:“NY”,“zip”:“10001”,“country”:“US”},“formatted_address”:“20 W 34th St, New York, NY 10001”,“location”:{“lat”:40.748557,“lng”:-73.985089},“accuracy”:1,“accuracy_type”:“range_interpolation”,“source”:“TIGER/Line\u00ae dataset from the US Census Bureau”}

Any help on the steps required to get this working to add coordinates to all records in a csv file?

I use https://www.geocod.io/
They offer free API key and 2500 free lookups a day

Thanks in advance

2

Hi @Golfer99 and welcome to the Community!

At what point are you stuck with EasyMorph? Sending a web request? Parsing the response? Iterating across the CSV?

Typically, you would have to create a “Web Location” connector, and then use the “Iterate web request” action to send a web request to geocod.io for every row in your CSV file.

Here is a topic with a similar case, but using SmartyStreetAPI: Verifying address data with SmartyStreets API

Here is a topic on using Iterate / Web Request: Example: use of Web Request and Iterate Web Request with the Community forum API

3

thank for the reply.

Caution I’m very new with ETL

Getting stuck at using URL Parameter for q=(Fulladdress)
Error: Calculated parameter {q} must not refer to a field. Parameters are calculated outside of tables.

image
image.png894×649 24.4 KB

Any help appreciated

4

That's not a problem at all!

The "Iterate Web Request" action can use column values. Instead of using calculated parameter q, just calculate a new column [q] using the same expression:

[Address] & ' ' & [City] ...etc.

Then, in the "Iterate Web Request" action use the new column. You can switch to the column here:

image

5

Thanks for your help,

I’m now getting the json response into columns.

Stuck at:
How to get only the first instance of the Json property (Notice 2 lat/lng per query)
How to append the 2 new columns (lat and lng) back into the original CSV

image
image.png601×610 26.8 KB

Thanks in advance

6

There are 2 options:

Option #1: include a column that is guaranteed to be unique, e.g. formatted_address. Then deduplicate based on that column using the "Deduplicate" action.

Option #1: if it's known that each 2nd row is a duplicate, then you can enumerate all rows using the "Enumerate" action, and then remove every even row using the "Filter on condition" action with the following condition:

iseven([Row number])

Once you have duplicates removed, you should have exactly the same number of rows as in your input file and in exactly the same order. You can then append the new columns to the original file using the "Append table" action in the "Append columns" mode.