Naming Convention (Spatial Column)

keywords: spatial database, spatial column, metadata, oracle, naming convention, PL/SQL …

sometimes, someone, somehow .. doesn't give too much attention naming the database tables and columns. That kind of messy database produce a lot of headache to the SW developers and GIS experts (speaking of spatial databases). This kind of mess happens specially when the tables are created using several different tools and by several different people. If you are at the end of the chain and you have to work with spatial tables that don't follow any kind of naming convention you may lose a lot of time …. I don’t want to mention nerves :-)

To help a little bit to myself and others that share same destiny I’ve wrote simple pl/sql procedure that renames spatial column of all spatial tables in one oracle schema. The changes are also done in spatial metadata (user_sdo_geom_metadata), spatial index is rebuilt automatically.

In this particular case schema name is PPSV and desired spatial column name is GEOM... Change it respectively for your needs …

If you use Geoserver and you have already published layers based on tables that need column renaming you just have to restart your Geoserver after you apply proposed procedure.

...enjoy!

declare
    cursor c_tablice is 
        select distinct(table_name) t_name 
            from  all_tab_cols 
            where owner = 'PPSV' 
            and column_name = 'VRSTA' 
            and table_name not like '%$%';
    cursor c_gtables is 
        select table_name t_name, column_name c_name 
            from  all_tab_cols 
            where owner = 'PPSV' 
            and  table_name not like '%$%' 
            and data_type = 'SDO_GEOMETRY' 
            order by table_name;

    type vrsta_type is ref cursor;
    c_vrsta vrsta_type;
    vrsta varchar2(2000);
    v_sql varchar2(2000);
    v_g_rename varchar2(2000);  -- sql to rename geometry field
    v_md_update varchar2(2000); -- sql to update geometry metadata
    v_new_geom_field_name varchar2(100) := 'GEOM';
begin
    for  r_tablice in c_gtables
    loop
        --dbms_output.put_line('> TABLE: '||r_tablice.t_name || '   GEOMETRY FIELD: ' || r_tablice.c_name);
        v_g_rename := 'alter table ' || r_tablice.t_name || ' rename column ' || r_tablice.c_name || ' to ' || v_new_geom_field_name;
        v_md_update := 'update user_sdo_geom_metadata set column_name = ' || q'[']' || v_new_geom_field_name || q'[']'||  ' where table_name = ' || q'[']' || r_tablice.t_name || q'[']' ;
     
     
        if r_tablice.c_name != v_new_geom_field_name
        then
            execute immediate v_g_rename;
            dbms_output.put_line(v_g_rename);
            execute immediate v_md_update;
            dbms_output.put_line(v_md_update );
        end if;

     
    end loop;
exception
    when others then
        dbms_output.put_line('err: ' || v_g_rename);
        dbms_output.put_line('err: ' || v_md_update);
end;
/