De SDEBINARY à SDO_GEOMETRY dans Oracle Spatial

Voici donc une méthode propre, étape par étape, pour transformer une table ESRI (Geodatabase Feature Class) d'un format SDEBINARY vers un format SDO_GEOMETRY dans Oracle à l'aide de FME. Cette méthode va aider à transformer en lot plusieurs Feature Class avec de Workbench FME, des scripts SQL et des lignes de commandes ArcSDE. Le fait que ArcGIS server ne supporte pas les Features Class avec deux colonnes géométriques, complique un peu la chose, mais il est possible d'y arriver en suivant ce roadmap:

1) Créer une copie de la Feature Class avec un suffixe “_SDO” à l’aide de FME.  Dans cette opération, on va transformer la géométrie SDEBINARY en SDO_GEOMETRY dans Oracle.  On ajoute un suffixe parce qu'on travaille dans la Même BD.

2) Une fois le transfert fait dans FME on va valider les données SELECT count(*) FROM REGION_S_SDO;SELECT count(*) FROM REGION_S_SDO WHERE GEOM is null; 3) FME gère bien les métadonnées spatiales dans Oracle, mais on peut tout de même les harmoniser soit-même. DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME ='REGION_S_SDO';INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)VALUES ('REGION_S_SDO', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-83,-54, 0.0000000000000005),MDSYS.SDO_DIM_ELEMENT('Y',43, 63,0.0000000000000005)),4326); 4) Il est important de vérifier que toutes les géométries de notre nouveau Feature Class sont valides DROP TABLE val_results;CREATE TABLE val_results (sdo_rowid rowid, result varchar2(2000));EXECUTE SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('REGION_S_SDO' ,'GEOM', 'VAL_RESULTS'); 5) Le résultat de cette requête indique le nombre de géométries invalides SELECT count(*) from val_results; 6) On peut au besoin appliquer des correctifs si des géométries sont invalides: UPDATE REGION_S_SDO SET geom = (SELECT sdo_util.rectify_geometry(REGION_S_SDO.geom, 0.0000000000000005) from val_results WHERE val_results.sdo_rowid=REGION_S_SDO.rowid);

7) Bien qu’impossible via FME, il pourrait arriver de trouver des géométries provenant d’ailleurs sans SRID.  Pour ne pas avoir de problème il faut corriger le SRID - NOTE on doit utiliser l'alias pour accéder au propriété de SDO_GEOMETRY select count(srid) as cnt_srid,srid from (SELECT t.geom.sdo_srid as srid FROM REGION_S_SDO t) group by srid; UPDATE REGION_S_SDO t set t.GEOM.SDO_SRID = 4326 WHERE GEOM is not null; 8 ) Créer index avec PK et Spatial sur la copie de la table. CREATE UNIQUE INDEX PK_REGION_S_SDO ON REGION_S_SDO(OBJECTID); 9) On va en même temps reconstruire les index de la table SDE pour la requête UPDATE plus loin de le process: SELECT ' ALTER INDEX ' || INDEX_NAME || ' REBUILD;' FROM USER_INDEXES WHERE TABLE_NAME = 'REGION_S' AND INDEX_TYPE='NORMAL'; ------------------------------------------- ALTER INDEX R262_SDE_ROWID_UK REBUILD;

10) On va détruire les tables géométriques ArcSDE avec la commande sdelayer.  Cette commande ne détruira pas la table, mais bien juste les classes géométriques du modèle ArcSDE (tables FXX, SXX, triggers et indexs).

Deletes an entire feature class with sdelayer means that all features and the feature class definition are permanently removed. The business table is not deleted.  For feature classes that have been implemented using the binary schema (Oracle and SQL Server only), deleting a feature class drops the DBMS spatial tables (the F and S tables) and removes the definition of the feature class from the ArcSDE data dictionary tables. select 'sdelayer -o delete -l ' || table_name || ',SHAPE -i port:5151 -u '|| owner || ' -p '|| owner || ' -N' from sde.table_registry where owner = 'GEO'; -------------------------------------------- sdelayer -o delete -l REGION_S,SHAPE -i port:5151 -u GEO -p baby@geo -N . . .

