6 Replies Latest reply: Apr 18, 2013 5:04 AM by Stefan Jager RSS

    sdo_union gives inconsistent results

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