Spatial index in Airtable? Or some other way to query locations within radius of latitude/longitude?

[Sean_Tierney]

TL;DR - trying to determine best way to notify users as new locations come online within their specified location radius.

More context: I created Charity Makeover and am in process of building an Airtable app to enable people in other cities to run these events. It’s similar in concept to Startup Weekend only focused on building stuff for local charities. I’m essentially looking to replicate some of the functionality of Meetup in that I want to give prospective volunteers the ability to get notified when a new Charity Makeover event happens within their locale.

My question is: is there a simple way to accomplish this type of pub/sub setup with geolocation or does it need to be created from scratch? I’ve been researching terms like geofencing and proximity-based notifications to try and find some SaaS based way of accomplishing this rather than developing it. I have to imagine this is a common need with geo-based searches… Haven’t found a “packaged” way to do it yet so am now going down rabbit hole looking into GIS spatial index capability like exists in MySQL. Does the equivalent exist in Airtable? Most ideal would be a no-code way of handling this but second best would be a field type that expresses locational data and can be queried efficiently… thanks

[Sean_Tierney]

Just found something that looks promising:
developers.google. com/maps/documentation/distance-matrix/intro
(Discourse won’t let me post links nor images 😕 )

So with that Google Maps service I should be able to make an API call to determine the distance between two Lat/Lon coordinates. So seemingly what I could do here is each time we publish a new event, iterate over the user table and compare the coords of each user with the coords of the new event to see if that distance falls within their desired notification threshold. Seems straightforward. AFAIK only downside to this is that it’s not super scaleable and efficient (ie. will walk the entire user table on each new published event so will consume API requests and time). But doesn’t need to be real-time and looks like daily free quota of 100,000 requests per 24 hours for Gmaps API so shouldn’t hit that anytime soon…

If anyone has other ideas for a more efficient way to accomplish this I’m all ears. Not a dev by trade but fumbling my way through building this app thanks to glorious no-code tools like Airtable & Zapier. cheers

[Bill_French]

@Sean_Tierney,

Sean_Tierney:

best would be a field type that expresses locational data and can be queried efficiently

Airtable is not likely to get you there. I’m very familiar with the use of geometries to determine query results based on locations and relative distances - even moving objects in real-=time. One thing that’s evident - GIS features were requested a long [long] time ago and no movement since.

I would take a close look at Carto (specifically their PostGIS database and the ability to create codeless widgets and complete location-science applications) and also ElasticSearch - both support extensive spatial features that make these types of computations effortlessly.

I’m a Carto Technology Partner, so feel free to tag me in Linked In if you’re interested.

[Nicolas_Maltais]

Hello,
I know a bit about Carto and Airtable.
Would it be possible to link those two apps so than we can select a data in Carto and see it Airtable? Or the opposite?
Thanks,

[Bill_French]

Hi @Nicolas_Maltais-Tari and welcome to the Airtable community!

Nicolas_Maltais-Tari:

possible to link those two apps so than we can select a data in Carto and see it Airtable? Or the opposite?

Yes. Both products have an API and both products can visualize location data.

Nicolas_Maltais-Tari:

… can select a data …

This part of your question intimates a user experience that you’ll need to explain in greater depth. Note that Carto apps can be framed inside the HTTP Embed Block, however, Carto offers no such framing ability nor does Airtable support the independent framing of Blocks, the essential GiS mapping functionality.

[kalenjordan]

Somewhat related question on the topic of locations. I played with the maps integration and it’s pretty handy for a single location. I want to have a field that supports multiple locations. It’s for job listings where for example they could be hiring remotely in “North America OR South America” or they could be hiring in just the United States, or in a specific city.

I don’t really need a radius search at all though, generally just a search by country/state/metro areas is best.

Any thoughts on the best way to approach that?

[Bill_French]

kalenjordan:

I want to have a field that supports multiple locations.

Don’t you mean multiple fields that can be searched in combination?

Or, are you suggesting that you want a field that supports a complex geometry such as a polygon of a continent, the US, a state, a county, a city?

kalenjordan:

I don’t really need a radius search at all though

Understood - you want a geo-inclusion search, right?

[kalenjordan]

Ideally a single field. Almost like a Multiple Select field but instead of the select values being simple dropdown options, they would be google maps autocomplete objects.

[Nicolas_Maltais]

Hello,

Sorry I just saw this post today. I do not know a lot about API. Can you explain a bit what it is?

[Bill_French]

Nicolas_Maltais-Tari:

I do not know a lot about API. Can you explain a bit what it is?

Okay, but before we get into the concepts of an API, let’s go back to the comment where you said…

“… can select a data …”

Please explain the user experience you are describing with regard to geo-location mapping and the data itself. I want to understand what your goal is.