Save the date! Join us on October 16 for our Product Ops launch event. Register here.
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.
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 :
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]; }
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]; }
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:
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
]
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 :
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]; }
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]; }
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 :
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 !