To be able to compute the distance on a sphere, you need the geography
type, i.e., you need PostGIS.
For your fictional planets, you need to add custom projections so that PostGIS knows about them. The PostGIS documentation says:
Although the PostGIS spatial_ref_sys
table contains over 3000 of the more commonly used spatial reference system definitions that can be handled by the proj library, it does not contain all known to man and you can define your own custom projection if you are familiar with proj4 constructs.
And if you are not familiar with proj4 constructs, you can do it anyway by taking some existing definition and modifying it. Go to spatialreference.org, and look at the projections defined by the International Astronomical Union, e.g., IAU2000:19900 for longitude/latitude on Mercury. There is an option to show the INSERT command for PostGIS:
INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext)
values ( 919900, 'iau2000', 19900, '+proj=longlat +a=2439700 +b=2439700 +no_defs ',
'GEOGCS["Mercury 2000",DATUM["D_Mercury_2000",SPHEROID["Mercury_2000_IAU_IAG",2439700.0,0.0]],PRIMEM["Greenwich",0],UNIT["Decimal_Degree",0.0174532925199433]]');
919900
is the SRID. User-defined PostGIS SRID values must be between 910000 and 998999; just select any unique number in this range.
iau2000
is the name of the authority that defines this projection. You are not the IAU, so replace it with something like The John O
.
19900
is a unique ID for the projection, defined by the authority. For simplicity, just use the same number as the SRID.
2439700
(3×) is the radius of Mercury, in meters. Replace this with your planet's radius.
Mercury 2000
is the name of the projection (here: the planet's name, and the year when it was defined). Also replace D_Mercury_2000
and Mercury_2000_IAU_IAG
with some other names.
Greenwich
is the name of the prime meridian. I do not think that the Greenwich Royal Observatory exists either on Mercury or on your planet, but these names are not actually used anyway. You need a value different from 0
only if there are different prime meridians.
0.0174532925199433
is the number of degrees per radian, and does not change.
For example, a projection for B-612 would look like this:
INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext)
values ( 912345, 'Antoine de Saint-Exupéry', 912345,
'+proj=longlat +a=1.75 +b=1.75 +no_defs',
'GEOGCS["B-612",DATUM["B-612",SPHEROID["B-612",1.75,0.0]],PRIMEM["The Rose",0],UNIT["Decimal_Degree",0.0174532925199433]]');
This can then be used in computations:
CREATE TABLE points_on_b612(name text, point geography(point, 912345));
INSERT INTO points_on_b612 VALUES ('north pole', ST_SetSRID(ST_MakePoint(0, 90), 912345));
INSERT INTO points_on_b612 VALUES ('south pole', ST_SetSRID(ST_MakePoint(0, -90), 912345));
SELECT a.name, b.name, ST_Distance(a.point, b.point)
FROM points_on_b612 a JOIN points_on_b612 b ON a.name < b.name;
name | name | st_distance
------------+------------+-------------
north pole | south pole | 5.49778714