By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT ((ST_Distance_Sphere(POINT(0, 0), POINT(180, 0))) -- half_the_circumference
*2 -- doubling to get the full cirumference
)/(2*pi()) -- 2*pi*r is the circumference, so the circumference/2*pi is the radius
AS mySQL_assumed_radius;
| mySQL_assumed_radius |
|---|
| 6370986 |
-- better yet, without the doubling
SELECT ST_Distance_Sphere(ST_SRID(POINT(0, 0),4326),
ST_SRID(POINT(180, 0),4326)) -- half_the_circumference
/pi() -- 2*pi*r is the circumference, so half the circumference/pi is the radius
AS mySQL_assumed_radius_4326;
| mySQL_assumed_radius_4326 |
|---|
| 6371008.771415059 |
-- better yet, without the doubling
SELECT ST_Distance_Sphere(POINT(0, 0), POINT(180, 0)) -- half_the_circumference
/pi() -- 2*pi*r is the circumference, so half the circumference/pi is the radius
AS mySQL_assumed_radius;
| mySQL_assumed_radius |
|---|
| 6370986 |
SELECT ST_Distance_Sphere(
POINT(0, 90),
POINT(0, -90)
) AS latitudinal_distance_pole_to_pole;
SELECT ST_Distance_Sphere(
POINT(0, 0),
POINT(180, 0)
) AS longitudinal_around_half_the_globe;
| latitudinal_distance_pole_to_pole |
|---|
| 20015042.813723423 |
| longitudinal_around_half_the_globe |
|---|
| 20015042.813723423 |
SELECT ST_Distance_Sphere(
POINT(-74.0060, 40.7128), -- New York
POINT(-118.2437, 34.0522) -- Los Angeles
) AS distance;
| distance |
|---|
| 3935737.605975668 |
SELECT ST_Distance_Sphere(
ST_SRID(POINT(-74.0060, 40.7128),4326),-- New York
ST_SRID(POINT(-118.2437, 34.0522),4326) -- Los Angeles
) AS distance;
| distance |
|---|
| 3935751.6732353647 |
SELECT ST_Distance_Sphere(
ST_SRID(POINT(-74.0060, 40.7128),4326),-- New York
ST_SRID(POINT(-118.2437, 34.0522),4326), -- Los Angeles
6378100 -- custom radius, conforming to MongoDB's config
) AS distance;
| distance |
|---|
| 3940132.3475947683 |