changing SRID of SDO_geometry from existing value without changing the original value

Question

I have a Oracle database table with a column in SDO_GEOMETRY type. I am trying to write a view on this table and get the SDO_GEOMETRY column with different SRID. I don't want to change the original table or transform the value to a different SRID. I just want to get the geometry column with a different SID (same vertice values). How to achieve this?

The table is this:

Create Table Locations (CityCode VARCHAR(2), Location SDO_GEOMETRY)

I am trying to create a view like this for example:

CREATE OR REPLACE VIEW VW_Locations AS
SELECT 
  CityCode, 
  SDO_GEOMETRY(Location, <NEW_SRID>) 
FROM Locations

I tried to use the constructor for SDO_GEOMETRY using WKT as parameter but i couldn't make it because the geometry values in my table are 3D and Oracle does not support WKT/WKB conversion of 3D SDO_GEOMETRY values. This one works for 2D geometries:

CREATE OR REPLACE VIEW VW_Locations AS
SELECT 
  CityCode, 
  SDO_GEOMETRY(SDO_UTIL.TO_WKTGEOMETRY(Location), <NEW_SRID>) AS Loc
FROM Locations

score 0 · Answer 1 · answered Jul 14 '15 at 19:54

I tried the same approach as you, and had the same problem (TO_WKTGEOMETRY export only works for 2D geometries).

My current approach is to use a custom function to apply the SRID through the object dot notation:

CREATE OR REPLACE FUNCTION APPLY_SRID 
(
  GEOM IN OUT MDSYS.SDO_GEOMETRY 
, SRID IN NUMBER DEFAULT 8307 
) RETURN MDSYS.SDO_GEOMETRY AS 
BEGIN
  GEOM.SDO_SRID := SRID;
  RETURN GEOM;
END APPLY_SRID;

In your own code, you would use this as follows:

CREATE OR REPLACE VIEW VW_Locations AS
SELECT 
  CityCode, 
  APPLY_SRID(Location, <NEW_SRID>) 
FROM Locations

changing SRID of SDO_geometry from existing value without changing the original value

1 Answers1