Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Other questions
- Randomize coordinates within a given radius

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Extensions

Solved

Jump to Solution

0
1608
3

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 23, 2023 03:30 AM

Hi everyone,

I need to show people on a map but for privacy concerns I would like to change the exact position randomly. I found a ruby script that is doing exactly what I need : do you think it could be possible to turn it into an airtable script or with formulas ?

I don't have any experience with scripts or coding yet so I don't know if it's something that Airtable can do.

Here's the ruby script :

lat = (point[0] * Math::PI / 180).to_d lon = (point[1] * Math::PI / 180).to_d max_distance = 200.to_d min_distance = 100.to_d earth_radius = 6_371_000.to_d distance = Math.sqrt(rand * (max_distance ** 2 - min_distance ** 2) + min_distance ** 2) delta_lat = Math.cos(rand * Math::PI) * distance / earth_radius sign = rand(2) * 2 - 1 delta_lon = sign * Math.acos( ((Math.cos(distance/earth_radius) - Math.cos(delta_lat)) / (Math.cos(lat) * Math.cos(delta_lat + lat))) + 1) [(lat + delta_lat) * 180 / Math::PI, (lon + delta_lon) * 180 / Math::PI]

Do you have an idea how to transcript this ?

Thanks for you answers !

Note : this code comes from this website : https://jordinl.com/posts/2019-02-15-how-to-generate-random-geocoordinates-within-given-radius : explanations are quite interesting !

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 23, 2023 03:00 PM

Hi Ron,

Thanks for your answer. I initially tried to convert the ruby code into a javascript code, but I don't know anything about coding and it was too hard for me to make it work. Then I asked the ChatGPT bot to write me a code from scratch, here's the prompt and answer :

-> Here is an example of an Airtable script that uses the Haversine formula to generate new coordinates that are 25 km away from a set of given coordinates:

