Transformaciones

Transformaciones
Anterior	Capítulo 8. Tipos temporales: Operaciones espaciales	Siguiente

Redondear los valores de las coordenadas a un número de decimales

round(tpoint,integer=0) → tpoint

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 Figura 8.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)
	)

Figura 8.1. Visualización de la velocidad de un objeto móvil usando una rampa de color en QGIS.

Devuelve la transformación afín 3D de un punto temporal para hacer cosas como trasladar, rotar y escalar en un solo paso

affine(tpoint,float a, float b, float c, float d, float e, float f, float g,

float h, float i, float xoff, float yoff, float zoff) → tpoint

affine(tpoint,float a, float b, float d, float e, float xoff, float yoff) → tpoint

-- 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 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]

Devuelve el punto temporal rotado en sentido antihorario sobre el punto de origen

rotate(tpoint, float radians) → tpoint

rotate(tpoint, float radians, float x0, float y0) → tpoint

rotate(tpoint, float radians, geometry pointOrigin) → tpoint

-- Rotate a temporal point 180 degrees
SELECT asEWKT(rotate(tgeompoint '[POINT(50 160)@2001-01-01, POINT(50 50)@2001-01-02, 
  POINT(100 50)@2001-01-03]', pi()), 6);
-- [POINT(-50 -160)@2001-01-01, POINT(-50 -50)@2001-01-02, POINT(-100 -50)@2001-01-03]
-- Rotate 30 degrees counter-clockwise at x=50, y=160
SELECT asEWKT(rotate(tgeompoint '[POINT(50 160)@2001-01-01, POINT(50 50)@2001-01-02, 
  POINT(100 50)@2001-01-03]', pi()/6, 50, 160), 6);
/* [POINT(50 160)@2001-01-01, POINT(105 64.737206)@2001-01-02, 
    POINT(148.30127 89.737206)@2001-01-03] */
-- Rotate 60 degrees clockwise from centroid
SELECT asEWKT(rotate(temp, -pi()/3, ST_Centroid(trajectory(temp))), 6)
FROM (SELECT tgeompoint '[POINT(50 160)@2001-01-01, POINT(50 50)@2001-01-02, 
  POINT(100 50)@2001-01-03]' AS temp) AS t;
/* [POINT(116.422459 130.672073)@2001-01-01, POINT(21.159664 75.672073)@2001-01-02, 
    POINT(46.159664 32.370803)@2001-01-03] */

Devuelve un punto temporal escalado por factores dados

scale(tpoint,float Xfactor,float Yfactor,float Zfactor) → tpoint

scale(tpoint,float Xfactor,float Yfactor) → tpoint

scale(tpoint,geometry factor) → tpoint

scale(tpoint,geometry factor,geometry origin) → tpoint

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(0.5 1.5 2.4,0.5 0.75 0.8)
SELECT asEWKT(scale(tgeompoint '[Point(1 2 3)@2001-01-01, Point(1 1 1)@2001-01-02]', 
  0.5, 0.75));
-- Point(0.5 1.5 3,0.5 0.75 1)
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(0.5 1.5 2.4,0.5 0.75 0.8)
SELECT asEWKT(scale(tgeompoint '[Point(1 1)@2001-01-01, Point(2 2)@2001-01-02]', 
  geometry 'POINT(2 2)', geometry 'POINT(1 1)'));
--  Point(1 1,3 3)

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}
```

Anterior	Subir	Siguiente
Accessores	Inicio	Operaciones de distancia