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

Applies a 3D affine transformation to a meshgeom or sfmesh object — rotate, translate, and scale in one 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 rotation and scaling coefficients of the 3×3 upper-left submatrix.
`xoff`	The x-axis translation offset.
`yoff`	The y-axis translation offset.
`zoff`	The z-axis translation offset.
`p`	A flat array of 16 values representing a 4×4 affine transformation matrix (row-major order).
`p2`	A second flat array of 16 values representing a 4×4 affine transformation matrix, used only in the two-array overload.

Description

ST_Affine builds a 4×4 transformation matrix and applies it to every vertex in the geometry.

Scalar form (parameters a–i and xoff/yoff/zoff):

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

Each vertex (x, y, z) is transformed to (x', y', z'):

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 (parameter p, a 16-element float8[]):

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

Each vertex is transformed as:

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]

Two-array overload ST_Affine(float8[] p, float8[] p2): multiplies the two 4×4 matrices and returns the resulting composed matrix as a float8[]. Use this to chain multiple transformations before applying them to geometry.

Examples

Rotate a meshgeom object 180 degrees around the z-axis (scalar form)

-- Rotate 180 degrees around the z-axis using cos(pi()) and sin(pi())
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)))))

Rotate a meshgeom object 180 degrees around the z-axis (array form)

The following example performs the same rotation using a float8[] array.

-- Equivalent rotation using the array form
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)))))

Compose two transformation matrices

The two-array overload multiplies two 4×4 transformation matrices and returns the composed matrix as a float8[]. You can then pass the result to ST_Affine to apply the combined transformation to geometry.

-- Multiply two transformation matrices to produce a composed 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}