Randomize coordinates within a given radius

[le_Labo_M]

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 !

[Ron_Daniel]

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:

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
]

[le_Labo_M · Accepted solution]

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 :

I want an airtable script code that use the Haversine formula to create new coordinates that are 25km from given coordinates

-> 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 :

Can I have the same code but with a distance withnin a radius of 25km maximum and 15km minimum ?Sure! Here's an updated version of the script that generates new coordinates within a radius of 25 km maximum and 15km minimum from a given set of coordinates:

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));

[le_Labo_M]

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 :

1. take the latitude,
2. generate a random number between 0 and 1 and divide it by 300 (that seems to be a good ratio to be around)
3. generate a second random number and test if it's bigger than 0.5. If bigger, value is 1 and if not -1.
4. 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 !