How to transform an point cloud at once in PostgreSQL with PostGIS

Question

I have an pointcloud which has an certain SRID. Now I want to transform the whole pointcloud using an select query into an different SRID (here: 4326 lat, lon). My first super inefficient approach is:

SELECT 
ST_X
( 
    ST_GeometryN( p , n ) 
) as lon , 
ST_Y
( 
    ST_GeometryN( p , n ) 
) as lat 
FROM 
ST_Ttransform
( 
    ST_SetSRID
    ( 
        ST_GeomFromText
        ( 
            'MULTIPOINT
            ( 
                10.0 20.0 30.0 , 40.0 50.0 60.0 , 70.0 80.0 90 
            )' -- three example 3d coordinates
        ) , 
        SRID_FROM -- current pointcloud srid
    ) , 
    SRID_TO -- desired pointcloud srid
) 
AS p 
CROSS JOIN 
generate_series
( 
    1 , 
    ST_NumGeometries( p ) 
) n

Is there any better way to achieve this tranformation? I need to transform approximately 10k - 100k points at once.

Answer 1

I hope thats what you are aiming for. I get at least the same result.

I chose Pseudo Mercator (EPSG:3857) and transformed it to WGS84 (EPSG:4326)

SELECT ST_X(p) as lon , ST_Y(p) as lat 
FROM 
(SELECT (ST_DumpPoints(
                       ST_Transform(
                                    ST_GeomFromText( 
       'MULTIPOINT(10.0 20.0 30.0 , 40.0 50.0 60.0 , 70.0 80.0 90)', 3857) , 4326))).geom as p) gtab


         lon          |         lat
----------------------+----------------------
 8.98315284119521e-05 | 0.000179663056819876
 0.000359326113647809 | 0.000449157642049691
 0.000628820698883665 | 0.000718652227279505
(3 Zeilen)

But the question remains: Why import it as MULTIPOINT?