function generateNewCoordinates(lat, lon) { // Earth's radius in kilometers var R = 6371; // Distance to move in kilometers var distance = 25; // Bearing (degrees) var bearing = Math.random() * 360; // Convert latitude and longitude to radians var lat1 = lat * (Math.PI / 180); var lon1 = lon * (Math.PI / 180); // Calculate new latitude and longitude var lat2 = Math.asin( Math.sin(lat1)*Math.cos(distance/R) + Math.cos(lat1)*Math.sin(distance/R)*Math.cos(bearing) ); var lon2 = lon1 + Math.atan2(Math.sin(bearing)*Math.sin(distance/R)*Math.cos(lat1), Math.cos(distance/R)-Math.sin(lat1)*Math.sin(lat2)); // Convert back to degrees lat2 = lat2 * (180 / Math.PI); lon2 = lon2 * (180 / Math.PI); // Return new coordinates return [lat2, lon2]; }

You can use this function in your script, and pass it the initial coordinates (latitude and longitude) as arguments, to generate a new set of coordinates that are 25 km away from the original coordinates.

---

Then I asked for some improvments :

function generateNewCoordinates(lat, lon) { // Earth's radius in kilometers var R = 6371; // Maximum distance to move in kilometers var maxDistance = 25; // Minimum distance to move in kilometers var minDistance = 15; // Random distance within the range var distance = Math.random() * (maxDistance - minDistance) + minDistance; // Bearing (degrees) var bearing = Math.random() * 360; // Convert latitude and longitude to radians var lat1 = lat * (Math.PI / 180); var lon1 = lon * (Math.PI / 180); // Calculate new latitude and longitude var lat2 = Math.asin( Math.sin(lat1)*Math.cos(distance/R) + Math.cos(lat1)*Math.sin(distance/R)*Math.cos(bearing) ); var lon2 = lon1 + Math.atan2(Math.sin(bearing)*Math.sin(distance/R)*Math.cos(lat1), Math.cos(distance/R)-Math.sin(lat1)*Math.sin(lat2)); // Convert back to degrees lat2 = lat2 * (180 / Math.PI); lon2 = lon2 * (180 / Math.PI); // Return new coordinates return [lat2, lon2]; }

This script works the same way as the previous one, but now it calculates a random distance between 15 and 25 kilometers, instead of always generating coordinates 25km away from the initial point.

---

That's quite amzing, I managed with that to build the script and launch it within an automation. I though I'd killed Airtable as it stays on "pending" for quite long on the second task on maaaany, but after dinner the job was done !

Some weeks ago I would not have though about coding without knowing anything in JS : for this AI is woow.

For those who would like to do the same, just add this two lines at the end of the above AI generated code :

let inputConfig = input.config();

output.set('RandomCoordinates', generateNewCoordinates(inputConfig.lat, inputConfig.lon));

3 Replies 3

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 23, 2023 12:39 PM

How random do you need it to be? You could do a simple rounding of the Lat/Lon numbers to generalize. For example, rounding them down to one decimal place will reduce the accuracy to 100 sq km. In that case, you could simply do this:

Formulas in this are:

Rounded Latitude: ROUND({True Latitude}, 1)

Rounded Longitude: ROUND({True Longitude}, 1)

Since Airtable doesn't have a random number generator, you could also convert the Ruby script to JavaScript and use the scripting extension to truly randomize it. Using a convertor, it looks like that JavaScript would be something like:

Rounded Longitude: ROUND({True Longitude}, 1)

Since Airtable doesn't have a random number generator, you could also convert the Ruby script to JavaScript and use the scripting extension to truly randomize it. Using a convertor, it looks like that JavaScript would be something like:

var lat = (point[0] * Math.PI / 180).to_d;

var lon = (point[1] * Math.PI / 180).to_d;

var max_distance = (200).to_d;

var min_distance = (100).to_d;

var earth_radius = (6371000).to_d;

var distance = Math.sqrt(rand * (Math.pow(max_distance, 2) - Math.pow(min_distance, 2)) + Math.pow(min_distance, 2));

var delta_lat = Math.cos(rand * Math.PI) * distance / earth_radius;

var sign = rand(2) * 2 - 1;

var delta_lon = sign * Math.acos(((Math.cos(distance / earth_radius) - Math.cos(delta_lat)) / (Math.cos(lat) * Math.cos(delta_lat + lat))) + 1);

[

(lat + delta_lat) * 180 / Math.PI,

(lon + delta_lon) * 180 / Math.PI

]

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 23, 2023 03:00 PM

Hi Ron,

Thanks for your answer. I initially tried to convert the ruby code into a javascript code, but I don't know anything about coding and it was too hard for me to make it work. Then I asked the ChatGPT bot to write me a code from scratch, here's the prompt and answer :

-> Here is an example of an Airtable script that uses the Haversine formula to generate new coordinates that are 25 km away from a set of given coordinates:

function generateNewCoordinates(lat, lon) { // Earth's radius in kilometers var R = 6371; // Distance to move in kilometers var distance = 25; // Bearing (degrees) var bearing = Math.random() * 360; // Convert latitude and longitude to radians var lat1 = lat * (Math.PI / 180); var lon1 = lon * (Math.PI / 180); // Calculate new latitude and longitude var lat2 = Math.asin( Math.sin(lat1)*Math.cos(distance/R) + Math.cos(lat1)*Math.sin(distance/R)*Math.cos(bearing) ); var lon2 = lon1 + Math.atan2(Math.sin(bearing)*Math.sin(distance/R)*Math.cos(lat1), Math.cos(distance/R)-Math.sin(lat1)*Math.sin(lat2)); // Convert back to degrees lat2 = lat2 * (180 / Math.PI); lon2 = lon2 * (180 / Math.PI); // Return new coordinates return [lat2, lon2]; }

You can use this function in your script, and pass it the initial coordinates (latitude and longitude) as arguments, to generate a new set of coordinates that are 25 km away from the original coordinates.

---

Then I asked for some improvments :

function generateNewCoordinates(lat, lon) { // Earth's radius in kilometers var R = 6371; // Maximum distance to move in kilometers var maxDistance = 25; // Minimum distance to move in kilometers var minDistance = 15; // Random distance within the range var distance = Math.random() * (maxDistance - minDistance) + minDistance; // Bearing (degrees) var bearing = Math.random() * 360; // Convert latitude and longitude to radians var lat1 = lat * (Math.PI / 180); var lon1 = lon * (Math.PI / 180); // Calculate new latitude and longitude var lat2 = Math.asin( Math.sin(lat1)*Math.cos(distance/R) + Math.cos(lat1)*Math.sin(distance/R)*Math.cos(bearing) ); var lon2 = lon1 + Math.atan2(Math.sin(bearing)*Math.sin(distance/R)*Math.cos(lat1), Math.cos(distance/R)-Math.sin(lat1)*Math.sin(lat2)); // Convert back to degrees lat2 = lat2 * (180 / Math.PI); lon2 = lon2 * (180 / Math.PI); // Return new coordinates return [lat2, lon2]; }

This script works the same way as the previous one, but now it calculates a random distance between 15 and 25 kilometers, instead of always generating coordinates 25km away from the initial point.

---

That's quite amzing, I managed with that to build the script and launch it within an automation. I though I'd killed Airtable as it stays on "pending" for quite long on the second task on maaaany, but after dinner the job was done !

Some weeks ago I would not have though about coding without knowing anything in JS : for this AI is woow.

For those who would like to do the same, just add this two lines at the end of the above AI generated code :

let inputConfig = input.config();

output.set('RandomCoordinates', generateNewCoordinates(inputConfig.lat, inputConfig.lon));

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 23, 2023 03:13 PM

When I though Airtable won't be able to do the script task (that was my second script I don't now anything about airtable capability on large data sets with scripts), I explored an Excel solution to do the first batch, with some randomize numbers to add to the coordinates.

Here is what I did :

- take the latitude,
- generate a random number between 0 and 1 and divide it by 300 (that seems to be a good ratio to be around)
- generate a second random number and test if it's bigger than 0.5. If bigger, value is 1 and if not -1.
- Calculate [1]+([2]*[3]) -> here is the randomize latitude, not so far away from the original.

Same for longitude !

Note : of course every single value has to have its own random number, not one for all 😉

Results were quite ok but less "accurate" than the above script that was precisely between 15 and 25km for the origin (having a precise randomized location looks quite paradoxal ! )

This can help for a large amount of data, then the automation can do the job without problem.

Have a good day !