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