Redondear los valores de las coordenadas a un número de decimales
round(tspatial,integer=0) → tgeo
SELECT asText(round(tgeompoint '{Point(1.12345 1.12345 1.12345)@2001-01-01, Point(2 2 2)@2001-01-02, Point(1.12345 1.12345 1.12345)@2001-01-03}', 2)); /* {POINT Z (1.12 1.12 1.12)@2001-01-01, POINT Z (2 2 2)@2001-01-02, POINT Z (1.12 1.12 1.12)@2001-01-03} */ SELECT asText(round(tgeogpoint 'Point(1.12345 1.12345)@2001-01-01', 2)); -- POINT(1.12 1.12)@2001-01-01
Devuelve una matriz de fragmentos del punto temporal que son simples
makeSimple(tpoint) → tgeompoint[]
SELECT asText(makeSimple(tgeompoint '[Point(0 0)@2001-01-01, Point(1 1)@2001-01-02, Point(0 0)@2001-01-03]')); /* {"[POINT(0 0)@2001-01-01, POINT(1 1)@2001-01-02)", "[POINT(1 1)@2001-01-02, POINT(0 0)@2001-01-03]"} */ SELECT asText(makeSimple(tgeompoint '[Point(0 0 0)@2001-01-01, Point(1 1 1)@2001-01-02, Point(2 0 2)@2001-01-03, Point(0 0 0)@2001-01-04]')); /* {"[POINT Z (0 0 0)@2001-01-01, POINT Z (1 1 1)@2001-01-02, POINT Z (2 0 2)@2001-01-03, POINT Z (0 0 0)@2001-01-04]"} */ SELECT asText(makeSimple(tgeompoint '[Point(0 0)@2001-01-01, Point(1 1)@2001-01-02, Point(0 1)@2001-01-03, Point(1 0)@2001-01-04]')); /* {"[POINT(0 0)@2001-01-01, POINT(1 1)@2001-01-02, POINT(0 1)@2001-01-03)", "[POINT(0 1)@2001-01-03, POINT(1 0)@2001-01-04]"} */ SELECT asText(makeSimple(tgeompoint '{[Point(0 0 0)@2001-01-01, Point(1 1 1)@2001-01-02], [Point(1 1 1)@2001-01-03, Point(0 0 0)@2001-01-04]}')); /* {"{[POINT Z (0 0 0)@2001-01-01, POINT Z (1 1 1)@2001-01-02], [POINT Z (1 1 1)@2001-01-03, POINT Z (0 0 0)@2001-01-04]}"} */
Construir una geometría/geografía con medida M a partir de un punto temporal y un número flotante temporal
geoMeasure(tpoint,tfloat,segmentize=false) → geo
El último argumento segmentize
establece si el valor resultado ya sea es un Linestring M
o un MultiLinestring M
donde cada componente es un segmento de dos puntos.
SELECT ST_AsText(geoMeasure(tgeompoint '{Point(1 1 1)@2001-01-01, Point(2 2 2)@2001-01-02}', '{5@2001-01-01, 5@2001-01-02}')); -- MULTIPOINT ZM (1 1 1 5,2 2 2 5) SELECT ST_AsText(geoMeasure(tgeogpoint '{[Point(1 1)@2001-01-01, Point(2 2)@2001-01-02], [Point(1 1)@2001-01-03, Point(1 1)@2001-01-04]}', '{[5@2001-01-01, 5@2001-01-02],[7@2001-01-03, 7@2001-01-04]}')); -- GEOMETRYCOLLECTION M (POINT M (1 1 7),LINESTRING M (1 1 5,2 2 5)) SELECT ST_AsText(geoMeasure(tgeompoint '[Point(1 1)@2001-01-01, Point(2 2)@2001-01-02, Point(1 1)@2001-01-03]', '[5@2001-01-01, 7@2001-01-02, 5@2001-01-03]', true)); -- MULTILINESTRING M ((1 1 5,2 2 5),(2 2 7,1 1 7))
Una visualización típica de los datos de movilidad es mostrar en un mapa la trayectoria del objeto móvil utilizando diferentes colores según la velocidad. La Figure 7.1, “Visualización de la velocidad de un objeto móvil usando una rampa de color en QGIS.” muestra el resultado de la consulta a continuación usando una rampa de color en QGIS.
WITH Temp(t) AS ( SELECT tgeompoint '[Point(0 0)@2001-01-01, Point(1 1)@2001-01-05, Point(2 0)@2001-01-08, Point(3 1)@2001-01-10, Point(4 0)@2001-01-11]' ) SELECT ST_AsText(geoMeasure(t, round(speed(t) * 3600 * 24, 2), true)) FROM Temp; /* MULTILINESTRING M ((0 0 0.35,1 1 0.35),(1 1 0.47,2 0 0.47),(2 0 0.71,3 1 0.71), (3 1 1.41,4 0 1.41)) */
La siguiente expresión se usa en QGIS para lograr esto. La función scale_linear
transforma el valor M de cada segmento componente al rango [0, 1]. Este valor luego se pasa a la función ramp_color
.
ramp_color( 'RdYlBu', scale_linear( m(start_point(geometry_n($geometry,@geometry_part_num))), 0, 2, 0, 1) )
Devuelve la transformación afín 3D de una geometría temporal para hacer cosas como trasladar, rotar y escalar en un solo paso
affine(tgeo,float a, float b, float c, float d, float e, float f, float g,
float h, float i, float xoff, float yoff, float zoff) → tgeo
affine(tgeo,float a, float b, float d, float e, float xoff, float yoff) → tgeo
-- Rotate a 3D temporal point 180 degrees about the z axis SELECT asEWKT(affine(temp, cos(pi()), -sin(pi()), 0, sin(pi()), cos(pi()), 0, 0, 0, 1, 0, 0, 0)) FROM (SELECT tgeompoint '[POINT(1 2 3)@2001-01-01, POINT(1 4 3)@2001-01-02]' AS temp) t; -- [POINT Z (-1 -2 3)@2001-01-01, POINT Z (-1 -4 3)@2001-01-02] SELECT asEWKT(rotate(temp, pi())) FROM (SELECT tgeompoint '[POINT(1 2 3)@2001-01-01, POINT(1 4 3)@2001-01-02]' AS temp) t; -- [POINT Z (-1 -2 3)@2001-01-01, POINT Z (-1 -4 3)@2001-01-02] -- Rotate a 3D temporal point 180 degrees in both the x and z axis SELECT asEWKT(affine(temp, cos(pi()), -sin(pi()), 0, sin(pi()), cos(pi()), -sin(pi()), 0, sin(pi()), cos(pi()), 0, 0, 0)) FROM (SELECT tgeometry '[Point(1 1)@2001-01-01, Linestring(1 1,2 2)@2001-01-02]' AS temp) t; -- [POINT(-1 -1)@2001-01-01, LINESTRING(-1 -1,-2 -2)@2001-01-02]
Devuelve el punto temporal rotado en sentido antihorario sobre el punto de origen
rotate(tgeo, float radians) → tgeo
rotate(tgeo, float radians, float x0, float y0) → tgeo
rotate(tgeo, float radians, geometry pointOrigin) → tgeo
-- Rotate a temporal point 180 degrees SELECT asEWKT(rotate(tgeompoint '[Point(5 10)@2001-01-01, Point(5 5)@2001-01-02, Point(10 5)@2001-01-03]', pi()), 6); -- [POINT(-5 -10)@2001-01-01, POINT(-5 -5)@2001-01-02, POINT(-10 -5)@2001-01-03] -- Rotate 30 degrees counter-clockwise at x=5, y=10 SELECT asEWKT(rotate(tgeompoint '[Point(5 10)@2001-01-01, Point(5 5)@2001-01-02, Point(10 5)@2001-01-03]', pi()/6, 5, 10), 6); -- [POINT(5 10)@2001-01-01, POINT(7.5 5.67)@2001-01-02, POINT(11.83 8.17)@2001-01-03] -- Rotate 60 degrees clockwise from centroid SELECT asEWKT(rotate(temp, -pi()/3, ST_Centroid(traversedArea(temp))), 2) FROM (SELECT tgeometry '[Point(5 10)@2001-01-01, Point(5 5)@2001-01-02, Linestring(5 5,10 5)@2001-01-03]' AS temp) AS t; /* [POINT(10.58 9.67)@2001-01-01, POINT(6.25 7.17)@2001-01-02, LINESTRING(6.25 7.17,8.75 2.83)@2001-01-03] */
Devuelve un punto temporal escalado por factores dados
scale(tgeo,float Xfactor,float Yfactor,float Zfactor) → tgeo
scale(tgeo,float Xfactor,float Yfactor) → tgeo
scale(tgeo,geometry factor) → tgeo
scale(tgeo,geometry factor,geometry origin) → tgeo
SELECT asEWKT(scale(tgeompoint '[Point(1 2 3)@2001-01-01, Point(1 1 1)@2001-01-02]', 0.5, 0.75, 0.8)); -- [POINT Z (0.5 1.5 2.4)@2001-01-01, POINT Z (0.5 0.75 0.8)@2001-01-02] SELECT asEWKT(scale(tgeompoint '[Point(1 2 3)@2001-01-01, Point(1 1 1)@2001-01-02]', 0.5, 0.75)); -- [POINT Z (0.5 1.5 3)@2001-01-01, POINT Z (0.5 0.75 1)@2001-01-02] SELECT asEWKT(scale(tgeompoint '[Point(1 2 3)@2001-01-01, Point(1 1 1)@2001-01-02]', geometry 'Point(0.5 0.75 0.8)')); -- [POINT Z (0.5 1.5 2.4)@2001-01-01, POINT Z (0.5 0.75 0.8)@2001-01-02] SELECT asEWKT(scale(tgeometry '[Point(1 1)@2001-01-01, Linestring(1 1,2 2)@2001-01-02]', geometry 'Point(2 2)', geometry 'Point(1 1)')); -- [POINT(1 1)@2001-01-01, LINESTRING(1 1,3 3)@2001-01-02]
Transformar un punto geométrico temporal en el espacio de coordenadas de un Mapbox Vector Tile
asMVTGeom(tpoint,bounds,extent=4096,buffer=256,clip=true) → (geom,times)
El resultado es un par compuesto de un valor geometry
y una matriz de valores de marca de tiempo asociados codificados como época de Unix. Los parámetros son los siguientes:
tpoint
es el punto temporal para transformar
bounds
es un stbox
que define los límites geométricos del contenido del mosaico sin búfer
extent
es la extensión del mosaico en el espacio de coordenadas del mosaico
buffer
es la distancia del búfer en el espacio de coordenadas de mosaico
clip
es un booleano que determina si las geometrías resultantes y las marcas de tiempo deben recortarse o no
SELECT ST_AsText((mvt).geom), (mvt).times FROM (SELECT asMVTGeom(tgeompoint '[Point(0 0)@2001-01-01, Point(100 100)@2001-01-02]', stbox 'STBOX X((40,40),(60,60))') AS mvt ) AS t; -- LINESTRING(-256 4352,4352 -256) | {946714680,946734120} SELECT ST_AsText((mvt).geom), (mvt).times FROM (SELECT asMVTGeom(tgeompoint '[Point(0 0)@2001-01-01, Point(100 100)@2001-01-02]', stbox 'STBOX X((40,40),(60,60))', clip:=false) AS mvt ) AS t; -- LINESTRING(-8192 12288,12288 -8192) | {946681200,946767600}