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.



  • Would this do? Read comments within code.

    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>




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2