Use ST_Affine to apply affine transformation on meshgeom and sfmesh - PolarDB

Apply a 3D affine transformation to a meshgeom or sfmesh object to translate, rotate, or scale it in a single step.

Syntax

meshgeom ST_Affine(meshgeom geom, float a, float b, float c, float d, float e, float f, float g, float h, float i, float xoff, float yoff, float zoff)
meshgeom ST_Affine(meshgeom geom, float[] p)
sfmesh ST_Affine(sfmesh sfmesh, float a, float b, float c, float d, float e, float f, float g, float h, float i, float xoff, float yoff, float zoff)
sfmesh ST_Affine(sfmesh sfmesh, float[] p)
sfmesh ST_Affine(float8[] p, float8[] p2)

Parameters

Parameter	Description
`geom`	The meshgeom object to transform.
`sfmesh`	The sfmesh object to transform.
`a`–`i`	The values of the parameters.
`xoff`	The translation offset along the x-axis.
`yoff`	The translation offset along the y-axis.
`zoff`	The translation offset along the z-axis.
`p`	A 16-element float array representing the full 4x4 affine transformation matrix.
`p2`	A second 16-element float array. Used with the `sfmesh ST_Affine(float8[] p, float8[] p2)` signature to combine two transformation matrices.

Description

ST_Affine constructs a 4x4 affine transformation matrix and applies it to every vertex of the input geometry. Use it to perform translation, rotation, scaling, or any combination of these in one call.

Named-parameter form — the matrix is:

/ a  b  c  xoff \
| d  e  f  yoff |
| g  h  i  zoff |
\ 0  0  0   1   /

Each output coordinate is computed as:

x' = a*x + b*y + c*z + xoff
y' = d*x + e*y + f*z + yoff
z' = g*x + h*y + i*z + zoff

Array form — pass a 16-element array p instead of individual coefficients. The matrix maps as:

/ p[1]  p[2]  p[3]  p[4]  \
| p[5]  p[6]  p[7]  p[8]  |
| p[9]  p[10] p[11] p[12] |
\ p[13] p[14] p[15] p[16] /

The coordinate transformation follows the same formula with p[n] substituted for the corresponding named parameter:

x' = p[1]*x + p[2]*y + p[3]*z + p[4]
y' = p[5]*x + p[6]*y + p[7]*z + p[8]
z' = p[9]*x + p[10]*y + p[11]*z + p[12]

Examples

Example 1: Rotate a meshgeom 180 degrees around the z-axis (named parameters)

cos(pi()) = -1 and sin(pi()) = 0, so the matrix reduces to a 180-degree rotation. The x and y coordinates are negated; z is unchanged.

SELECT ST_AsText(
  ST_Affine(
    'MESHGEOM(PATCH(INDEXSURFACE(VERTEX(0 0,0 10,10 10,10 0), INDEX((0,1,2),(1,2,3)))))'::meshgeom,
    cos(pi()), -sin(pi()), 0,
    sin(pi()),  cos(pi()), 0,
    0,          0,         1,
    0, 0, 0
  )
);

Result:

MESHGEOM(PATCH(INDEXSURFACE(VERTEX(0 0,0 -10,-10 -10,-10 0),INDEX((0,1,2),(1,2,3)))))

Example 2: Same rotation using a float array

Pass the same 180-degree z-axis rotation as a 16-element float8[] array instead of named parameters.

SELECT ST_AsText(
  ST_Affine(
    'MESHGEOM(PATCH(INDEXSURFACE(VERTEX(0 0,0 10,10 10,10 0), INDEX((0,1,2),(1,2,3)))))'::meshgeom,
    ARRAY[
      cos(pi()), -sin(pi()), 0, 0,
      sin(pi()),  cos(pi()), 0, 0,
      0,          0,         1, 0,
      0,          0,         0, 0
    ]::float8[]
  )
);

Result:

MESHGEOM(PATCH(INDEXSURFACE(VERTEX(0 0,0 -10,-10 -10,-10 0),INDEX((0,1,2),(1,2,3)))))

Example 3: Combine two sfmesh transformation matrices

Pass two float8[] matrices to compute their combined transformation.

SELECT st_affine(
  '{-0.3583679495453059,-0.9335804264971996,0,72.60910593620417,0.9335804264971996,-0.3583679495453059,0,82.98262879309624,0,0,1,96.52,0,0,0,1}'::float8[],
  '{-0.3583679495453059,-0.9335804264971996,0,63.25570597150741,0.9335804264971996,-0.3583679495453059,0,107.3490699956786,0,0,1,87.92,0,0,0,1}'::float8[]
);

Result:

{-0.743144825477386,0.669130606358867,0,-50.2787022604891,-0.669130606358867,-0.743144825477386,0,103.566451652411,0,0,1,184.44,0,0,0,1}