Capturing coordinate epoch in database

[rouault]

Background: ​https://github.com/OSGeo/gdal/pull/3810 and conversation at ​https://twitter.com/pwramsey/status/1390403943969169408

How should PostGIS capture the coordinate epoch of geometries referenced against a dynamic CRS (that is a non-plate-fixed CRS, such as WGS 84 (G1762), ITRF2014, etc), for which it is required to store the coordinate epoch to have centimer-level precision during coordinate transformation ?

Potential ideas:

• add a coordinate_epoch column to geometry_columns / geography_columns
• add a coordinate_epoch column to spatial_ref_sys
• add a SRTEXT2 column to spatial_ref_sys that could contain WKT:2019 content and the COORDINATEMETADATA[] construct of ​http://docs.opengeospatial.org/is/18-010r7/18-010r7.html#130
• but GeoPackage did something similar as the SRTEXT2 solution (​http://www.geopackage.org/spec121/#extension_crs_wkt) and are now considering just having the column that contains WKT to allow WKT:2019 (​https://github.com/opengeospatial/geopackage/issues/508)
• add the coordinate epoch in EWKT / EWKB

[Algunenano]

I understand that the epoch of a coordinate is a property of the coordinate or at most of the geometry, not the whole table (since that can have mixed geometries and srid/CRSs) so I wouldn't store it in geometry_columns.

I see 2 options that could complement each other:

• Have SRIDs with associated epochs, which mean you might get N SRIDs per CRS.
• Have the epoch as part of the geometry metadata/header.

So if the source or target SRSs require an epoch you would read it from the geometry header (if available) or from the SRID (if available). Would that make sense?

[rouault]

So if the source or target SRSs require an epoch you would read it from the geometry header (if available) or from the SRID (if available). Would that make sense?

Yes

[komzpa]

What is an epoch?

Can it be resolved by having T timestamp dimension in coordinates? It will also help with trajectories greatly and possibly merge in a lot of MobilityDB.

[rouault]

What is an epoch?

See ​https://docs.opengeospatial.org/as/18-005r4/18-005r4.html#68

Can it be resolved by having T timestamp dimension in coordinates?

That would be overkill. The finest reasonable granularity for a coordinate epoch is the geometry level, not the vertex. Note that the coordinate epoch is not necessarily the timestamp at which it was collected. This could be a few months or years before. This pretty much depends on the processing done in the acquisition device.

[mdavis]

Note this statement in the ​18-005 standard:

It is vital to realise that in all of these examples the [epoch] suffix “2017.53” refers to the coordinates. It does not belong to the CRS and therefore does not modify in any way the definition of [the coordinate systems].

To me this suggests that encoding the epoch as part of a SRID definition is not ideal.

[mdavis]

Another option:

• Store the epoch as a column beside the geometry column. This could be an epoch number, or a Postgres timestamp. Add an option to the ST_Transform function to accept an epoch modifier for each CRS.

This is a low-bar option, which might be a good first step to providing more "baked-in" support for epochs. It also aligns with existing models for storing temporal information about spatial data.

The modification to ST_Transform seems like a good idea regardless.

[rouault]

There's a similar discussion how to do that in GeoPackage: ​https://github.com/opengeospatial/geopackage/issues/599

[pramsey]

Milestone renamed

[rouault]

FYI

• GeoPackage will add a "epoch" coordinate to its gpkg_spatial_ref_sys table:https://github.com/opengeospatial/geopackage/pull/600
• related discussion for Spatialite: ​https://groups.google.com/g/spatialite-users/c/NtbuBpRzYBE

[robe]

In prep for 3.1.3 release

[robe]

structural changes can't be done in a micro