This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

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

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Other questions
- Re: 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

Solved

Jump to Solution

0
1194
0

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 !