From NGDC Wiki
Jump to: navigation, search

orginal tiki page

SQL commands for target database

-- Change the SRID of MBINFO_SURVEY_TSQL to 8307 (WGS84) in user_sdo_geom_metadata

update user_sdo_geom_metadata set srid=8307 where table_name = 'MBINFO_SURVEY_TSQL';

-- find spatial indexes for this table

select index_name from user_indexes 
where index_type='DOMAIN' and table_name='MBINFO_SURVEY_TSQL';

-- Drop the spatial index named in above output


-- Set SRID in each of the shapes in the table

update MBINFO_SURVEY_TSQL t set shape = mdsys.sdo_geometry(t.shape.sdo_gtype, 8307,
t.shape.sdo_point, t.shape.sdo_elem_info, t.shape.sdo_ordinates);

Instead of re-creating the geometry, one can also modify the SRID directly, e.g.

update MBINFO_SURVEY_TSQL t set t.shape.sdo_srid = 8307;

-- Reinstate the spatial index

create index MBINFO_SURVEY_TSQL_SPX on MBINFO_SURVEY_TSQL(shape) indextype is 

-- Check on the new SRID aftward and commit if OK

select distinct t.shape.sdo_srid from MBINFO_SURVEY_TSQL t;


Make sure the SDE layer registration is set to match.

sdelayer -o alter -l MBINFO_SURVEY_TSQL,shape -G 4326 -k SDO_WGS84 -s -u mb -p

It may be easier to drop a layer's registration before beginning the process and then re-register after the table has been updated. Be careful - "sdelayer -o delete" may remove the user_sdo_geom_metadata record for the table. It should not drop the table itself or it's spatial index