Opened 16 months ago

Last modified 7 weeks ago

#4911 new enhancement

Capturing coordinate epoch in database

Reported by: rouault Owned by: pramsey
Priority: medium Milestone: PostGIS Fund Me
Component: postgis Version:
Keywords: Cc: jorisvandenbossche, rcoup

Description

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:

Change History (16)

comment:1 by Algunenano, 16 months ago

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?

comment:2 by rouault, 16 months ago

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

comment:3 by komzpa, 15 months ago

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.

comment:4 by rouault, 15 months ago

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.

comment:5 by jorisvandenbossche, 15 months ago

Cc: jorisvandenbossche added

comment:6 by mdavis, 15 months ago

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.

comment:7 by mdavis, 15 months ago

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.

comment:8 by rouault, 15 months ago

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

comment:9 by pramsey, 15 months ago

Milestone: PostGIS 3.1.23.1.3

comment:10 by pramsey, 15 months ago

Milestone: 3.1.3PostGIS 3.1.3

Milestone renamed

comment:11 by rcoup, 15 months ago

Cc: rcoup added

comment:12 by rouault, 15 months ago

FYI

comment:13 by robe, 14 months ago

Milestone: PostGIS 3.1.3PostGIS 3.1.4

In prep for 3.1.3 release

comment:14 by robe, 14 months ago

Milestone: PostGIS 3.1.4PostGIS 3.2.0

structural changes can't be done in a micro

comment:15 by pramsey, 12 months ago

Milestone: PostGIS 3.2.0PostGIS 3.3.0

comment:16 by robe, 7 weeks ago

Milestone: PostGIS 3.3.0PostGIS Fund Me
Note: See TracTickets for help on using tickets.