4 Replies Latest reply: Feb 14, 2013 6:57 AM by Pleiadian RSS

    Convert multiline to simple line?

    Pleiadian
      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
          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
            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
              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
                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