Postgresql – Would it be worthwhile to use the PostGIS extension for custom planets

datatypespostgispostgresql

I need to be able to store latitude and longitude in several different tables. My understanding is that PostGIS has a geography column type that does this.

However, I'm not storing coordinates on Earth, but those on fictional planets (for a game). I'm not a cartographer or any other sort of expert on this thing, knowing only what I'd learned in elementary/high school (90°S to 0° to 90°N for latitude, 180°E to 0° to 180°W for longitude).

Will the PostGIS extension be overkill for something I could manage as a composite type myself? Would I be able to use any of the additional functionality, for instance to be able to determine the distance between two points on the same planet, given that I assume PostGIS makes assumptions about those coordinates being located on our own planet?

Is PostGIS suitable for such uses as mine?

Best Answer

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
Related Question