11) Ajouter une colonne SDO_GEOMETRY à la classe géométrique.  On utilise cette SQL pour générer le code: select 'ALTER TABLE ' || table_name || ' ADD GEOM SDO_GEOMETRY;'from sde.table_registry where owner = 'GEO'; ---------------------------- ALTER TABLE REGION_S ADD GEOM SDO_GEOMETRY; . . 12) Faire la requête update qui va transférer les SDO_GEOMETRY de la table temporaire à la classe géométrique UPDATE REGION_S SET GEOM = (select REGION_S_SDO.GEOM from REGION_S_SDO where REGION_S.objectid = REGION_S_SDO.objectid); 13) On doit détruire les métadonnées du catalogue Oracle pour en remettre de bonnes infos propres. DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME ='REGION_S'; INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)VALUES ('REGION_S', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-83,-54, 0.00005),MDSYS.SDO_DIM_ELEMENT('Y',43, 63,0.00005)),4326); 14) Créer l’index spatial. L’index utilisé est un Index R-Tree. select 'CREATE INDEX '|| table_name || '_IDX ON ' || table_name||'("GEOM") INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('' geodetic=false layer_gtype=COLLECTION SDO_COMMIT_INTERVAL=100 tablespace=BDTAG INITIAL=200K NEXT=64K MINEXTENTS=1 MAXEXTENTS=200 PCTINCREASE=0'');' sde.table_registry where owner = 'GEO'; ---------------------------- CREATE INDEX EQUIP_L_GEOIDX ON REGION_S("GEOM") INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS (' geodetic=false layer_gtype=COLLECTION SDO_COMMIT_INTERVAL=100 tablespace=BDTAG INITIAL=200K NEXT=64K MINEXTENTS=1 MAXEXTENTS=200 PCTINCREASE=0'); . . .

NOTE: Il est possible que l’index par défaut R-Tree ne fonctionne pas, même en corrigeant les géométrie avec FME et/ou les packages de fonctions d’Oracle spatial.  Dans un tel cas on aura le message d’erreur qui ressemble à ceci: Erreur SQL : ORA-29855: erreur d'exécution de la routine ODCIINDEXCREATEORA-13205: erreur interne lors de l'analyse des paramètres spatiaux ORA-06512: à "MDSYS.SDO_INDEX_METHOD_10I", ligne 1029855. 00000 -  "error occurred in the execution of ODCIINDEXCREATE routine" *Cause: Failed to successfully execute the ODCIIndexCreate routine .*Action:   Check to see if the routine has been coded correctly.

Pour remédier à ce problème, on doit utiliser un index de type Quadtree.  Oracle ne recommande plus d’utiliser ce type d’index mais il fonctionne toujours très bien.  Plus le paramètre SDO_LEVEL est élevé, plus la pyramide d’indexation sera longue à créer et volumineuse en espace disque.  Ne chercher plus les fonctions obsolètes pour évaluer ce paramètre (tel SDO_TUNE.ESTIMATE_TILING_LEVEL): CREATE INDEX REGION_S_SDO_GEOIDX ON REGION_S("GEOM") INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS (' SDO_LEVEL=7 geodetic=false layer_gtype=COLLECTION SDO_COMMIT_INTERVAL=100 tablespace=BDTAG INITIAL=200K NEXT=64K MINEXTENTS=1 MAXEXTENTS=200 PCTINCREASE=0');

15) Il est conseillé de faire un analyse table sur l’index spatial pour optimiser les performances.  Puisqu’il s’agit d’un index système on doit requêter la table sdo_index_table pour trouver le nom de l’index spatial: select 'analyze table ' || sdo_index_table || ' compute statistics;'from user_sdo_index_metadata where sdo_index_name like 'BDGA%'; -------------------------- 16) On va enregistrer la classe géométrique de type SDO_GEOMETRY dans le registre ArcSDE. sdelayer -o register -l REGION_S,GEOM -e l -C OBJECTID,USER -R 2 -k SDO_GEOMETRY -t SDO_GEOMETRY -i port:5151 -u geo -p baby -s sigdev

Merci au DBA Beauceron pour son aide! Vive la Beauce