Google Maps Geocoding Workflow

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

IP BD Geocoding - Own Macro-.yxmd

Macros

API

Spatial Analysis

Accepted answers

fmvizcaino

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

BenMoss

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

All comments

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

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.

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703