A temporal value can be converted into a temporal value of a compatible type. This can be done using the notation CAST(ttype1 AS ttype2)
or ttype1::ttype2
.
Cast a temporal integer to a temporal float
tint::tfloat
SELECT tint '[1@2001-01-01, 2@2001-01-03]'::tfloat; -- "[1@2001-01-01 00:00:00+00, 2@2001-01-03 00:00:00+00]" SELECT tint '[1@2000-01-01, 2@2000-01-03, 3@2000-01-05]'::tfloat; -- "Interp=Stepwise;[1@2000-01-01 00:00:00+01, 2@2000-01-03 00:00:00+01, 3@2000-01-05 00:00:00+01]"
Cast a temporal float to a temporal integer
tfloat::tint
SELECT tfloat 'Interp=Stepwise;[1.5@2001-01-01, 2.5@2001-01-03]'::tint; -- "[1@2001-01-01 00:00:00+00, 2@2001-01-03 00:00:00+00]" SELECT tfloat '[1.5@2001-01-01, 2.5@2001-01-03]'::tint; -- ERROR: Cannot cast temporal float with linear interpolation to temporal integer
Cast a temporal geometry point to a temporal geography point
tgeompoint::tgeogpoint
SELECT asText((tgeogpoint 'Point(0 0)@2001-01-01')::tgeompoint); -- "{POINT(0 0)@2001-01-01}"
Cast a temporal geography point to a temporal geometry point
tgeogpoint::tgeompoint
SELECT asText((tgeompoint '[Point(0 0)@2001-01-01, Point(0 1)@2001-01-02)')::tgeogpoint); -- "{[POINT(0 0)@2001-01-01, POINT(0 1)@2001-01-02)}"
A common way to store temporal points in PostGIS is to represent them as geometries of type LINESTRING M
and abuse the M dimension to encode timestamps as seconds since 1970-01-01 00:00:00. These time-enhanced geometries, called trajectories, can be validated with the function ST_IsValidTrajectory
to verify that the M value is growing from each vertex to the next. Trajectories can be manipulated with the functions ST_ClosestPointOfApproach
, ST_DistanceCPA
, and ST_CPAWithin
. Temporal point values can be converted to/from PostGIS trajectories.
Cast a temporal point to a PostGIS trajectory
tgeompoint::geometry
tgeogpoint::geography
SELECT ST_AsText((tgeompoint 'Point(0 0)@2001-01-01')::geometry); -- "POINT M (0 0 978307200)" SELECT ST_AsText((tgeompoint '{Point(0 0)@2001-01-01, Point(1 1)@2001-01-02, Point(1 1)@2001-01-03}')::geometry); -- "MULTIPOINT M (0 0 978307200,1 1 978393600,1 1 978480000)" SELECT ST_AsText((tgeompoint '[Point(0 0)@2001-01-01, Point(1 1)@2001-01-02)')::geometry); -- "LINESTRING M (0 0 978307200,1 1 978393600)" SELECT ST_AsText((tgeompoint '{[Point(0 0)@2001-01-01, Point(1 1)@2001-01-02), [Point(1 1)@2001-01-03, Point(1 1)@2001-01-04), [Point(1 1)@2001-01-05, Point(0 0)@2001-01-06)}')::geometry); -- "MULTILINESTRING M ((0 0 978307200,1 1 978393600),(1 1 978480000,1 1 978566400), (1 1 978652800,0 0 978739200))" SELECT ST_AsText((tgeompoint '{[Point(0 0)@2001-01-01, Point(1 1)@2001-01-02), [Point(1 1)@2001-01-03], [Point(1 1)@2001-01-05, Point(0 0)@2001-01-06)}')::geometry); -- "GEOMETRYCOLLECTION M (LINESTRING M (0 0 978307200,1 1 978393600), POINT M (1 1 978480000),LINESTRING M (1 1 978652800,0 0 978739200))"
Cast a PostGIS trajectory to a temporal point
geometry::tgeompoint
geography::tgeogpoint
SELECT asText(geometry 'LINESTRING M (0 0 978307200,0 1 978393600, 1 1 978480000)'::tgeompoint); -- "[POINT(0 0)@2001-01-01, POINT(0 1)@2001-01-02, POINT(1 1)@2001-01-03]"; SELECT asText(geometry 'GEOMETRYCOLLECTION M (LINESTRING M (0 0 978307200,1 1 978393600), POINT M (1 1 978480000),LINESTRING M (1 1 978652800,0 0 978739200))'::tgeompoint); -- "{[POINT(0 0)@2001-01-01, POINT(1 1)@2001-01-02], [POINT(1 1)@2001-01-03], [POINT(1 1)@2001-01-05, POINT(0 0)@2001-01-06]}"