Lauter kleine Helferlein

Heute soll es darum gehen, Geometrien, die nicht Simple Feature-konform sind, soweit möglich zu korrigieren.

Für eine solche Korrektur steht seit 11gR1 die Funktion RECTIFY_GEOMETRY im Package SDO_UTIL bereit, welche die korrigierte (rektifizierte) Geometrie zurückliefert.

Ein Blick in die Oracle Spatial Online-Dokumentation verrät dabei, welche Fehler korrigierbar sind:

• doppelte Stützpunkte (bezogen auf die Toleranz)
• sich selbst schneidende Polygone
• falsche Orientierung von innerem oder/und äusseren Ring(en) eines Polygons

Dort findet sich ebenso das Format beschrieben.

SDO_UTIL.RECTIFY_GEOMETRY(
  geom      IN SDO_GEOMETRY,
  tolerance IN NUMBER
) RETURN SDO_GEOMETRY;

Wie kann man nun diese Funktion in Anwendung bringen? Schauen wir uns das einfach mal genauer an.

Zunächst wird eine Tabelle angelegt, in welcher alle fehlerhaften Geometrien registriert werden sollen.

drop table geometry_errors purge;

-- Fehlertabelle anlegen
create table geometry_errors (
  table_name      varchar2(30),
  column_name     varchar2(30),
  obj_rowid       rowid,
  geometry        sdo_geometry,
  tolerance       number,
  error_code      char(5),
  error_message   varchar2(256),
  error_context   varchar2(256),
  is_fixed        number(1) default 0
);

Die eigentliche Prozedur zum Überprüfen der Geometrien, kann dann wie nachfolgend aussehen. Die problematischen Geometrien darunter werden in die Fehlertabelle übernommen.

-- Prozedur zum Prüfen auf invalide Geometrien im gesamten Schema
-- Achtung: Kann einige Zeit in Anspruch nehmen!
declare
  DEFAULT_TOLERANCE   number := 0.0000005;
  COMMIT_FREQUENCY    number := 100;
  geom_cursor         sys_refcursor;
  v_diminfo           sdo_dim_array;
  v_srid              number;
  v_tolerance         number;
  v_rowid             rowid;
  v_geometry          sdo_geometry;
  v_num_rows          number;
  v_num_errors        number;
  v_error_code        char(5);
  v_error_message     varchar2(256);
  v_error_context     varchar2(256);
  v_status            varchar2(256);
begin

  -- Alle Tabellen mit SDO_GEOMETRY Spalten prozessieren
  for t in (
    select table_name, column_name
    from   user_tab_columns
    where  data_type = 'SDO_GEOMETRY'
    order by table_name, column_name )
  loop
  
    -- Lies Toleranz aus den Metadaten
    begin
      select diminfo, srid into v_diminfo, v_srid
      from user_sdo_geom_metadata
      where table_name = t.table_name
        and column_name = t.column_name;
    exception
      when no_data_found then
        v_diminfo := null;
        v_srid := null;
    end;
 
    -- Bei fehlenden Metadaten, Default-Toleranz annehmen
    if v_diminfo is null then
      v_tolerance := DEFAULT_TOLERANCE;
    else
      v_tolerance := v_diminfo(1).sdo_tolerance;
    end if;
 
    -- Geometrien prozessieren
    v_num_rows := 0;
    v_num_errors := 0;

    open geom_cursor for
      'select rowid,' || t.column_name || ' from ' || t.table_name;
    loop
      v_status := NULL;
   
      -- Hole Geometrie
      fetch geom_cursor into v_rowid, v_geometry;
        exit when geom_cursor%notfound;
      v_num_rows := v_num_rows + 1;
   
      -- Validiere Geometry
      v_status := sdo_geom.validate_geometry_with_context (v_geometry, v_tolerance);
   
      -- Wenn Fehler, dann protokollieren
      if v_status <> 'TRUE' then
   
        -- Fehler hochzählen
        v_num_errors := v_num_errors + 1;
  
        -- Format the error message
        if length(v_status) >= 5 then
          v_error_code := substr(v_status, 1, 5);
          v_error_message := sqlerrm(-v_error_code);
          v_error_context := substr(v_status,7);
        else
          v_error_code := v_status;
          v_error_message := null;
          v_error_context := null;
        end if;
  
        -- Fehler wegschreiben
        insert into geometry_errors (
          table_name,
          column_name,
          obj_rowid,
          geometry,
          tolerance,
          error_code,
          error_message,
          error_context
        )
        values (
          t.table_name,
          t.column_name,
          v_rowid,
          v_geometry,
          v_tolerance,
          v_error_code,
          v_error_message,
          v_error_context
        );
      end if;
   
      -- Commit entsprechend COMMIT_FREQUENCY
      if mod(v_num_rows,COMMIT_FREQUENCY) = 0 then
        commit;
      end if;
    end loop;
  end loop;
  
  -- Finales Commit
  commit;
