By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
SELECT ST_DistanceSphere('POINT(0 0)'::geometry,
'POINT(180 0)'::geometry) -- half_the_circumference
/pi() -- 2*pi*r is the circumference, so half the circumference/pi is the radius
AS PostgreSQL_assumed_radius;
| postgresql_assumed_radius |
|---|
| 6371008.771415061 |
SELECT 1
SELECT ST_DistanceSphere(ST_SetSRID('POINT(0 0)'::geometry,4326),
ST_SetSRID('POINT(180 0)'::geometry,4326)) -- half_the_circumference
/pi() -- 2*pi*r is the circumference, so half the circumference/pi is the radius
AS PostgreSQL_assumed_radius_for_4326;
| postgresql_assumed_radius_for_4326 |
|---|
| 6371008.771415061 |
SELECT 1
SELECT ST_DistanceSphere(
st_geomfromtext('POINT(0 -90)'),
st_geomfromtext('POINT(0 90)')
) AS latitudinal_distance_pole_to_pole;
SELECT ST_DistanceSphere(
st_geomfromtext('POINT(0 0)'),
st_geomfromtext('POINT(180 0)')
) AS longitudinal_around_half_the_globe;
| latitudinal_distance_pole_to_pole |
|---|
| 20015114.35223369 |
SELECT 1
| longitudinal_around_half_the_globe |
|---|
| 20015114.35223369 |
SELECT 1
SELECT ST_DistanceSphere(
st_geomfromtext('POINT(-74.0060 40.7128)'), -- New York
st_geomfromtext('POINT(-118.2437 34.0522)') -- Los Angeles
) AS distance;
| distance |
|---|
| 3935751.67323537 |
SELECT 1
SELECT ST_DistanceSphere(
st_setsrid(st_geomfromtext('POINT(-74.0060 40.7128)'),4326), -- New York
st_setsrid(st_geomfromtext('POINT(-118.2437 34.0522)'),4326) -- Los Angeles
) AS distance;
| distance |
|---|
| 3935751.67323537 |
SELECT 1
SELECT ST_DistanceSphere(
st_geomfromtext('POINT(-74.0060 40.7128)'), -- New York
st_geomfromtext('POINT(-118.2437 34.0522)'), -- Los Angeles
6378100 --custom radius, conforming to MongoDB's config
) AS distance;
| distance |
|---|
| 3940132.347594769 |
SELECT 1
select * from spatial_ref_sys where srid=4326;--6378137
| srid | auth_name | auth_srid | srtext | proj4text |
|---|---|---|---|---|
| 4326 | EPSG | 4326 | GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]] | +proj=longlat +datum=WGS84 +no_defs |
SELECT 1