0

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.

nali
  • 481
  • 1
  • 3
  • 16
  • 1
    Where are stored the point in input? in postgis table? in an external files? – ScaisEdge Feb 27 '19 at 08:33
  • In MATLAB, I pass them as a query using the Database Toolbox. – nali Feb 27 '19 at 08:46
  • 1
    this mean that you have point geometry .. ? and in which SR are stored in Matlab .. ? which coordinate have the point in matlab? – ScaisEdge Feb 27 '19 at 08:48
  • Yes, I have 3D coordinates in MATLAB with with various SRIDs which I want to transform in various SRIDs as well. – nali Feb 27 '19 at 08:53
  • 1
    So you have a different SRID for each of the ca. 100k points? And want each to be transformed in a different SRID? Why do you import it as `Multipoint` from Matlab? – Humpelstielzchen Feb 27 '19 at 08:59
  • @Humpelstielzchen: Not in that sense. All source-points are in the same coordinate system (SRID_FROM). They all should be transferred into the other coordinate system (SRID_TO). – nali Feb 27 '19 at 14:34

1 Answers1

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?

Humpelstielzchen
  • 6,126
  • 3
  • 14
  • 34
  • The answer is simple. I have no idea how to avoid a for loop where I transform a single coordinate. I want to transform them as one statement and have a return as two columns (lon,lat) as you did it. What would be the better way to achieve this? Your answer already simplifies my query. I'll check it soon. – nali Feb 28 '19 at 10:16
  • 1
    Hm, maybe I don't really understand the problem. My answer transforms all your coordinates in one statement and returns lat an lon. What is missing? – Humpelstielzchen Feb 28 '19 at 10:53
  • Congratulations you've managed to reduce the query time from 47s to 0.35s by a factor of 134! Thank you very much for your help. – nali Mar 07 '19 at 08:43
  • Awesome! Glad to help! – Humpelstielzchen Mar 07 '19 at 08:43