Help

Re: Convert address (list) to geocode (latitude and longitude) through map block and google API

509 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Michael_La
4 - Data Explorer
4 - Data Explorer

I have a list of addresses and I already represented them through Map block using google Map API on the map.
The queseion is how to get the coresponding geocode (latitude and longitude) for each address.

eventhough I noteced that Map Block created text code for each address called Geocode cache is that may help or converted to coordinates.

“Geocode cache
This block stores additional information for each address. Create a new text field in your table and pick it below. Other map blocks can use the same field.”

Thank you so much for your help in advance.

2 Replies 2
Steven_Lehrburg
5 - Automation Enthusiast
5 - Automation Enthusiast

I realize this is a few years old, but in case anyone is searching for this in the future: it looks like those fields are base-64-encoded JSON. I think you'll need to do the decoding in an Automation, but that should get you the latitude and longitude.

based on work done by @apg314 and @Yourapp  from this post,  https://community.airtable.com/t5/development-apis/base64-conversion/td-p/103578
I was able to modify the code for Base64 decoding rather than encoding. 
Just copy past into a script block and execute to give you a long string which contains lat & long
It's a big JSON bundle so you need extra formula fields to strip out lat & long

eg Lat:   

MID( JSONOutput, FIND("Australia",JSONOutput)+17, 11)     
not 100% as decimals vary, but 2000 records had about 10 errors needing manual adjustment
-----

function btoa(token) {
var Base64={_keyStr:"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=",decodeURI:function(e){var t="";var n,r,i,s,o,u,a;var f=0;e=Base64._utf8_encode(e);while(f<e.length){n=e.charCodeAt(f++);r=e.charCodeAt(f++);i=e.charCodeAt(f++);s=n>>2;o=(n&3)<<4|r>>4;u=(r&15)<<2|i>>6;a=i&63;if(isNaN(r)){u=a=64}else if(isNaN(i)){a=64}t=t+this._keyStr.charAt(s)+this._keyStr.charAt(o)+this._keyStr.charAt(u)+this._keyStr.charAt(a)}return t},decode:function(e){var t="";var n,r,i;var s,o,u,a;var f=0;e=e.replace(/[^A-Za-z0-9\+\/\=]/g,"");while(f<e.length){s=this._keyStr.indexOf(e.charAt(f++));o=this._keyStr.indexOf(e.charAt(f++));u=this._keyStr.indexOf(e.charAt(f++));a=this._keyStr.indexOf(e.charAt(f++));n=s<<2|o>>4;r=(o&15)<<4|u>>2;i=(u&3)<<6|a;t=t+String.fromCharCode(n);if(u!=64){t=t+String.fromCharCode(r)}if(a!=64){t=t+String.fromCharCode(i)}}t=Base64._utf8_decode(t);return t},_utf8_encode:function(e){e=e.replace(/\r\n/g,"\n");var t="";for(var n=0;n<e.length;n++){var r=e.charCodeAt(n);if(r<128){t+=String.fromCharCode(r)}else if(r>127&&r<2048){t+=String.fromCharCode(r>>6|192);t+=String.fromCharCode(r&63|128)}else{t+=String.fromCharCode(r>>12|224);t+=String.fromCharCode(r>>6&63|128);t+=String.fromCharCode(r&63|128)}}return t},_utf8_decode:function(e){var t="";var n=0;var r=c1=c2=0;while(n<e.length){r=e.charCodeAt(n);if(r<128){t+=String.fromCharCode(r);n++}else if(r>191&&r<224){c2=e.charCodeAt(n+1);t+=String.fromCharCode((r&31)<<6|c2&63);n+=2}else{c2=e.charCodeAt(n+1);c3=e.charCodeAt(n+2);t+=String.fromCharCode((r&15)<<12|(c2&63)<<6|c3&63);n+=3}}return t}}
return Base64.decode(token)
}


// Change this name to use a different table
let table = base.getTable("Company");

let tableQuery = await table.selectRecordsAsync();

for(let tableRec of tableQuery.records) {
b64 = btoa(tableRec.getCellValue("MapCache"));
 
// Insert records in "field2paste" table1
await table.updateRecordAsync(tableRec, {
"JSONOutput" : b64,
});
}