13356 error when validating point layer

Question

I get a 13356 error when validating a polyline layer. I know that I can use SDO_UTIL.REMOVE_DUPLICATE_VERTICES to fix the problem but how would I write the syntax for this polyline layer?

+----------------------+----------------------------------------+
|     SDO_ROWID        |                STATUS                  |  
+----------------------+----------------------------------------+
|         (null)       |  Rows Processed <4035>                 |   
| AAA2EVAANAAB8ccAAJ   |  13356 [Element <1>] [Coordinate <3>]  |   
| AAA2EVAANAAB8cvAAK   |  13356 [Element <1>] [Coordinate <5>]  |    
| AAA2EVAANAAB8cwAAD   |  13356 [Element <1>] [Coordinate <4>]  |   
| AAA2EVAANAAB8dBAAJ   |  13356 [Element <1>] [Coordinate <1>]  |  
| AAA2EVAANAAB8dBAAO   |  13356 [Element <1>] [Coordinate <4>]  |   
| AAA2EVAANAAB8d/AAP   |  13356 [Element <1>] [Coordinate <3>]  |  
+----------------------+----------------------------------------+

Could I write a syntax like below:

update my_geometries
set geometry = sdo_util.remove_duplicate_vertices(geometry)
where rowid in (
  select sdo_rowid 
  from GEOM_VALID_COLLECTORS   --table that was created to store results
  where substr(status,1,5) = '13356'
);

Then migrate to current revalidate again:

--migrate to current
EXECUTE SDO_MIGRATE.TO_CURRENT('OTTAWACOLLECTORS', 'GEOMETRY', 4035);   

--validate layer
CREATE TABLE Geom_Valid_Collectors (SDO_ROWID ROWID, STATUS VARCHAR2(2000) );                       --some error, go back because I did this with the buildings accidentally
EXECUTE MDSYS.SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT ('OTTAWACOLLECTORS', 'GEOMETRY', 'Geom_Valid_Collectors'); 
SELECT * FROM Geom_Valid_Collectors;

Answer 1

Duplicate points in a point layer is not possible. Or are those multi-points ?

Also the fact that a point is seen as a duplicate of another means that this point and the one that follows are closer than the tolerance you specified when calling the validation function. If you validate with a tolerance of, say 0.5, it means that no two consecutive points must be closer than 50cm. The exact meaning of the error message is:

ORA-13356: adjacent points in a geometry are redundant

Validating at different tolerances will detect different errors. It is therefore important to validate at the tolerance you expect for your data.

Of course, when two consecutive points are exactly the same, they will also be diagnosed as duplicates.

With that in mind, you can remove those "duplicate" points from your geometries by doing this:

update ottawacollectors
set geometry = sdo_util.remove_duplicate_vertices(geometry, 0.05)
where rowid in (
  select sdo_rowid 
  from geom_valid_collectors
  where substr(status,1,5) = '13356
);

(I used a tolerance of 0.05 = 5cm. Replace it with the actual tolerance of your data, as you specified it in your metadata).

Notice that each shape may contain multiple duplicate points at different places. The validation only reports the first one. Also the shape may contain other errors: the validation stops on the first one. So after removing the duplicates, you need to revalidate.

You do not need the sdo_migrate.to_current. That is an old function that was only used ages ago in the 8i time frame. It is still sometimes used for correcting orientation errors.