Oracle Database Discussions

Hi,

We have a client using Spatial where they sometimes retrieve geometries (typically GPS trackings) where there are “zero length linestrings”, ie. linestrings with identical start and end points, so they are in reality points. Very simple example: 'MULTILINESTRING ((2 2, 3 3, 4 4), (5 5, 5 5))' - the second linestring is actually a point.

Works fine in Oracle, but they need to send it (as WKT) to a third party, whose software cannot handle these “pseudo points”. So they are looking for a way to remove such zero length linestrings (for their purposes, these pseudo points can safely be ignored and bring no value to the data.)

As there is not anything wrong as such, sdo_util.simplify or sdo_util.rectify does not help.

I used sdo_geom.sdo_self_union, which turns these zero length linestrings into actual points, which is actually more correct as such:

with t as (
   select sdo_util.from_wktgeometry('MULTILINESTRING ((2 2, 3 3, 4 4), (5 5, 5 5))') as geom from dual
)

select
   sdo_util.to_wktgeometry(
      sdo_geom.sdo_self_union(t.geom, 0.0000000005)
   ) as de_duped_wkt
from t
;

DE_DUPED_WKT                                                                |
----------------------------------------------------------------------------+
GEOMETRYCOLLECTION (LINESTRING (2.0 2.0, 3.0 3.0, 4.0 4.0), POINT (5.0 5.0))|

The problem now (unfortunately) is that the third party they are delivering data to also cannot handle geometrycollection :-(

As these points do not bring them value and they just want to get rid of them, I'm looking for a good way to simply remove the points after using sdo_self_union, leaving just the linestrings.

I've managed to get a working solution:

with t as (
--   select sdo_util.from_wktgeometry('MULTILINESTRING ((8 9, 8 9))') as geom from dual
--   union all
--   select sdo_util.from_wktgeometry('MULTILINESTRING ((8 9, 8 9), (1 2, 1 2))') as geom from dual
--   union all
   select sdo_util.from_wktgeometry('MULTILINESTRING ((2 3, 4 5, 6 7), (8 9, 8 9))') as geom from dual
   union all
   select sdo_util.from_wktgeometry('MULTILINESTRING ((2 3, 4 5, 6 7), (8 9, 8 9), (7 6, 5 4, 3 2))') as geom from dual
   union all
   select sdo_util.from_wktgeometry('MULTILINESTRING ((8 9, 8 9), (2 3, 4 5, 6 7))') as geom from dual
   union all
   select sdo_util.from_wktgeometry('MULTILINESTRING ((1 2, 1 2), (2 3, 4 5, 6 7), (8 9, 8 9))') as geom from dual
)
select
   sdo_util.to_wktgeometry(geom_no_points) g
from (
   select
      sdo_geom.sdo_self_union(
         sdo_util.from_wktgeometry(
            regexp_replace(
               regexp_replace(
                  sdo_util.to_wktgeometry(
                     sdo_geom.sdo_self_union(t.geom, 0.0000000005)
                  )
                , '(, )?POINT \(\d+\.?\d* \d+\.?\d*\)'
               )
             , '(, )?MULTIPOINT \((\(\d+\.?\d* \d+\.?\d*\))(, \(\d+\.?\d* \d+\.?\d*\))*\)'
            )
         )
       , 0.000000005
      ) as geom_no_points
   from t
)
;

It works in this way:

• First do the sdo_self_union to turn the zero length linestrings into points.
• Then convert it to WKT.
• Then use regexp_replace to remove all POINT and MULTIPOINT entries in the WKT.
• Then convert back to a spatial object.
• Finally run sdo_self_union again to get rid of the geometrycollection.

(The first two sample spatial objects in the above is commented out, as the solution fails if an input geometry contains only zero length linestrings - but in the real situation this is never the case, so that can safely be ignored.)

Our client has confirmed this works, even on their much larger geometry objects.

But I myself am slightly unhappy about such a “cludgy” work-around (my own opinion) of having to do regular expressions on a textual representation of the spatial object. (Yes, I'm nerdy ;-)

Does anyone know of a method to remove any POINT child objects using a supported SDO functionality?

(Just curious if I can make a “better” solution than the above ;-)