# Remove third coordinate from string

• 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))
``````

• Coordinates 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 coordinate (the "M" coordinate) 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.

Sample data:

``````SQL> with cte as (
2  select 'LINESTRING ( 1.0 2.0, 3 4)'                               as txt from dual union all
3  select 'LINESTRING M ( 1 2 3, 4 5 6.0)'                           as txt from dual union all
4  select 'LINESTRING ( 1 2, 3 4, 5 6)'                              as txt from dual union all
5  select 'LINESTRING M ( 1 2 3, 4 5 6.0, 7 8.00 9.0)'               as txt from dual union all
6  select 'LINESTRING M ( 1 2 3.0, 4 5 6)'                           as txt from dual union all
7  select 'MULTILINESTRING M (( 1 2 3, 4 5 6),( 7 8 9, 10 11 12.0))' as txt from dual
8  ),
``````

Query begins here:

``````  9  temp as
10    -- LINE is the 1st part of the string ("letters")
11    -- SUB is the 2nd part of the string (coordinates)
12    (select
13      txt,
14      substr(txt, 1, instr(txt, '(') - 1) line,
15      substr(txt, instr(txt, '(')) sub
16     from cte
17    ),
18  temp2 as
19    -- split coordinates into rows; they are separated by a comma
20    (select txt,
21       sub,
22       line,
23       column_value as rn,
24       regexp_substr(sub, '[^,]+', 1, column_value) val
25     from temp cross join
26       table(cast(multiset(select level from dual
27                           connect by level ``````

Result:

``````TXT                                                      RESULT
-------------------------------------------------------- ----------------------------------------------------
LINESTRING ( 1.0 2.0, 3 4)                               LINESTRING ( 1.0 2.0, 3 4)
LINESTRING ( 1 2, 3 4, 5 6)                              LINESTRING ( 1 2, 3 4, 5 6)
LINESTRING M ( 1 2 3, 4 5 6.0)                           LINESTRING M ( 1 2 , 4 5 )
LINESTRING M ( 1 2 3.0, 4 5 6)                           LINESTRING M ( 1 2 , 4 5 )
LINESTRING M ( 1 2 3, 4 5 6.0, 7 8.00 9.0)               LINESTRING M ( 1 2 , 4 5 , 7 8.00 )
MULTILINESTRING M (( 1 2 3, 4 5 6),( 7 8 9, 10 11 12.0)) MULTILINESTRING M (( 1 2 , 4 5 ),( 7 8 , 10 11 ))
6 rows selected.
SQL>
``````

2

2

2

2

2

2

2

2

2

2

2

2

2

2

2