How do I change the SRID's for Oracle SDO_GEOMETRY

Question

I have spatial data that Oracle has assigned an SRID of 81989. I'd like to change it to 27700 - they're both the same coordinate system, its just Oracle uses its own SRID; so no re-projection is necessary (as such SDO_CS.TRANSFORM doesn't work as it actually changes the coordinates too, which I don't want)).

I've updated USER_SDO_GEOM_METADATA easily enough, but the SDO_GEOMETRY containing the data itself has the SRID too and I don't know how to change this.

So for example my current data looks like:

MDSYS.SDO_GEOMETRY(2001,81989,MDSYS.SDO_POINT_TYPE(420531.663898,268911.956161,NULL),NULL,NULL)

and I need to go change it to:

MDSYS.SDO_GEOMETRY(2001,27700,MDSYS.SDO_POINT_TYPE(420531.663898,268911.956161,NULL),NULL,NULL)

For all rows in a table. But I don't know how to automatically alter a single element in a SDO_GEOMETRY while leaving the other aspects of the array unchanged.

Can anyone point me in the direction? Thanks.

Answer 1

To update the SRID, you might use something like this:

UPDATE YOUR_TABLE T
SET T.YOUR_SDO_GEOMETRY_COLUMN.SDO_SRID = 27700
WHERE T.YOUR_SDO_GEOMETRY_COLUMN IS NOT NULL

Note that use of a table alias (like T in this example) is necessary.