Edit– Code may be flawed, testing—– testing. Please wait to use… .

In PostGIS, ST_RotateZ has a couple forms: a version that rotates around 0,0 and a version that rotates around a point of origin:

geometry ST_Rotate(geometry geomA, float rotRadians, geometry pointOrigin)

ST_RotateX and ST_RotateY have had no equivalents– until now. These equivalents are dumb versions– they use a transformation/rotation/reverse-transformation to do their magic, which is maybe not as efficient as using ST_Affine, but I’m not smart enough for ST_Affine. Not today anyway. Repo here: https://github.com/smathermather/postgis-etc/tree/master/3D

geometry ST_RotateX(geometry geomA, float rotRadians, geometry pointOrigin)

ST_RotateX version:

-- Function: st_rotatex(geometry, double precision, geometry)
CREATE OR REPLACE FUNCTION ST_RotateX(geomA geometry, rotRadians double precision, pointOrigin geometry)
  RETURNS geometry AS
$BODY$

----- Transform geometry to nullsville (0,0,0) so rotRadians will take place around the pointOrigin
WITH transformed AS (
    SELECT ST_Translate(geomA, -1 * ST_X(pointOrigin), -1 * ST_Y(pointOrigin), -1 * ST_Z(pointOrigin)) AS the_geom
    ),
----- Rotate in place
rotated AS (
    SELECT ST_RotateX(the_geom, rotRadians) AS the_geom FROM transformed
    ),
----- Translate back home
rotTrans AS (
    SELECT ST_Translate(the_geom, ST_X(pointOrigin), ST_Y(pointOrigin), ST_Z(pointOrigin)) AS the_geom
	FROM rotated
    )
----- profit
SELECT the_geom from rotTrans

;

$BODY$
  LANGUAGE sql VOLATILE
  COST 100;

geometry ST_RotateY(geometry geomA, float rotRadians, geometry pointOrigin)

ST_RotateY version:

-- Function: ST_RotateY(geometry, double precision, geometry)
CREATE OR REPLACE FUNCTION ST_RotateY(geomA geometry, rotRadians double precision, pointOrigin geometry)
  RETURNS geometry AS
$BODY$

----- Transform geometry to nullsville (0,0,0) so rotRadians will take place around the pointOrigin
WITH transformed AS (
    SELECT ST_Translate(geomA, -1 * ST_X(pointOrigin), -1 * ST_Y(pointOrigin), -1 * ST_Z(pointOrigin)) AS the_geom
    ),
----- Rotate in place
rotated AS (
    SELECT ST_RotateY(the_geom, rotRadians) AS the_geom FROM transformed
    ),
----- Translate back home
rotTrans AS (
    SELECT ST_Translate(the_geom, ST_X(pointOrigin), ST_Y(pointOrigin), ST_Z(pointOrigin)) AS the_geom
	FROM rotated
    )
----- profit
SELECT the_geom from rotTrans

;

$BODY$
  LANGUAGE sql VOLATILE
  COST 100;