Remove third ordinate from string of 3 ordinates

Question

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.

Answer 1

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 <= regexp_count(sub, ',') + 1
 28                          ) as sys.odcinumberlist))
 29    )
 30  -- finally, using REGEXP_REPLACE remove the 3rd coordinate (if any); aggregate
 31  -- remaining coordinates back (using LISTAGG) and add the LINE prefix to
 32  -- compose the final result
 33  select txt,
 34    line || listagg(regexp_replace(val, '\d+(\.\d+)?', '', 1, 3), ',')
 35      within group (order by rn) result
 36  from temp2
 37  group by txt, line;

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>

Answer 2

There is a far easier way of doing this than the answer here suggests. A single regular expression (regex) can do it all and replace ~ 20 lines of complicated non-standard SQL in a single line with a (relatively) standard regex construct.

Here is a particularly good site on regexes (there are many) and there is a "quick start" here. All of the code below is to be found on the fiddle here:

CREATE TABLE test
(
  txt VARCHAR (200)
);

and populate it:

INSERT INTO test
  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;

And then we run:

SELECT
  txt,
  REGEXP_REPLACE(txt, '(\d+\.{0,1}0* \d+\.{0,1}0{0,3}) \d+\.{0,1}0{0,3}', '\1') AS res
FROM
  test;

Result:

TXT                                                         RES
LINESTRING ( 1.0 2.0, 3 4)                                  LINESTRING ( 1.0 2.0, 3 4)
LINESTRING M ( 1 2 3, 4 5 6.0)                              LINESTRING M ( 1 2, 4 5)
LINESTRING ( 1 2, 3 4, 5 6)                                 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, 4 5, 7 8.00)
LINESTRING M ( 1 2 3.0, 4 5 6)                              LINESTRING M ( 1 2, 4 5)
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

The REGEXP_REPLACE() function (also see manual) - it's basic structure is

REGEXP_REPLACE( string, pattern [, replacement_string [, start_position [, nth_appearance [, match_parameter ] ] ] ] )

Explanation of regex:

I would suggest that you read up a bit on regexes (tutorial here) - good discussion on how far they should be taken here. The discussion is about valid emails, but can be applied to a regex for anything - a case in point can be found here - the regex to validate emails is 6000 characters long! Life's too short!.

The regex (aka the "pattern"):

 (\d+\.{0,1}0* \d+\.{0,1}0{0,3}) \d+\.{0,1}0{0,3}

• (\d+\.{0,1}0* :

  • So, this starts with a ( - this creates a capturing group - we'll see this in use a bit later,

  • \d+ - the backslash (\) is a metacharacter - i.e. it is a special character for regexes - \d means a digit [0-9] (you'll find a list of these metacharacters on any regex cheat-sheet) and the plus sign (+) signifies the preceding character 1 or more times. This will therefore pick up 1, 2, 3... or 45, 34... or any integer.

  • \. - we wish to match integers such as 8.00, so we have to match a decimal point (.). However, decimal points (full stops, dot or periods) are also, you've guessed it, regex metacharacters - so we require a backslash again!

  • {0,1} - now, an integer may or may not be followed by a decimal point, so we put a quantifier after the literal decimal point of {0,1}, so what this is saying is match the point 0 or 1 time, but no more than once - which wouldn't make sense!

  • 0* - if the decimal point is in place, it also may or may not be followed by zeros (0). The asterisk (* - aka star) is (another!) metacharacter which means look for repeats of the preceding zero character 0 or more times.

  • - finally, a space - in technical terms a "space literal". This correspondes to the space between the first and second integers.

• \d+\.{0,1}0{0,3}) - this is the same as the one above except for the 0{0,3} (instead of 0*) which means (as explained above) zero to 3 repetitions of the 0 after the decimal point - just to show the potential differences and the potential power(^*) of regexes.

  ^{* - or their capacity to induce confusion, not to say torpor!}

  • ) - then there's the closing parenthesis ()) which ends the capturing group.

• \d+\.{0,1}0{0,3} - the last part of the regex - and not part of the capturing group. A space literal (this time between 2nd and 3rd integers), followed by another integer, with or without a trailing decimal point, followed by 0 up to 3 zeros.

Then we come to the replacement - it's simply \1 which is a reference to the first capturing group - i.e. replace all of the regex with just the capturing group - i.e. truncate the string as desired - i.e. replace the groups of three integers with groups of two as the question requires.

Many swear by regexes, many swear at them! I'm in neither camp (or maybe both :-) ). They can provide a good quick solution to many problems that would require a lot of lines of code otherwise - but see the discussion on email regexes mentioned above - like any tool, they have their uses, but there may be times when another solution may be optimal, even if regexes can do the job, doesn't meant that they're the best tool for the job!