Snowflake does not have a direct equivalent to the ST_DISTANCESPHERE function. However, you can use the Haversine formula to calculate the great-circle distance between two points on the Earth’s surface using the spherical Earth approximation.
Here’s an example of how to use the Haversine formula in Snowflake:
WITH coordinates AS (
SELECT
-- San Francisco (latitude, longitude)
37.7758333 AS lat1,
-122.4180556 AS lon1,
-- New York (latitude, longitude)
40.67 AS lat2,
-73.9444444 AS lon2
)
SELECT
2 * 6371 * ASIN(
SQRT(
POWER(SIN(RADIANS(lat2 - lat1) / 2), 2) +
COS(RADIANS(lat1)) * COS(RADIANS(lat2)) *
POWER(SIN(RADIANS(lon2 - lon1) / 2), 2)
)
) * 1000 AS distance_meters
FROM coordinates;
This query calculates the approximate distance between San Francisco and New York in meters using the Haversine formula. Replace the latitude and longitude values with the desired points to calculate the distance between them.
Please note that this method provides an approximation of the distance, as it assumes a spherical Earth, while the actual Earth’s shape is closer to an oblate spheroid. For most purposes, this approximation should be sufficient.
Snowflake important urls to refer