end;
/

Zu beachten ist dabei, dass das Ausführen der Prozedur eine Weile in Anspruch nehmen kann, je nach Anzahl der zu überprüfenden Geometrien.

Bevor es an die eigentliche Korrektur geht, schauen wir uns zunächst die Fehlertabelle genauer an.

-- Fehler pro Tabelle, Error Code
select table_name, error_code, error_message, count(*)
from geometry_errors
group by table_name, error_code, error_message 
order by table_name, error_code;

-- Fehler pro Error Code, Tabelle
select error_code, error_message, table_name, count(*)
from geometry_errors
group by error_code, error_message, table_name
order by error_code, table_name;

-- Fehlerdetails
select table_name, obj_rowid, is_fixed, error_message, error_context
from geometry_errors
where is_fixed = 0
order by table_name, obj_rowid;

Für die Korrektur wird nun die Funktion SDO_UTIL.RECTIFY_GEOMETRY eingesetzt.

-- Prozedur zum Korrigieren der Geometrien
declare
  -- Fehlerbehandlung für nicht korrigierbare Geometrien
  -- "ORA-13199: the given geometry cannot be rectified"
  cannot_rectify exception;
  pragma exception_init(cannot_rectify, -13199);
  v_geometry_fixed sdo_geometry;

begin
  -- Invalide Geometrien prozessieren
  for e in (
    select rowid, table_name, column_name, obj_rowid, tolerance, geometry
    from geometry_errors
    where is_fixed = 0
    order by table_name, column_name
  )
  loop
    -- Wenn möglich, Geometrie korrigieren mit SDO_UTIL.RECTIFY_GEOMETRY. 
    -- Andernfalls:
    --   In 11.1.0.6: Funktion gibt NULL zurück
    --   In 11.1.0.7 und 11.2: Funktion wirft ORA-13199 Fehler
    begin
      v_geometry_fixed := sdo_util.rectify_geometry (e.geometry, e.tolerance);
    exception
      when cannot_rectify then
        v_geometry_fixed := null;
    end;

    if v_geometry_fixed is not null then
      -- Korrigiere Geometrie
      execute immediate 'update ' || e.table_name || ' set '|| e.column_name || ' = :g where rowid = :r'
        using v_geometry_fixed, e.obj_rowid;

      -- Kennzeichne korrigierte Geometrien in Fehlertabelle mit is_fixed = 1
      update geometry_errors set is_fixed = 1 where rowid = e.rowid;
      -- Alternativ ist auch DELETE möglich
      -- delete from geometry_error where rowid = e.rowid;
    end if;

  end loop;
end;
/

Was wurde korrgiert? Was nicht? Das Ergebnis kann am Ende in der Fehlertabelle überprüft werden.

-- Fehler nach Tabelle, Korrekturstatus und Error Code
select table_name, is_fixed, error_code, error_message, count(*)
from geometry_errors
group by table_name, is_fixed, error_code, error_message 
order by table_name, is_fixed, error_code;

-- Fehler nach Korrekturstatus und Error Code
select is_fixed, error_code, error_message, count(*)
from geometry_errors
group by is_fixed, error_code, error_message
order by is_fixed, error_code;

-- Details
select table_name, obj_rowid, is_fixed, error_message, error_context
from geometry_errors
order by is_fixed, table_name, obj_rowid;

Möglicherweise konnten nicht alle fehlerhaften Geometrien auf diese Weise korrigiert werden, aber ein erster Schritt in Richtung Qualitätsverbesserung ist getan.
Wer die Fehlertabelle nach erfolgter Korrektur nicht mehr benötigt, sollte übrigens nicht vergessen, diese wieder zu entfernen.

drop table geometry_errors purge;