This content has been marked as final. Show 6 replies
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;
I know, but it is irrelevant in this case. It's just an example.
But this it better:
ps: commenting out the "else" in the original statement would nullify the geometries where sdo_union().sdo_gtype != 2002
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
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...):
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...)
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;
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.
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 :)
Here's an example that shows the issue:
On our database (10g Enterprise Edition Release 10.2.0.4.0) this will give results like:
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)
But sometimes it gives only 2002 and other times only 2006...
2002 2006 2006 2006 2006 ...
Edit: All geometries have been validated.