Google Maps Geocoding Workflow

[bobbywatkins]

Hi all

Please excuse any stupidity here, I've only recently started working with Alteryx and I'm still very much in learning phase. 

Basically I have tried to design a workflow that utilises the Google Maps API (free version, 2,500 requests per day) in order to Geocode the lat/long of some companies. Through a bit of trial and error I've managed to botch together the attached macro which appears to output what I'm after, however after 3 or 4 runs of the workflow (using a random sample of 1) the Download/JSON Parse produces the following value string:

You have exceeded your daily request quota for this API. We recommend registering for a key at the Google Developers Console: https://console.developers.google.com/apis/credentials?project=_

OVER_QUERY_LIMIT

Can anyone explain to me what's happening here? I'd have thought the 2,500 API requests would stretch much further than geocoding 3 or 4 companies. Any workarounds/ideas also welcome. 

Thanks in advance 

[BenMoss]

A request is not defined by a single run of alteryx, it's defined by how many items you are trying to geocode.

So if you have 1000 items to geocode in your first run, 1000 in your second and so on then you are going to hit your qouta fairly quickly.

Basically how many rows you are passing into the download tool is the number of requests you are making each time.

Ben

[bobbywatkins]

Thanks Ben.

The random sample tool is restricting my rows to just one, so I'm a bit confused how I'm reaching the limit? If you could tell me in dummy terms that would be much appreciated. Is there a workaround?

[fmvizcaino · Accepted solution]

Hi bobbywatkins,

From what I've seen in your workflow, you forgot to add the key part in your query (the bold part).

https://maps.googleapis.com/maps/api/geocode/json?address=YOURADRESS&key=YOUR_API_KEY

So your formula should be [API URL]+[Location]+'&key='+[Key]

The way you created, you are using the geocode without a key and that is why your are getting that message.

Best,

Fernando Vizcaino

[bobbywatkins]

Thanks for looking Fernando, the key is there in the Text Input tool, perhaps you need to run the workflow to get the Query to fill out properly. I have completed several successful runs it's more a capacity issue I am facing. 

[fmvizcaino]

Hi bobbywatkins,

I know that your workflow is working, but the way you constructed your URL, you are not using your key, you are using an old way of geocoding without key.

To use your key, you need to insert in your URL the string &key=

Best,

Fernando V.

[BenMoss]

It's also worth noting there are quite a few pre-built google geocoding tools available on the Alteryx Gallery, which built out the query URL for your credentials automatically.

But I would follow @fmvizcaino's advice and change the structure of the request and see if this resolves the issue.

Ben

[bobbywatkins]

@fmizcaino seem's to have hit the nail on the head from my trial run - thank you. 

My other challenge is (I'm not sure if you can see the data) that some of names from the two sources are similar but not exact, for example 3D ICOM GMBH & CO KG vs. 3D ICOM GMBH & CO. KG

Is there a way I can achieve a join on the datasets for matches that are close enough? 

Thanks

[BenMoss · Accepted solution]

Try the fuzzy match tool to try and link the cases (blog here: https://www.theinformationlab.co.uk/2014/04/02/alteryx-tools-focus-fuzzy-match-make-group-unique/).

Alternatively you could clean those characters out of the string using a formula then attempt a join.

Ben