This discussion is archived
4 Replies Latest reply: Feb 14, 2013 4:57 AM by Pleiadian RSS

Convert multiline to simple line?

Pleiadian Journeyer
Currently Being Moderated
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

We are processing spatial data from another source to display in our GIS environment.

The data is a set of multilines. The gtype is 2006. A typical geometry looks like:
SDO_GTYPE     2006
SDO_SRID      31370
SDO_POINT.X   NULL     
SDO_POINT.Y   NULL
SDO_POINT.Z   NULL
SDO_ELEM_INFO (1,2,1, 7,2,1)
SDO_ORDINATES (105094.84, 195084.96,
               105094.54, 195080.22,
               105094.84, 195084.96,
               105094.84, 195084.96,
               105094.68, 195082.47 )
Now, this is not an actual multiline... it's just encoded as a multiline, but if you look at the coordinates you'll see that the end point of the first line is the same as the beginning of the second line (105094.84, 195084.96).

A better way to encode this geometry would be:
SDO_GTYPE     2002 <---
SDO_SRID      31370
SDO_POINT.X   NULL     
SDO_POINT.Y   NULL
SDO_POINT.Z   NULL
SDO_ELEM_INFO (1,2,1)
SDO_ORDINATES (105094.84, 195084.96,
               105094.54, 195080.22,
               105094.84, 195084.96, <---
               105094.68, 195082.47 )
Is there a standard function in Oracle that does this conversion for me? Or do I have to write my own :)

Thanks a lot!
Rob
  • 1. Re: Convert multiline to simple line?
    Pleiadian Journeyer
    Currently Being Moderated
    It looks like the function sdo_util.internal_merge_linestrings() does what I am looking for.
    This is an undocumented function, so I am reluctant to use it...
  • 2. Re: Convert multiline to simple line?
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Rob,

    I would have to be quite desperate to use an undocumented function ...

    Personally, I would make a copy of my data and try something like this:

    Update your geometries:
    UPDATE <tablename> T 
    SET 
        T.<geometry column name>.SDO_GTYPE = 2002,
        T.<geometry column name>.SDO_ELEM_INFO = SDO_ELEM_INFO_ARRAY(1,2,1);
    Then use [url http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_util.htm#BJEBHAJB]SDO_UTIL.REMOVE_DUPLICATE_VERTICES with the proper tolerance. Theoretically that should fix your data. I'm not sure if you need to drop your spatial index before you try something like this though, and I haven't tested this (that's why I'm saying to try this on a copy of your data ;-) ).

    So no, not a standard function, but with a bit of tweaking I think you should be able to succeed.

    HTH,
    Stefan
  • 3. Re: Convert multiline to simple line?
    Simon Greener Journeyer
    Currently Being Moderated
    Rob,

    Plenty of ways to skin this cat with the standard tools.
    with mLine as (
    select SDO_GEOMETRY(2006,31370,NULL,
    SDO_ELEM_INFO_Array (1,2,1, 7,2,1),
    SDO_ORDINATE_ARRAY (105094.84, 195084.96,
                   105094.54, 195080.22,
                   105094.84, 195084.96,
                   105094.84, 195084.96,
                   105094.68, 195082.47 )) as geom
     from dual
    )
    select sdo_util.concat_lines(l1.geom,l2.geom) as line
      from (select sdo_util.extract(geom,1) as geom from mline) l1, 
           (select sdo_util.extract(geom,2) as geom from mline) l2
    union all
    select sdo_geom.sdo_union(geom,geom,0.005) as line
      from mline l
    union all
    select sdo_util.remove_duplicate_vertices(sdo_geometry(2002,l.geom.sdo_srid,l.geom.sdo_point,SDO_ELEM_INFO_ARRAY(1,2,1),l.geom.sdo_ordinates),0.005) as line
      from mline l;
    -- Results
    -- 
    LINE
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------
    SDO_GEOMETRY(2002,31370,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(105094.84,195084.96, 105094.54,195080.22, 105094.84,195084.96, 105094.68,195082.47))
    SDO_GEOMETRY(2002,31370,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(105094.54,195080.22, 105094.68,195082.47, 105094.84,195084.96))
    SDO_GEOMETRY(2002,31370,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(105094.84,195084.96, 105094.54,195080.22, 105094.84,195084.96, 105094.68,195082.47))
    Note how SDO_UNION reorganizes and cleans up the linestring which you may not want.

    regards
    Simon
  • 4. Re: Convert multiline to simple line?
    Pleiadian Journeyer
    Currently Being Moderated
    Thanks guys!

    I have decided to go for sdo_union. It does exactly what I am looking for, it returns the simplest way to encode the geometry.

    It was right under my nose, I had tried sdo_union(geom,*null*, tol) but was not smart enough to try sdo_union(geom,*geom*, tol)!

    Rob

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points