Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here
Dec 06, 2020 04:39 PM
Hi All,
Just wanted to share a formula I created to convert latitude and longitude into Google Maps Plus Codes. If you are not familiar with Plus Codes, check out this documentation.
The benefits to the plus code are:
Every location on earth can have a mailing address
The encoding of 10 characters plus “+” character is much more efficient in terms of number of bytes than latitude and longitude, which to represent the same level of specificity would need to consist of two floating point decimal numbers of 10 or even more decimal places specificity, requiring more than double the number of bytes, and a tuple type representation instead of a simple string primitive.
Plus codes can be used in filenames easily because they don’t use reserved characters.
Plus codes are compressible—each additional character adds another level of specificity, similar to the decimal place system, so if you have millions of plus codes you are storing, and you remove two characters from the end, you save on 25% of that data, at the cost of a just little bit of specificity.
No confusion over direction (NSEW) or positive/negative.
Given a regional or local landmark, plus codes can be compressed even more—for example, given a cell tower location, you probably only need 2-4 characters for a location, which can be located within the known larger plus code region of a cell tower—or you can use a landmark, a city, or county, etc.
There are many important uses, for example, addressing homes that don’t have streets or consistently named streets, especially in 3rd world countries or low-population density areas.
Even now you can search Google Maps using plus codes, just as you can with lat & lon. Anyway here are the formulas, which in the first, take two separate Latitude and Longitude fields, and in the second, a combined Location (ie latitude and longitude separated by a comma and/or space):
#1 (two separate latitude & longitude fields)
IF(AND(Latitude,Longitude),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE("["&FLOOR(MOD(FLOOR((Latitude+90)*8000)/POWER(20,4),20))&"]","["&FLOOR(MOD(FLOOR((Longitude+180)*8000)/POWER(20,4),20))&"]","["&FLOOR(MOD(FLOOR((Latitude+90)*8000)/POWER(20,3),20))&"]","["&FLOOR(MOD(FLOOR((Longitude+180)*8000)/POWER(20,3),20))&"]","["&FLOOR(MOD(FLOOR((Latitude+90)*8000)/POWER(20,2),20))&"]","["&FLOOR(MOD(FLOOR((Longitude+180)*8000)/POWER(20,2),20))&"]","["&FLOOR(MOD(FLOOR((Latitude+90)*8000)/POWER(20,1),20))&"]","["&FLOOR(MOD(FLOOR((Longitude+180)*8000)/POWER(20,1),20))&"]","+","["&FLOOR(MOD(FLOOR((Latitude+90)*8000)/POWER(20,0),20))&"]","["&FLOOR(MOD(FLOOR((Longitude+180)*8000)/POWER(20,0),20))&"]"),"[0]","2"),"[1]","3"),"[2]","4"),"[3]","5"),"[4]","6"),"[5]","7"),"[6]","8"),"[7]","9"),"[8]","C"),"[9]","F"),"[10]","G"),"[11]","H"),"[12]","J"),"[13]","M"),"[14]","P"),"[15]","Q"),"[16]","R"),"[17]","V"),"[18]","W"),"[19]","X"))
#2 (one combined latitude & longitude field)
IF(Location,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE("["&FLOOR(MOD(FLOOR((LEFT(SUBSTITUTE({Location}," ",""),FIND(',',SUBSTITUTE({Location}," ",""))-1)+90)*8000)/POWER(20,4),20))&"]","["&FLOOR(MOD(FLOOR((MID(SUBSTITUTE({Location}," ",""),FIND(',',SUBSTITUTE({Location}," ",""))+1,LEN(SUBSTITUTE({Location}," ","")))+180)*8000)/POWER(20,4),20))&"]","["&FLOOR(MOD(FLOOR((LEFT(SUBSTITUTE({Location}," ",""),FIND(',',SUBSTITUTE({Location}," ",""))-1)+90)*8000)/POWER(20,3),20))&"]","["&FLOOR(MOD(FLOOR((MID(SUBSTITUTE({Location}," ",""),FIND(',',SUBSTITUTE({Location}," ",""))+1,LEN(SUBSTITUTE({Location}," ","")))+180)*8000)/POWER(20,3),20))&"]","["&FLOOR(MOD(FLOOR((LEFT(SUBSTITUTE({Location}," ",""),FIND(',',SUBSTITUTE({Location}," ",""))-1)+90)*8000)/POWER(20,2),20))&"]","["&FLOOR(MOD(FLOOR((MID(SUBSTITUTE({Location}," ",""),FIND(',',SUBSTITUTE({Location}," ",""))+1,LEN(SUBSTITUTE({Location}," ","")))+180)*8000)/POWER(20,2),20))&"]","["&FLOOR(MOD(FLOOR((LEFT(SUBSTITUTE({Location}," ",""),FIND(',',SUBSTITUTE({Location}," ",""))-1)+90)*8000)/POWER(20,1),20))&"]","["&FLOOR(MOD(FLOOR((MID(SUBSTITUTE({Location}," ",""),FIND(',',SUBSTITUTE({Location}," ",""))+1,LEN(SUBSTITUTE({Location}," ","")))+180)*8000)/POWER(20,1),20))&"]","+","["&FLOOR(MOD(FLOOR((LEFT(SUBSTITUTE({Location}," ",""),FIND(',',SUBSTITUTE({Location}," ",""))-1)+90)*8000)/POWER(20,0),20))&"]","["&FLOOR(MOD(FLOOR((MID(SUBSTITUTE({Location}," ",""),FIND(',',SUBSTITUTE({Location}," ",""))+1,LEN(SUBSTITUTE({Location}," ","")))+180)*8000)/POWER(20,0),20))&"]"),"[0]","2"),"[1]","3"),"[2]","4"),"[3]","5"),"[4]","6"),"[5]","7"),"[6]","8"),"[7]","9"),"[8]","C"),"[9]","F"),"[10]","G"),"[11]","H"),"[12]","J"),"[13]","M"),"[14]","P"),"[15]","Q"),"[16]","R"),"[17]","V"),"[18]","W"),"[19]","X"))
Example input/output:
Input: 37.78239612266476, -122.40520883383574
(Airtable HQ)
Output: 849VQHJV+XW
(try searching this code on Google Maps)
That’s all, cheers!
Dec 26, 2020 06:08 PM
This is a really good observation and post. If you care about location data at scale, open location codes are ideal and encoding and decoding them are flawless and pervasively supported.
Question -
8FXCCP66+R5G
What in the heck were they thinking? More useful - how does one decode this sh_tshow into either a lat/lng or an open location code?
Perhaps @Moe knows?
Dec 27, 2020 12:44 AM
Yikes, yeah I know… but isn’t the map app generating some kind of token through Google’s Maps API?
BTW, the implementation is trivial—in lieu of adding a node dependency, just use the following utility function (for encoding… i’m sure decoding would be equally trivial—EDIT: Now with decoding, too—it was slightly more complicated but quite trivial)
Try Open Location/Plus Code Encode/Decode on RunKit ›
// EDIT: Improved the encodePlusCode() method.
/** @constant {string} numerals - string listing official base-20 open location/plus code numerals */
const _numerals = '23456789CFGHJMPQRVWX';
/**
* Encodes an open location/plus code, given map coordinates
*
* @param {(string[]|number[])} coords - latitude and longitude
* @param {number} [resolution = 5] - number of pairs of digits in the return code, greater is more specific
* @returns {string} an open location/plus code of the specified {@link resolution}
*
* @example
* // encodePlusCode([ 37.944027, -93.635504 ]);
* // returns '8698W9V7+JQ'
*
* @example <caption>Takes either an array of numbers or an array of strings as an argument, so you can do:</caption>
* // encodePlusCode('37.944027, -93.635504'.split(', '));
* // returns '8698W9V7+JQ'
*
* @example <caption>encodePlusCode() is the inverse of decodePlusCode(). A small amount of uncertainty is part of the spec.</caption>
* // encodePlusCode(decodePlusCode('8698W9V7+JQ'));
* // returns '8698W9V7+JQ'
*
* @todo validation...
*/
const encodePlusCode = (coords, resolution = 5) => {
const [ latitude, longitude ] = [Math.floor((+coords[0] + 90) * 8000), Math.floor((+coords[1] + 180) * 8000)]
.map(meridian => [...Array(resolution)]
.map((_, i) => Math.floor((meridian / Math.pow(20, i)) % 20).toString()).reverse());
return latitude
.flatMap((_, d) => [latitude, longitude]
.map(meridian => meridian[d]))
.map((d, i) => (i === 8 ? '+' : '') + _numerals.charAt(d))
.join('');
// Alternative to above line for earlier versions of Javascript without Array.flatMap():
//
// return latitude
// .map((_, d) => [latitude, longitude]
// .map(meridian => meridian[d]))
// .reduce((code, pair) => code.concat(pair))
// .map((d, i) => (i === 8 ? '+' : '') + _numerals.charAt(d))
// .join('');
}
encodePlusCode([ 37.944027, -93.635504 ]);
// returns '8698W9V7+JQ'
encodePlusCode('37.944027, -93.635504'.split(', '));
// returns '8698W9V7+JQ'
// EDIT: had to try to do a decoder:
// EDIT #2: made this more legible by using destructuring syntax in the reduce method in the below function.
/**
* Decodes an open location/plus code
*
* @param {string} plusCode - a valid open location/plus code
* @param {number} [places = 6] - the number of decimal places for all return values
* @returns {number[]} latitude and longitude, to the number of specified decimal {@link places}
*
* @example
* // decodePlusCode(encodePlusCode([ 37.944027, -93.635504 ]));
* // returns [ 37.944063, -93.635563 ]
*/
const decodePlusCode = (plusCode, places = 6) => {
const [ latitude, longitude ] = plusCode
.replace('+','')
.split('')
.map(d => _numerals.indexOf(d))
.reduce(([lat, lon], d, i) => i % 2 === 0 ? [[...lat, d], lon] : [lat, [...lon, d]], [[], []])
.map(meridian => meridian.reduce((total, d, i) => total + d * Math.pow(20, 1 - i), 0))
.map(meridian => meridian + Math.pow(20, 2 - plusCode.replace('+', '').length / 2) / 2);
return [
+(latitude - 90).toFixed(places),
+(longitude - 180).toFixed(places)
];
}
decodePlusCode(encodePlusCode([ 37.944027, -93.635504 ]));
// returns [ 37.944063, -93.635563 ]
encodePlusCode(decodePlusCode('8698W9V7+JQ'));
// returns '8698W9V7+JQ'
Dec 27, 2020 06:17 AM
I don’t know, but if so, for what purpose and what benefit? To make integration to achieve additional features impossible?
@Kasra, any chance you can explain why a location token is used instead of a simpler, space-optimizing and transparent approach based on open standards? Can you also point me to documentation that would allow me to decode these tokens?
Dec 27, 2020 07:19 AM
Also, nobody should be using the Maps App at all, because it generates an insanely excessive amount of google maps api calls, which can end up costing people hundreds or thousands of dollars in usage fees from Google.
The Maps App is just another half-baked feature of Airtable.
Dec 27, 2020 07:24 AM
Indeed, but setting aside this noted and potentially costly disfunction, unsuspecting users are geo-encoding their locations using an approach that obfuscates the true location data in a way that makes it near impossible to add value or expand the use cases for location data.
If there was a simple way to unpack these map codes, I’d be less inclined to complain, but I see only downside to this type of location object. Perhaps someone can enlighten us why this approach was used. Maybe there are good reasons - I want to understand.
Dec 27, 2020 11:46 AM
I use it, and yes this is true. But you can set quota restrictions upon your Google acct. This really should be front and center when people use this app, however, or Airtable should work out an arrangement with Google…