The Oracle Spatial and Graph support for three-dimensional coordinate reference systems complies with the EPSG model (described in EPSG Model and Spatial and Graph), which provides the following types of coordinate reference systems:
Geographic 2D
Projected 2D
Geographic 3D, which consists of Geographic 2D plus ellipsoidal height, with longitude, latitude, and height based on the same ellipsoid and datum
Compound, which consists of either Geographic 2D plus gravity-related height or Projected 2D plus gravity-related height
Thus, there are two categories of three-dimensional coordinate reference systems: those based on ellipsoidal height (geographic 3D, described in Geographic 3D Coordinate Reference Systems) and those based on gravity-related height (compound, described in Compound Coordinate Reference Systems).
Three-dimensional computations are more accurate than their two-dimensional equivalents, particularly when they are chained: For example, datum transformations internally always are performed in three dimensions, regardless of the dimensionality of the source and target CRS and geometries. When two-dimensional geometries are involved, one or more of the following can occur:
When the input or output geometries and CRS are two-dimensional, the (unspecified) input height defaults to zero (above the ellipsoid, depending on the CRS) for any internal three-dimensional computations. This is a potential source of inaccuracy, unless the height was intended to be exactly zero. (Data can be two-dimensional because height values were originally either unavailable or not considered important; this is different from representing data in two dimensions because heights are known to be exactly zero.
The transformation might then internally result in a non-zero height. Since the two-dimensional target CRS cannot accommodate the height value, the height value must be truncated, resulting in further inaccuracy.
If further transformations are chained, the repeated truncation of height values can result in increasing inaccuracies. Note that an inaccurate input height can affect not only the output height of a transformation, but also the longitude and latitude.
However, if the source and target CRS are three-dimensional, there is no repeated truncation of heights. Consequently, accuracy is increased, particularly for transformation chains.
For an introduction to support in Spatial and Graph for three-dimensional geometries, see Three-Dimensional Spatial Objects.
A geographic three-dimensional coordinate reference system is based on longitude and latitude, plus ellipsoidal height. The ellipsoidal height is the height relative to a reference ellipsoid, which is an approximation of the real Earth. All three dimensions of the CRS are based on the same ellipsoid.
Using ellipsoidal heights enables Spatial and Graph to perform internal operations with great mathematical regularity and efficiency. Compound coordinate reference systems, on the other hand, require more complex transformations, often based on offset matrixes. Some of these matrixes have to be downloaded and configured. Furthermore, they might have a significant footprint, on disk and in main memory.
The supported geographic 3D coordinate reference systems are listed in the SDO_CRS_GEOGRAPHIC3D view, described in SDO_CRS_GEOGRAPHIC3D View.
A compound three-dimensional coordinate reference system is based on a geographic or projected two-dimensional system, plus gravity-related height. Gravity-related height is the height as influenced by the Earth's gravitational force, where the base height (zero) is often an equipotential surface, and might be defined as above or below "sea level."
Gravity-related height is a more complex representation than ellipsoidal height, because of gravitational irregularities such as the following:
Orthometric height is also referred to as the height above the geoid. The geoid is an equipotential surface that most closely (but not exactly) matches mean sea level. An equipotential surface is a surface on which each point is at the same gravitational potential level. Such a surface tends to undulate slightly, because the Earth has regions of varying density. There are multiple equipotential surfaces, and these might not be parallel to each other due to the irregular density of the Earth.
Height relative to mean sea level, to sea level at a specific location, or to a vertical network warped to fit multiple tidal stations (for example, NGVD 29)
Sea level is close to, but not identical to, the geoid. The sea level at a given location is often defined based on the "average sea level" at a specific port.
The supported compound coordinate reference systems are listed in the SDO_CRS_COMPOUND view, described in SDO_CRS_COMPOUND View.
You can create a customized compound coordinate reference system, which combines a horizontal CRS with a vertical CRS. (The horizontal CRS contains two dimensions, such as X and Y or longitude and latitude, and the vertical CRS contains the third dimension, such as Z or height or altitude.) Creating a Compound CRS explains how to create a compound CRS.
Spatial and Graph supports three-dimensional coordinate transformations for SDO_GEOMETRY objects directly, and indirectly for point clouds and TINs. (For example, a point cloud must be transformed to a table with an SDO_GEOMETRY column.) The supported transformations include the following:
Three-dimensional datum transformations
Transformations between ellipsoidal and gravity-related height
For three-dimensional datum transformations, the datum transformation between the two ellipsoids is essentially the same as for two-dimensional coordinate reference systems, except that the third dimension is considered instead of ignored. Because height values are not ignored, the accuracy of the results increases, especially for transformation chains.
For transformations between ellipsoidal and gravity-related height, computations are complicated by the fact that equipotential and other gravity-related surfaces tend to undulate, compared to any ellipsoid and to each other. Transformations might be based on higher-degree polynomial functions or bilinear interpolation. In either case, a significant parameter matrix is required to define the transformation.
For transforming between gravity-related and ellipsoidal height, the process usually involves a transformation, based on an offset matrix, between geoidal and ellipsoidal height. Depending on the source or target definition of the offset matrix, a common datum transformation might have to be appended or prefixed.
Example 6-2 Three-Dimensional Datum Transformation
Example 6-2 shows a three-dimensional datum transformation.
set numwidth 9 CREATE TABLE source_geoms ( mkt_id NUMBER PRIMARY KEY, name VARCHAR2(32), GEOMETRY SDO_GEOMETRY); INSERT INTO source_geoms VALUES( 1, 'reference geom', SDO_GEOMETRY( 3001, 4985, SDO_POINT_TYPE( 4.0, 55.0, 1.0), NULL, NULL)); INSERT INTO USER_SDO_GEOM_METADATA VALUES ( 'source_geoms', 'GEOMETRY', SDO_DIM_ARRAY( SDO_DIM_ELEMENT('Longitude', -180, 180, 10), SDO_DIM_ELEMENT('Latitude', -90, 90, 10), SDO_DIM_ELEMENT('Height', -1000,1000, 10)), 4985); commit; -------------------------------------------------------------------------------- CALL SDO_CS.TRANSFORM_LAYER( 'source_geoms', 'GEOMETRY', 'GEO_CS_4979', 4979); INSERT INTO USER_SDO_GEOM_METADATA VALUES ( 'GEO_CS_4979', 'GEOMETRY', SDO_DIM_ARRAY( SDO_DIM_ELEMENT('Longitude', -180, 180, 10), SDO_DIM_ELEMENT('Latitude', -90, 90, 10), SDO_DIM_ELEMENT('Height', -1000,1000, 10)), 4979); set lines 210; -------------------------------------------------------------------------------- CALL SDO_CS.TRANSFORM_LAYER( 'GEO_CS_4979', 'GEOMETRY', 'source_geoms2', 4985); INSERT INTO USER_SDO_GEOM_METADATA VALUES ( 'source_geoms2', 'GEOMETRY', SDO_DIM_ARRAY( SDO_DIM_ELEMENT('Longitude', -180, 180, 10), SDO_DIM_ELEMENT('Latitude', -90, 90, 10), SDO_DIM_ELEMENT('Height', -1000,1000, 10)), 4985); -------------------------------------------------------------------------------- DELETE FROM USER_SDO_GEOM_METADATA WHERE table_name = 'GEO_CS_4979'; DELETE FROM USER_SDO_GEOM_METADATA WHERE table_name = 'SOURCE_GEOMS'; DELETE FROM USER_SDO_GEOM_METADATA WHERE table_name = 'SOURCE_GEOMS2'; drop table GEO_CS_4979; drop table source_geoms; drop table source_geoms2;
As a result of the transformation in Example 6-2, (4, 55, 1) is transformed to (4.0001539, 55.0000249, 4.218).
Example 6-3 Transformation Between Geoidal And Ellipsoidal Height
Example 6-3 configures a transformation between geoidal and ellipsoidal height, using a Hawaii offset grid. Note that without the initial creation of a rule (using the SDO_CS.CREATE_PREF_CONCATENATED_OP procedure), the grid would not be used.
-- Create Sample operation: insert into mdsys.sdo_coord_ops ( COORD_OP_ID, COORD_OP_NAME, COORD_OP_TYPE, SOURCE_SRID, TARGET_SRID, COORD_TFM_VERSION, COORD_OP_VARIANT, COORD_OP_METHOD_ID, UOM_ID_SOURCE_OFFSETS, UOM_ID_TARGET_OFFSETS, INFORMATION_SOURCE, DATA_SOURCE, SHOW_OPERATION, IS_LEGACY, LEGACY_CODE, REVERSE_OP, IS_IMPLEMENTED_FORWARD, IS_IMPLEMENTED_REVERSE) values ( 1000000005, 'Test Bi-linear Interpolation', 'CONVERSION', null, null, null, null, 9635, null, null, 'Oracle', 'Oracle', 1, 'FALSE', null, 1, 1, 1); --Create sample parameters, pointing to the offset file --(in this case reusing values from an existing operation): insert into mdsys.sdo_coord_op_param_vals ( coord_op_id, COORD_OP_METHOD_ID, PARAMETER_ID, PARAMETER_VALUE, PARAM_VALUE_FILE_REF, PARAM_VALUE_FILE, PARAM_VALUE_XML, UOM_ID) ( select 1000000005, 9635, 8666, PARAMETER_VALUE, PARAM_VALUE_FILE_REF, PARAM_VALUE_FILE, PARAM_VALUE_XML, UOM_ID from mdsys.sdo_coord_op_param_vals where coord_op_id = 999998 and parameter_id = 8666); --Create a rule to use this operation between SRIDs 7406 and 4359: call sdo_cs.create_pref_concatenated_op( 300, 'CONCATENATED OPERATION', TFM_PLAN(SDO_TFM_CHAIN(7406, 1000000005, 4359)), NULL); -- Now, actually perform the transformation: set numformat 999999.99999999 -- Create the source table CREATE TABLE source_geoms ( mkt_id NUMBER PRIMARY KEY, name VARCHAR2(32), GEOMETRY SDO_GEOMETRY); INSERT INTO source_geoms VALUES( 1, 'reference geom', SDO_GEOMETRY( 3001, 7406, SDO_POINT_TYPE( -161, 18, 0), NULL, NULL)); INSERT INTO USER_SDO_GEOM_METADATA VALUES ( 'source_geoms', 'GEOMETRY', SDO_DIM_ARRAY( SDO_DIM_ELEMENT('Longitude', -180, 180, 10), SDO_DIM_ELEMENT('Latitude', -90, 90, 10), SDO_DIM_ELEMENT('Height', -100, 100, 10)), 7406); commit; SELECT GEOMETRY "Source" FROM source_geoms; -------------------------------------------------------------------------------- --Perform the transformation: CALL SDO_CS.TRANSFORM_LAYER( 'source_geoms', 'GEOMETRY', 'GEO_CS_4359', 4359); INSERT INTO USER_SDO_GEOM_METADATA VALUES ( 'GEO_CS_4359', 'GEOMETRY', SDO_DIM_ARRAY( SDO_DIM_ELEMENT('Longitude', -180, 180, 10), SDO_DIM_ELEMENT('Latitude', -90, 90, 10), SDO_DIM_ELEMENT('Height', -100, 100, 10)), 4359); set lines 210; SELECT GEOMETRY "Target" FROM GEO_CS_4359; -------------------------------------------------------------------------------- --Transform back: CALL SDO_CS.TRANSFORM_LAYER( 'GEO_CS_4359', 'GEOMETRY', 'source_geoms2', 7406); INSERT INTO USER_SDO_GEOM_METADATA VALUES ( 'source_geoms2', 'GEOMETRY', SDO_DIM_ARRAY( SDO_DIM_ELEMENT('Longitude', -180, 180, 10), SDO_DIM_ELEMENT('Latitude', -90, 90, 10), SDO_DIM_ELEMENT('Height', -100, 100, 10)), 7406); SELECT GEOMETRY "Source2" FROM source_geoms2; -------------------------------------------------------------------------------- --Clean up (regarding the transformation): DELETE FROM USER_SDO_GEOM_METADATA WHERE table_name = 'GEO_CS_4359'; DELETE FROM USER_SDO_GEOM_METADATA WHERE table_name = 'SOURCE_GEOMS'; DELETE FROM USER_SDO_GEOM_METADATA WHERE table_name = 'SOURCE_GEOMS2'; drop table GEO_CS_4359; drop table source_geoms; drop table source_geoms2; --Clean up (regarding the rule): CALL sdo_cs.delete_op(300); delete from mdsys.sdo_coord_op_param_vals where coord_op_id = 1000000005; delete from mdsys.sdo_coord_ops where coord_op_id = 1000000005; COMMIT;
With the configuration in Example 6-3:
Without the rule, (-161.00000000, 18.00000000, .00000000) is transformed to (-161.00127699, 18.00043360, 62.03196364), based simply on a datum transformation.
With the rule, (-161.00000000, 18.00000000, .00000000) is transformed to (-161.00000000, 18.00000000, 6.33070000).
You cannot directly perform a cross-dimensionality transformation (for example, from a two-dimensional geometry to a three-dimensional geometry) using the SDO_CS.TRANSFORM function or the SDO_CS.TRANSFORM_LAYER procedure. However, you can use the SDO_CS.MAKE_3D function to convert a two-dimensional geometry to a three-dimensional geometry, or the SDO_CS.MAKE_2D function to convert a three-dimensional geometry to a two-dimensional geometry; and you can use the resulting geometry to perform a transformation into a geometry with the desired number of dimensions.
For example, transforming a two-dimensional geometry into a three-dimensional geometry involves using the SDO_CS.MAKE_3D function. This function does not itself perform any coordinate transformation, but simply adds a height value and sets the target SRID. You must choose an appropriate target SRID, which should be the three-dimensional equivalent of the source SRID. For example, three-dimensional WGS 84 (4327) is the equivalent of two-dimensional WGS 84 (4326). If necessary, modify height values of vertices in the returned geometry.
There are many options for how to use the SDO_CS.MAKE_3D function, but the simplest is the following:
Transform from the two-dimensional source SRID to two-dimensional WGS 84 (4326).
Call SDO_CS.MAKE_3D to convert the geometry to three-dimensional WGS 84 (4327)
Transform from three-dimensional WGS 84 (4327) to the three-dimensional target SRID.
Example 6-4 transforms a two-dimensional point from SRID 27700 to two-dimensional SRID 4326, converts the result of the transformation to a three-dimensional point with SRID 4327, and transforms the converted point to three-dimensional SRID 4327.
Example 6-4 Cross-Dimensionality Transformation
SELECT SDO_CS.TRANSFORM( SDO_CS.MAKE_3D( SDO_CS.TRANSFORM( SDO_GEOMETRY( 2001, 27700, SDO_POINT_TYPE(577274.984, 69740.4923, NULL), NULL, NULL), 4326), height => 0, target_srid => 4327), 4327) "27700 > 4326 > 4327 > 4327" FROM DUAL; 27700 > 4326 > 4327 > 4327(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INF -------------------------------------------------------------------------------- SDO_GEOMETRY(3001, 4327, SDO_POINT_TYPE(.498364058, 50.5006366, 0), NULL, NULL)
There are two possible answers to the question What is 3D equivalent for the WGS 84 coordinate system? (that is, 2D Oracle SRID 8308 or EPSG SRID 4326):
4979 (in many or most cases), or
It depends on what you mean by height (for example, above ground level, above or below sea level, or something else).
There are many different height datums. Height can be relative to:
The ellipsoid, which requires the use of a coordinate system of type GEOGRAPHIC3d, for which SRID values 4327, 43229, and 4979 are predefined in Oracle Spatial and Graph.
A non-ellipsoidal height datum, which requires the use of a coordinate system of type COMPOUND, for which a custom SRID must usually be defined. The non-ellipsoidal height may be specified in relation to the geoid, to some local or mean sea level (or a network of local sea levels), or to some other definition of height (such as above ground surface).
To define a compound coordinate system (see Compound Coordinate Reference Systems) based on the two dimensions of the WGS 84 coordinate system, you must first select a predefined or custom vertical coordinate reference system (see Creating a Vertical CRS). To find the available vertical coordinate reference systems, enter the following statement:
SELECT srid, COORD_REF_SYS_NAME from sdo_coord_ref_sys WHERE COORD_REF_SYS_KIND = 'VERTICAL' order by srid; SRID COORD_REF_SYS_NAME ---------- --------------------------------------------------------------------- 3855 EGM2008 geoid height 3886 Fao 1979 height 4440 NZVD2009 height 4458 Dunedin-Bluff 1960 height 5600 NGPF height 5601 IGN 1966 height 5602 Moorea SAU 1981 height . . . 5795 Guadeloupe 1951 height 5796 Lagos 1955 height 5797 AIOC95 height 5798 EGM84 geoid height 5799 DVR90 height 123 rows selected.
After selecting a vertical coordinate reference system, create the compound SRID by entering a statement in the following form:
INSERT INTO sdo_coord_ref_system ( SRID, COORD_REF_SYS_NAME, COORD_REF_SYS_KIND, COORD_SYS_ID, DATUM_ID, GEOG_CRS_DATUM_ID, SOURCE_GEOG_SRID, PROJECTION_CONV_ID, CMPD_HORIZ_SRID, CMPD_VERT_SRID, INFORMATION_SOURCE, DATA_SOURCE, IS_LEGACY, LEGACY_CODE, LEGACY_WKTEXT, LEGACY_CS_BOUNDS, IS_VALID, SUPPORTS_SDO_GEOMETRY) values ( custom-SRID, 'custom-name', 'COMPOUND', NULL, NULL, 6326, NULL, NULL, 4326, vertical-SRID, 'custom-information-source', 'custom-data-source', 'FALSE', NULL, NULL, NULL, 'TRUE', 'TRUE');
You can check the definition, based on the generated WKT, by entering a statement in the following form:
SELECT wktext3d FROM cs_srs WHERE srid = custom-SRID;
WKTEXT3D
------------------------------------------------------------------------------
COMPD_CS[
"NTF (Paris) + NGF IGN69",
GEOGCS["NTF (Paris)",
DATUM["Nouvelle Triangulation Francaise (Paris)",
SPHEROID[
"Clarke 1880 (IGN)",
6378249.2,
293.4660212936293951,
AUTHORITY["EPSG", "7011"]],
TOWGS84[-168.0, -60.0, 320.0, 0.0, 0.0, 0.0, 0.0],
AUTHORITY["EPSG", "6807"]],
PRIMEM["Paris", 2.337229, AUTHORITY["EPSG","8903"]],
UNIT["grad", 0.015707963267949, AUTHORITY["EPSG", "9105"]],
AXIS["Lat", NORTH],
AXIS["Long", EAST],
AUTHORITY["EPSG", "4807"]],
VERT_CS["NGF IGN69",
VERT_DATUM["Nivellement general de la France - IGN69", 2005,
AUTHORITY["EPSG", "5119"]],
UNIT["metre", 1.0, AUTHORITY["EPSG", "9001"]],
AXIS["H", UP],
AUTHORITY["EPSG", "5720"]],
AUTHORITY["EPSG","7400"]]
When transforming between different height datums, you might use a VERTCON matrix. For example, between the WGS 84 ellipsoid and geoid, there is an offset matrix that allows height transformation. For more information, see the following: