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.

Derive

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.

Bilestone

For others it might be important to note that in order to actually do this, one must first drop the spatial index, update the metadata table, then perform the srid update, and then re-add the spatial index – Nectarine