This discussion is archived
4 Replies Latest reply: Aug 28, 2013 12:59 AM by Paul Dziemiela RSS

Convert Oracle Spatial heterogenous collection (2004) to multiline string (2006)

1010855 Newbie
Currently Being Moderated

Hi,

 

 

I have all the SDO geometries in 2004 (heterogeneous collection) gtype and I want to convert all the geometries to 2006 (multiline string) gtype since the consuming application can't render 2004 gtypes. I am running a update query to change the gtype from 2004 to 2006, keeping SDO_SRID, SDO_POINT, SDO_ELEM_INFO and SDO_ORDINATES as the same. I have tested the multiline string (2006) geometries in a viewer and the shape remains the same.

 

Are there any caveat that I am missing, where there is a possibility that the shape may get changed? Please give me some pointers if there are any other solution of converting 2004 gtypes to 2006.

 

 

Regards,

Ananda

  • 1. Re: Convert Oracle Spatial heterogenous collection (2004) to multiline string (2006)
    Luc Van Linden Pro
    Currently Being Moderated

    Hi Ananda

     

    This should be feasible only if the collection geometries as such are all made up by linestrings.

    In practice the sdo_etype (second position in the triplets) in the sdo_elem_info (triplets) collection can only be a 2 (linestring(s) made up by consecutive lines or arcs) or a 4 (linestring(s) can be compound (combination) of lines and arcs).

     

    It could be useful to validate the geometries after your update statement.

     

    Hope this helps or confirms your thoughts

     

    Luc

  • 2. Re: Convert Oracle Spatial heterogenous collection (2004) to multiline string (2006)
    Paul Dziemiela Journeyer
    Currently Being Moderated

    Hi Ananda,

     

    What you are doing is really not a good idea.  As Luc points out it will only work if the collection is already only composed of linestrings and if your collection is homogenous then its not a collection.

     

    You might explain how are you ending up with collections when apparently you only want linestrings?  Either the "other stuff" is of value to you or its not.

     

    For example, if you are intersecting lines and getting back collections, then probably you are getting back lines and a few points where things happen to intersect at a single point.  What do you want to do with those points?

     

    The simplest solution is to just toss the points away.  You need to make sure your business rules support that.  A more complex solution is to pick through the results and branch to handle each accordingly.

     

    However for the simple solution here is some code to "scrub" away collections into pure lines or polygons.

     

    Cheers,

    Paul

     

       -----------------------------------------------------------------------------    -----------------------------------------------------------------------------    CREATE OR REPLACE FUNCTION scrub_lines(       p_input               IN MDSYS.SDO_GEOMETRY    ) RETURN MDSYS.SDO_GEOMETRY    AS       int_gtype   PLS_INTEGER;       sdo_temp    MDSYS.SDO_GEOMETRY;       output      MDSYS.SDO_GEOMETRY;          BEGIN       IF p_input IS NULL       THEN          RETURN NULL;                END IF;       int_gtype := p_input.get_gtype();       IF int_gtype IN (3,7)       THEN          RAISE_APPLICATION_ERROR(-20001,'found polygons in your lines');                ELSIF int_gtype IN (2,6)       THEN          RETURN p_input;                ELSIF int_gtype IN (1,5)       THEN          RETURN NULL;                ELSIF int_gtype = 4       THEN          FOR i IN 1 .. SDO_UTIL.GETNUMELEM(p_input)          LOOP             sdo_temp := SDO_UTIL.EXTRACT(p_input,i);             IF sdo_temp.get_gtype() = 2             THEN                IF output IS NULL                THEN                   output := sdo_temp;                                  ELSE                   output := SDO_UTIL.APPEND(output,sdo_temp);                                  END IF;                            ELSIF sdo_temp.get_gtype() = 3             THEN                RAISE_APPLICATION_ERROR(-20001,'found polygons in your lines');                            END IF;                      END LOOP;                   RETURN output;                END IF;    END scrub_lines;    -----------------------------------------------------------------------------    -----------------------------------------------------------------------------    CREATE OR REPLACE FUNCTION scrub_polygons(       p_input                  IN MDSYS.SDO_GEOMETRY    ) RETURN MDSYS.SDO_GEOMETRY    AS       int_gtype   PLS_INTEGER;       sdo_temp    MDSYS.SDO_GEOMETRY;       output      MDSYS.SDO_GEOMETRY;    BEGIN       IF p_input IS NULL       THEN          RETURN NULL;       END IF;       int_gtype := p_input.get_gtype();       IF int_gtype IN (3,7)       THEN          RETURN p_input;                ELSIF int_gtype IN (1,2,5,6)       THEN          RETURN NULL;                ELSIF int_gtype = 4       THEN          FOR i IN 1 .. SDO_UTIL.GETNUMELEM(p_input)          LOOP             sdo_temp := SDO_UTIL.EXTRACT(p_input,i);             IF sdo_temp.get_gtype() = 3             THEN                IF output IS NULL                THEN                   output := sdo_temp;                                  ELSE                   output := SDO_UTIL.APPEND(output,sdo_temp);                                  END IF;                            END IF;                      END LOOP;                   RETURN output;                END IF;    END scrub_polygons;
  • 3. Re: Convert Oracle Spatial heterogenous collection (2004) to multiline string (2006)
    1010855 Newbie
    Currently Being Moderated

    The scenario here is that; initially the geoms are of 2006 gtype. However after some manipulation in FME tool, the FME converts the geoms into 2004 automatically. Hence I am changing it back to 2006, keeping the other attributes intact. Done some sanity test and it is working fine. Hence I wanted to cross-check if there are any caveats, which I was missing.

     

    Thanks Luc and Paul, for clearing my doubts.

     

    Ananda

  • 4. Re: Convert Oracle Spatial heterogenous collection (2004) to multiline string (2006)
    Paul Dziemiela Journeyer
    Currently Being Moderated

    Hi Ananda,

     

    What FME process is doing that?  Willy-nilly flipping 2006s into 2004s for no good reason sounds like a bug to me (for instance it sure the heck will make any usage in ESRI products highly problematic).  Might be worth a SR to Safe to get this fixed. 

     

    Cheers,

     

    Paul

Legend

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