Oracle Spatial - Coordinate System

About

The SDO_SRID attribute identify the associated coordinate System with the geometry. Its value can be null or one of the SDO_COORD_REF_SYS table.

Articles Related

MetaData

The coordinate systems functions and procedures use information provided in the tables and views supplied with Oracle Spatial. The tables and views are part of the MDSYS schema; however, public synonyms are defined, so you do not need to specify MDSYS. before the table or view name.

All tables are explained in this paragraph from the user guide.

Coordinate System Transformation

This section presents a simplified example that uses coordinate system transformation functions and procedures.

Polygon

-- Return the transformation of cola_c using to_srid 8199 
-- ('Longitude / Latitude (Arc 1950)')
SELECT c.name, SDO_CS.TRANSFORM(c.shape, m.diminfo, 8199) 
  FROM cola_markets_cs c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS_CS' AND m.column_name = 'SHAPE' 
  AND c.name = 'cola_c';

-- Transform the geometry to the coordinate system 90112
-- and create a table to store the new geometries
CREATE TABLE europa_landen_90112 AS
SELECT C.Code,
  C.Name,
  C.COUNTRY_UK,
  Sdo_Cs.Transform(C.Geometry, M.Diminfo, 90112) geometry
FROM europa_landen c,
  USER_SDO_GEOM_METADATA M
WHERE M.TABLE_NAME = 'EUROPA_LANDEN'
AND M.COLUMN_NAME  = 'GEOMETRY';

Don't forget to create the metadata before using this new layer

INSERT
INTO User_Sdo_Geom_Metadata VALUES
  (
    'EUROPA_LANDEN_90112',
    'GEOMETRY',
    MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, .005), MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, .005)),
    90112
  );
CREATE INDEX EUROPA_LANDEN_90112_sdx ON EUROPA_LANDEN_90112
  (
    GEOMETRY
  )
  INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Point

See Oracle Spatial - Point

EPSG TO

EPSG to SRID

SELECT SDO_CS.MAP_EPSG_SRID_TO_ORACLE(28992) FROM DUAL;
SELECT SDO_CS.MAP_ORACLE_SRID_TO_EPSG(28992) from dual;
--Gets the version number of the EPSG dataset used by Oracle Spatial.
SELECT SDO_CS.GET_EPSG_DATA_VERSION FROM DUAL;