Sample Header Ad - 728x90

Remove third ordinate from string of 3 ordinates

1 vote
2 answers
152 views
I have geometries represented as strings (Oracle 18c): with cte as ( select 'LINESTRING ( 1.0 2.0, 3 4)' as txt from dual union all select 'LINESTRING M ( 1 2 3, 4 5 6.0)' as txt from dual union all select 'LINESTRING ( 1 2, 3 4, 5 6)' as txt from dual union all select 'LINESTRING M ( 1 2 3, 4 5 6.0, 7 8.00 9.0)' as txt from dual union all select 'LINESTRING M ( 1 2 3.0, 4 5 6)' as txt from dual union all select 'MULTILINESTRING M (( 1 2 3, 4 5 6),( 7 8 9, 10 11 12.0))' as txt from dual ) select txt from cte TXT -------------------------------------------------------- LINESTRING ( 1.0 2.0, 3 4) LINESTRING M ( 1 2 3, 4 5 6.0) LINESTRING ( 1 2, 3 4, 5 6) LINESTRING M ( 1 2 3, 4 5 6.0, 7 8.00 9.0) LINESTRING M ( 1 2 3.0, 4 5 6) MULTILINESTRING M (( 1 2 3, 4 5 6),( 7 8 9, 10 11 12.0)) - Ordinates are separated by spaces (X Y M). - Vertices are separated by commas. - Groups/multi-parts are wrapped in brackets and separated by commas. --------------- Using SQL, is there a way to remove the third ordinate (the "M" ordinate) from each vertex? Result: TXT -------------------------------------------------------- LINESTRING ( 1.0 2.0, 3 4) LINESTRING M ( 1 2, 4 5) LINESTRING ( 1 2, 3 4, 5 6) LINESTRING M ( 1 2, 4 5, 7 8.00) LINESTRING M ( 1 2, 4 5) MULTILINESTRING M (( 1 2, 4 5),( 7 8, 10 11)) It would be ok to round out the .0s. Or not.
Asked by User1974 (1527 rep)
May 20, 2022, 06:31 PM
Last activity: Jun 30, 2022, 07:54 PM