Custom Formula for Accurate Direction Calculation in Airtable

[adriancheofski]

Hello Airtable Community,

I'm working on a project that involves calculating the Qibla direction using Airtable. I have latitude and longitude data for various locations stored in my Airtable base. I need to create a custom formula that can accurately determine the Qibla direction for each location.

I have come across the term "boussole prière en ligne" (online prayer compass) and understand its importance in providing precise Qibla directions. Could someone guide me on how to implement the necessary trigonometric calculations within Airtable’s formula field to achieve this? Specifically, I’m looking for a way to:

1. Input the coordinates of the Kaaba (latitude: 21.4225, longitude: 39.8262).
2. Use the haversine formula or any other relevant trigonometric function to compute the initial bearing from any given point to the Kaaba.
3. Ensure the result is displayed in degrees, representing the direction to the Qibla.

I would appreciate any examples or step-by-step guidance on setting up this formula. If scripting is required, advice on incorporating it effectively within the Airtable environment would be beneficial.

Thank you for your assistance!

[Saravanan_009]

To calculate the Qibla direction using Airtable, you can use a combination of trigonometric calculations to determine the initial bearing from a given location to the Kaaba. Here’s how you can approach this problem using Airtable’s formula field:

Formula Breakdown: The initial bearing (or azimuth) can be calculated using the following steps:
Convert latitude and longitude from degrees to radians. Apply the trigonometric formulas to find the bearing. Convert the result back to degrees.

Formula Components
Latitude and Longitude of the Kaaba:
Latitude (Kaaba): 21.4225
Longitude (Kaaba): 39.8262

Input Coordinates (let’s assume these are columns in Airtable):
Latitude (Location): {Latitude}
Longitude (Location): {Longitude}

Steps to Implement in Airtable: Add Fields for Latitude and Longitude:
Ensure you have fields for the latitude and longitude of the location and the Kaaba.

Add Formula Field for Bearing Calculation: Create a new formula field to compute the Qibla direction. Airtable’s formula field can handle basic trigonometric functions.

Here’s a detailed formula you can use:

// Convert degrees to radians
LAT_KAABA = 21.4225
LON_KAABA = 39.8262
LAT_LOC = {Latitude}
LON_LOC = {Longitude}

LAT_KAABA_RAD = LAT_KAABA * PI() / 180
LON_KAABA_RAD = LON_KAABA * PI() / 180
LAT_LOC_RAD = LAT_LOC * PI() / 180
LON_LOC_RAD = LON_LOC * PI() / 180

// Calculate differences
LON_DIFF = LON_LOC_RAD - LON_KAABA_RAD

// Bearing calculation
X = SIN(LON_DIFF) * COS(LAT_LOC_RAD)
Y = COS(LAT_KAABA_RAD) * SIN(LAT_LOC_RAD) - SIN(LAT_KAABA_RAD) * COS(LAT_LOC_RAD) * COS(LON_DIFF)

BEARING_RAD = ATAN2(X, Y)
BEARING_DEG = (BEARING_RAD * 180 / PI() + 360) % 360

// Final formula to output
BEARING_DEG

Unfortunately, Airtable’s formula field doesn’t directly support all the functions needed for trigonometric calculations in a straightforward manner. For complex calculations like these, you might need to use Airtable’s scripting block. Here’s how you can use Airtable Scripting:

Set Up Scripting Block: Go to the Apps section in Airtable and add a Scripting block.
Write the Script: Use the following JavaScript code in the scripting block:

let table = base.getTable("YourTableName");
let query = await table.selectRecordsAsync();
let kaabaLat = 21.4225;
let kaabaLon = 39.8262;

for (let record of query.records) {
let locLat = record.getCellValue("Latitude");
let locLon = record.getCellValue("Longitude");

// Convert degrees to radians
let latKaabaRad = kaabaLat * Math.PI / 180;
let lonKaabaRad = kaabaLon * Math.PI / 180;
let latLocRad = locLat * Math.PI / 180;
let lonLocRad = locLon * Math.PI / 180;

// Calculate differences
let lonDiff = lonLocRad - lonKaabaRad;

// Bearing calculation
let x = Math.sin(lonDiff) * Math.cos(latLocRad);
let y = Math.cos(latKaabaRad) * Math.sin(latLocRad) - Math.sin(latKaabaRad) * Math.cos(latLocRad) * Math.cos(lonDiff);

let bearingRad = Math.atan2(x, y);
let bearingDeg = (bearingRad * 180 / Math.PI + 360) % 360;

// Update the record with the bearing
await table.updateRecordAsync(record.id, {
"Bearing to Qibla": bearingDeg
});
}

Run the Script: Execute the script to compute and update the Qibla direction for each record in your table.