How to? - Google Maps API - Filtering number of people by X km radius/distance from their address

[Stephen]

Hi Everyone

Assuming I have the a ‘location address’ for each user (where the full address is contained in one cell and not separated out in the form of Address 1, Address 2, Region, Country Etc across multiple cells assigned to that specific user), does anyone know of a methodology to return the number of users within a ‘X’ radius? Someone mentioned I could do this via the Google Maps API but would appreciate any instruction on how to do this. :slightly_smiling_face: ?

For example. Of 1k users, I require a list of users who live within 5kms of a particular zip/post code.

Any advice would be greatly appreciated.

Thank you

Stephen

[TheTimeSavingCo]

Hey Stephen, if you’d be alright with using a box around a particular zipcode instead of a circle then you could:

1. Convert the addresses into latitude and longitude points
2. Create a new table for those zipcodes you want to check against
3. Convert those zipcodes into latitude and longitude points
4. Create formula fields to calculate the upper and lower ranges of the box around the zipcodes from point 3
5. Use an automation to find all the addresses that fall within the ranges calculated in point 4, and then attach those addresses to the appropriate record

It’d be way less accurate though; with reference to your requirement of within 5km, the best we’d be able to do would be 5-7km instead (assuming 5km is the lower bound)

Best of all you’d be able to set this all up and maintain it yourself

---

If you’re really set on using a radius for accuracy instead, that would probably require some scripting as we’d have to do some math to calculate the distance between the address and the zipcode you’re checking against. Doable, but also comes with its own (much lower) margin of error

(Sorry, not too sure how you’d do this via the Google Maps API, so can’t provide any input on that I’m afraid)

[Bill_French]

Stephen:

… a list of users who live within 5kms of a particular zip/post code.

There are a few nuanced aspects of this question.

• Since an outer postcode (like HB1) or zipcode (80435) are polygons, not points, measuring the distance of an address from these objects requires that you either compute the centroid of the polys or determine the point at which the direct-most roadway crosses into that poly.

• Distance can be a radius; however, that will be imprecise because the distance you seek is likely for vehicles, not for drones.

• Even if you have travel distance, that can be affected by traffic, so you may want to consider travel time, not the distance as a measure of range and resources.

• To do this well, you need to use isochrones which measure travel distance or travel time from a given point at a given moment of the day.

• Whatever degree or precision you require, you will need to perform geometry computations which always require script.

The purple shading is the area you can reach from a vehicle in 60 minutes from any of the flaged points.

My Location Science Portfolio has a number of examples with distance and isochrones.

Coda | Everything evolves, the evolution of documents. – 3 Mar 20

Location Science Portfolio

Global Technologies Corporation Location Science Examples

[ScottWorld]

@Stephen I actually don’t know how to accomplish this easily in Airtable, and it would likely require scripting if it can be accomplished.

However, I have been through this process dozens of times with my clients using FileMaker, which makes it extremely easy & trivial to find people within X miles radius of a zip code, using any of the many radius/distance/geolocation functions listed here (and elsewhere on the Internet).

I don’t do FileMaker consulting anymore because I have transitioned my business to Airtable consulting, but if you need me to refer you to some fantastic FileMaker consultants, just contact me through my Airtable consulting website.

Also, if required, FileMaker and Airtable can also communicate with each other using REST API calls.

[Stephen]

Thanks to everyone for their input. I’ll take on board everything said and look into this further. In reality the usage will not be that complex, and does not need to be highly accurate. It’s purely to determine what respondents are within x miles of a market research project, and then send only them an email to see if they’d like to take part as it’s in their locality.

[Bill_French]

Stephen:

does not need to be highly accurate

Ha ha! Okay, so only somewhat accurate. :winking_face:

Stephen:

respondents are within x miles

There are places in the US where you can see the homes you are targeting; they are less than a few miles away. But the drive time is more than 45 minutes.

There is also the corollary - people who live outside the radius can easily get to the centre of the radius faster than people inside the radius. I know it sounds weird, but the data doesn’t lie; traffic can change everything. In fact, my approach also factors in left vs right turns in the US and the opposite in the UK. If a customer has to navigate a high percentage of left turns, they are considered less likely to engage in commerce.

Miles as the crow flies generally works, but it doesn’t scale well. It’s the hidden side of data that often sets us up for errors and little imprecisions that tend to accumulate.

[Stephen]

I totally understand where you’re coming from on this Bill, and ‘fully’ agree with the importance of accuracy, but it would be imagine in terms of development time it would be overkill in this small usage requirement. :slightly_smiling_face: In a nutshell, the main overriding aim is to cut down on the number/percentage of possible respondents that would receive the initial email informing them of the the research e.g. so instead of sending to the whole population of London (based on Region, which is the current methodology), only those that live within say 5 miles of the research study would receive an email notification (this then cuts the email delivery target down by 95%). Let’s say only 100 people actually then go on to complete and pass the research screener, there’s only probably 15 research places actually available (and hence why if 5 of them, after a telephone conversation, then prove to live too far in terms of ‘travel’ time, we’d just reach out to the next 5 people on the list).

We actually operated off this milage/radius method on our previous Drupal website and it worked very well indeed, but since then have moved to a low-code strategy and hence why I’m trying to re-create it in Airtable. :slightly_smiling_face:

[Bill_French]

Stephen:

development time it would be overkill in this small usage requirement

This is why concise business requirements are so beneficial when seeking technical guidance. If you frame the business narrative, developers can shape their responses with greater precision.

I require a list of users who live within 5kms of a particular zip/post code.

This, for example, can be interpreted in many ways.

• Is it within 5kms of the outer boundary of the postcode?
• Is it within 5kms of the outer boundary of the outer postcode (i.e., the district code)?
• Is it within 5kms of the outer boundary of the inner postcode (i.e., the complete code)?
• Is it within 5kms of the centroid of the postcode?

Stephen:

… and hence why if 5 of them, after a telephone conversation, then prove to live too far in terms of ‘travel’ time, we’d just reach out to the next 5 people on the list

Understood. The deeply lazy person inside me would then say -

How do I avoid conversing with people who are ostensibly a dead-end?

Maybe there’s a benefit to acquiring data from one-on-one conversations that ultimately leads nowhere (another unstated business requirement, perhaps?). However, location science gives us the tools to compress the time and effort to get the best answers more quickly. And the lazy me is happier about that. LOL