Un valor temporal se puede convertir en un tipo compatible usando la notación CAST(ttype1 AS ttype2) o ttype1::ttype2.
Convertir un valor temporal a un período
ttype::period
SELECT tint '[1@2001-01-01, 2@2001-01-03]'::period; -- "[2001-01-01, 2001-01-03]" SELECT ttext '(A@2000-01-01, B@2000-01-03, C@2000-01-05]'::period; -- "(2000-01-01, 2000-01-05]"
Convertir un número temporal a un rango
tnumber::range
SELECT tint '[1@2001-01-01, 2@2001-01-03]'::intrange; -- "[1, 3)" SELECT tfloat '(1@2000-01-01, 3@2000-01-03, 2@2000-01-05]'::floatrange; -- "(1, 3]" SELECT tfloat 'Interp=Stepwise;(1@2000-01-01, 3@2000-01-03, 2@2000-01-05]'::floatrange; -- "[1, 3]"
Convertir un número temporal number a un tbox
tnumber::tbox
SELECT tint '[1@2001-01-01, 2@2001-01-03]'::tbox; -- "TBOX((1,2001-01-01 00:00:00+01),(2,2001-01-03 00:00:00+01))" SELECT tfloat '(1@2000-01-01, 3@2000-01-03, 2@2000-01-05]'::tbox; -- "TBOX((1,2000-01-01 00:00:00+01),(3,2000-01-05 00:00:00+01))"
Convertir un punto temporal a un stbox
tpoint::stbox
SELECT tgeompoint '[Point(1 1)@2001-01-01, Point(3 3)@2001-01-03]'::stbox; -- STBOX T((1,1,2001-01-01),(3,3,2001-01-03)) SELECT SELECT tgeogpoint '[Point(1 1)@2001-01-01, Point(3 3)@2001-01-03]'::stbox;; -- "SRID=4326;GEODSTBOX T ((0.9972609281539917,0.017449747771024704,0.01745240643728351,2001-01-01), (0.9996954202651978,0.05226423218846321,0.05233595624294383,2001-01-03))"
Convertir un entero temporal en un flotante temporal
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, 2@2000-01-03, 3@2000-01-05]"
Convertir un flotante temporal en un entero temporal
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
Convertir un punto geométrico temporal en un punto geográfico temporal
tgeompoint::tgeogpoint
SELECT asText((tgeogpoint 'Point(0 0)@2001-01-01')::tgeompoint);
-- "{POINT(0 0)@2001-01-01}"
Convertir un punto geográfico temporal en un punto geométrico temporal
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)}"
Una forma común de almacenar puntos temporales en PostGIS es representarlos como geometrías de tipo LINESTRING M y utilizar la dimensión M para codificar marcas de tiempo como segundos desde 1970-01-01 00:00:00. Estas geometrías aumentadas con tiempo, llamadas trayectorias, se pueden validar con la función ST_IsValidTrajectory para verificar quel el valor M está creciendo de cada vértice al siguiente. Las trayectorias se pueden manipular con las funciones ST_ClosestPointOfApproach, ST_DistanceCPA y ST_CPAWithin. Los valores de puntos temporales se pueden convertir a/desde trayectorias de PostGIS.
Convertir un punto temporal en una trayectoria PostGIS
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))"
Convertir una trayectoria PostGIS a un punto temporal
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]}"