This discussion is archived
6 Replies Latest reply: Apr 18, 2013 3:04 AM by Stefan Jager RSS

sdo_union gives inconsistent results

Pleiadian Journeyer
Currently Being Moderated
Hi,

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

We are using sdo_union to convert line geometries. Some of the geometries that are inserted into our database have sdo_gtype 2006, but can be easily converted to 2002 using a query like:
update lines_tb l 
set    shape = case when sdo_geom.sdo_union(l.shape, l.shape, 0.001).sdo_gtype = 2002 
                    then sdo_geom.sdo_union(l.shape, l.shape, 0.001) 
                    else l.shape 
               end 
where  l.shape.sdo_gtype = 2006;
We notice however that if you run the query again, it keeps finding results! It does not make any sense...

The only conslusion I can make is that sdo_union is not consistent in its results!

Can anyone confirm this behaviour?

Thanks,

Rop
  • 1. Re: sdo_union gives inconsistent results
    user8048037 Newbie
    Currently Being Moderated
    Why do you need the ELSE statement ? You will UPDATE these geometries (which don't fit to sdo_gtype=2002) with itself again and again:
    update lines_tb l 
    set    shape = case when sdo_geom.sdo_union(l.shape, l.shape, 0.001).sdo_gtype = 2002 
                        then sdo_geom.sdo_union(l.shape, l.shape, 0.001) 
                        --else l.shape 
                   end 
    where  l.shape.sdo_gtype = 2006;
  • 2. Re: sdo_union gives inconsistent results
    Pleiadian Journeyer
    Currently Being Moderated
    I know, but it is irrelevant in this case. It's just an example.

    But this it better:
    update lines_tb l 
    set    shape = sdo_geom.sdo_union(l.shape, l.shape, 0.001) 
    where  l.shape.sdo_gtype = 2006
    and    sdo_geom.sdo_union(l.shape, l.shape, 0.001).sdo_gtype = 2002 
    ps: commenting out the "else" in the original statement would nullify the geometries where sdo_union().sdo_gtype != 2002
  • 3. Re: sdo_union gives inconsistent results
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Pleiadian,

    With respect to the above query: use the WITH-statement, that way you do not have to execute the UNION twice.

    Secondly, a few questions pop up:
    1. Do you need to use Union? Would simply replacing the GType and the ELEM_INFO be enough (with maybe a validation on duplicate points)? So something like this (not tested...):
    update lines_tb l 
    set    shape = SDO_UTIL.REMOVE_DUPLICATE_VERTICES(SDO_GEOMETRY(2002, l.shape.srid, NULL, SDO_ELEM_INFO(1,2,2), l.shape.SDO_ORDINATES), 0.001) 
    where  l.shape.sdo_gtype = 2006;
    2. If all your elements have to be 2002, you can specify in your index that only that GType is allowed. That way you would not need to fix your data afterwards. But I don't know if that is a feasible construction to use (if your data is coming from MicroStation for example it would be difficult to enforce this...)

    And lastly, to check if Union is inconsistent, instead of updating the current geometry insert the ID in a new table. A second run of the same query should give exactly the same results as already stored in the table, but if it doesn't then this sounds like something Oracle should look into.

    HTH,
    Stefan
  • 4. Re: sdo_union gives inconsistent results
    Pleiadian Journeyer
    Currently Being Moderated
    Hi Stefan,

    Bedankt voor je antwoord!

    1) Yes, there are different ways to reduce a geometry to it's simplest form without losing data. sdo_union with itself is just a quick and easy way to do it.

    2) No, the geometries do not HAVE to be 2006, we just prefer 2002 if possible. This is input data for building a (esri) geometric network and this fails on lines with gtype = 2006. Reducing the number of 2006 gtypes improves the quality of the geometric network.

    I have done several tests and can conclude that in some case, sdo_union is giving different results on the same input. Time to migrate to 11g :)

    Rop
  • 5. Re: sdo_union gives inconsistent results
    Pleiadian Journeyer
    Currently Being Moderated
    Here's an example that shows the issue:
    with g as ( select sdo_geometry(2006,31370,sdo_point_type(null,null,null),
                       sdo_elem_info_array( 1,2,1,7,2,1,15,2,1,53,2,1,61,2,1),
                       sdo_ordinate_array( 63634.1550000012,183385.377799999,63638.9395999983,183373.2269,63641.8335999995,183365.5623,63634.1550000012,183385.377799999,63634.1600999981,
                                           183385.3662,63634.1458000019,183385.400899999,63634.1550000012,183385.377799999,63632.0187999979,183387.558600001,63632.0841000006,183387.529199999,
                                           63632.2485999987,183387.4463,63632.4094000012,183387.356400002,63632.5659999996,183387.259599999,63632.7184000015,183387.156100001,63632.8660999984,
                                           183387.046100002,63633.0088,183386.9298,63633.1463999972,183386.807300001,63633.2780999988,183386.679499999,63633.4047999978,183386.545499999,63633.5253999978,
                                           183386.406199999,63633.6398999989,183386.261799999,63633.7479000017,183386.112599999,63633.8493999988,183385.958799999,63633.9442000017,183385.800799999,
                                           63634.0319999978,183385.638799999,63634.1125999987,183385.473099999,63634.1550000012,183385.377799999,63632.0187999979,183387.558600001,63632.1538000032,
                                           183387.529800002,63631.9161999971,183387.604800001,63632.0187999979,183387.558600001,63619.0442000032,183388.850099999,63629.3642999977,183388.124699999,
                                           63632.0187999979,183387.558600001 )) geom from dual )
    select sdo_geom.sdo_union(g.geom,g.geom, 0.001).sdo_gtype
    from   g
    ,    ( select * from all_tables where rownum <= 100)
    On our database (10g Enterprise Edition Release 10.2.0.4.0) this will give results like:
    2002
    2006
    2006
    2006
    2006
    ...
    But sometimes it gives only 2002 and other times only 2006...

    Edit: All geometries have been validated.
  • 6. Re: sdo_union gives inconsistent results
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Rop,

    Then this must be an issue already fixed, because I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production, and every time I execute this it returns 2002.
    Time to migrate to 11g :)
    It seems so ;-)

    Cheers,
    Stefan

Legend

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