| Oracle® Spatial User's Guide and Reference 10g Release 2 (10.2) Part Number B14255-01 |
|
|
View PDF |
| Oracle® Spatial User's Guide and Reference 10g Release 2 (10.2) Part Number B14255-01 |
|
|
View PDF |
This chapter describes in greater detail the Oracle Spatial coordinate system support, which was introduced in Section 1.5.4. You can store and manipulate SDO_GEOMETRY objects in a variety of coordinate systems.
For reference information about coordinate system transformation functions and procedures in the MDSYS.SDO_CS package, see Chapter 13.
This chapter contains the following major sections:
Section 6.8, "Creating a User-Defined Coordinate Reference System"
Section 6.9, "Notes and Restrictions with Coordinate Systems Support"
This section explains important terms and concepts related to coordinate system support in Oracle Spatial.
A coordinate system (also called a spatial reference system) is a means of assigning coordinates to a location and establishing relationships between sets of such coordinates. It enables the interpretation of a set of coordinates as a representation of a position in a real world space.
The term coordinate reference system has the same meaning as coordinate system for Spatial, and the terms are used interchangeably. European Petroleum Survey Group (EPSG) specifications and documentation typically use the term coordinate reference system. (EPSG has its own meaning for the term coordinate system, as noted in Section 6.6.11.)
Cartesian coordinates are coordinates that measure the position of a point from a defined origin along axes that are perpendicular in the represented two-dimensional or three-dimensional space.
Geodetic coordinates (sometimes called geographic coordinates) are angular coordinates (longitude and latitude), closely related to spherical polar coordinates, and are defined relative to a particular Earth geodetic datum (described in Section 6.1.6). For more information about geodetic coordinate support, see Section 6.2.
Projected coordinates are planar Cartesian coordinates that result from performing a mathematical mapping from a point on the Earth's surface to a plane. There are many such mathematical mappings, each used for a particular purpose.
Local coordinates are Cartesian coordinates in a non-Earth (non-georeferenced) coordinate system. Section 6.3 describes local coordinate support in Spatial.
A geodetic datum (or datum) is a means of shifting and rotating an ellipsoid to represent the figure of the Earth, usually as an oblate spheroid, that approximates the surface of the Earth locally or globally, and is the reference for the system of geodetic coordinates.
Each geodetic coordinate system is based on a datum.
Transformation is the conversion of coordinates from one coordinate system to another coordinate system.
If the coordinate system is georeferenced, transformation can involve datum transformation: the conversion of geodetic coordinates from one geodetic datum to another geodetic datum, usually involving changes in the shape, orientation, and center position of the reference ellipsoid.
Effective with Oracle9i, Spatial provides a rational and complete treatment of geodetic coordinates. Before Oracle9i, Spatial computations were based solely on flat (Cartesian) coordinates, regardless of the coordinate system specified for the layer of geometries. Consequently, computations for data in geodetic coordinate systems were inaccurate, because they always treated the coordinates as if they were on a flat surface, and they did not consider the curvature of the surface.
Effective with release 9.2, ellipsoidal surface computations consider the curvatures of the Earth in the specified geodetic coordinate system and return correct, accurate results. In other words, Spatial queries return the right answers all the time.
A two-dimensional geometry is a surface geometry, but the important question is: What is the surface? A flat surface (plane) is accurately represented by Cartesian coordinates. However, Cartesian coordinates are not adequate for representing the surface of a solid. A commonly used surface for spatial geometry is the surface of the Earth, and the laws of geometry there are different than they are in a plane. For example, on the Earth's surface there are no parallel lines: lines are geodesics, and all geodesics intersect. Thus, closed curved surface problems cannot be done accurately with Cartesian geometry.
Spatial provides accurate results regardless of the coordinate system or the size of the area involved, without requiring that the data be projected to a flat surface. The results are accurate regardless of where on the Earth's surface the query is focused, even in "special" areas such as the poles. Thus, you can store coordinates in any datum and projections that you choose, and you can perform accurate queries regardless of the coordinate system.
For applications that deal with the Earth's surface, the data can be represented using a geodetic coordinate system or a projected plane coordinate system. In deciding which approach to take with the data, consider any needs related to accuracy and performance:
Accuracy
For many spatial applications, the area is sufficiently small to allow adequate computations on Cartesian coordinates in a local projection. For example, the New Hampshire State Plane local projection provides adequate accuracy for most spatial applications that use data for that state.
However, Cartesian computations on a plane projection will never give accurate results for a large area such as Canada or Scandinavia. For example, a query asking if Stockholm, Sweden and Helsinki, Finland are within a specified distance may return an incorrect result if the specified distance is close to the actual measured distance. Computations involving large areas or requiring very precise accuracy must account for the curvature of the Earth's surface.
Performance
Spherical computations use more computing resources than Cartesian computations. Some operations using geodetic coordinates may take longer to complete than the same operations using Cartesian coordinates.
To create a query window for certain operations on geodetic data, use an MBR (minimum bounding rectangle) by specifying an SDO_ETYPE value of 1003 or 2003 and an SDO_INTERPRETATION value of 3, as described in Table 2-2 in Section 2.2.4. A geodetic MBR can be used with the following operators: SDO_FILTER, SDO_RELATE with the ANYINTERACT mask, SDO_ANYINTERACT, and SDO_WITHIN_DISTANCE.
Example 6-1 requests the names of all cola markets that are likely to interact spatially with a geodetic MBR.
Example 6-1 Using a Geodetic MBR
SELECT c.name FROM cola_markets_cs c WHERE
SDO_FILTER(c.shape,
SDO_GEOMETRY(
2003,
8307, -- SRID for WGS 84 longitude/latitude
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,3),
SDO_ORDINATE_ARRAY(6,5, 10,10))
) = 'TRUE';
Example 6-1 produces the following output (assuming the data as defined in Example 6-7 in Section 6.11):
NAME -------------------------------- cola_c cola_b cola_d
The following considerations apply to the use of geodetic MBRs:
Do not use a geodetic MBR with spatial objects stored in the database. Use it only to construct a query window.
The lower-left Y coordinate (minY) must be less than the upper-right Y coordinate (maxY). If the lower-left X coordinate (minX) is greater than the upper-right X coordinate (maxX), the window is assumed to cross the date line meridian (that is, the meridian "opposite" the prime meridian, or both 180 and -180 longitude). For example, an MBR of (-10,10, -100, 20) with longitude/latitude data goes three-fourths of the way around the Earth (crossing the date line meridian), and goes from latitude lines 10 to 20.
When Spatial constructs the MBR internally for the query, lines along latitude lines are densified by adding points at one-degree intervals. This might affect results for objects within a few meters of the edge of the MBR (especially objects in the middle latitudes in both hemispheres).
The following additional examples show special or unusual cases, to illustrate how a geodetic MBR is interpreted with longitude/latitude data:
(10,0, -110,20) crosses the date line meridian and goes most of the way around the world, and goes from the equator to latitude 20.
(10,-90, 40,90) is a band from the South Pole to the North Pole between longitudes 10 and 40.
(10,-90, 40,50) is a band from the South Pole to latitude 50 between longitudes 10 and 40.
(-180,-10, 180,5) is a band that wraps the equator from 10 degrees south to 5 degrees north.
(-180,-90, 180,90) is the whole Earth.
(-180,-90, 180,50) is the whole Earth below latitude 50.
(-180,50, 180,90) is the whole Earth above latitude 50.
The following geometries are not permitted if a geodetic coordinate system is used:
Circles
Circular arcs
Geodetic coordinate system support is provided only for geometries that consist of points or geodesics (lines on the ellipsoid). If you have geometries containing circles or circular arcs in a projected coordinate system, you can densify them using the SDO_GEOM.SDO_ARC_DENSIFY function (documented in Chapter 15) before transforming them to geodetic coordinates, and then perform Spatial operations on the resulting geometries.
The following size limits apply with geodetic data:
No polygon element can have an area larger than one-half the surface of the Earth.
In a line, the distance between two adjacent coordinates cannot be greater than or equal to one-half the perimeter (a great circle) of the Earth.
If you need to work with larger elements, first break these elements into multiple smaller elements and work with them. For example, you cannot create a geometry representing the entire ocean surface of the Earth; however, you can create multiple geometries, each representing part of the overall ocean surface. To work with a line string that is greater than or equal to one-half the perimeter of the Earth, you can add one or more intermediate points on the line so that all adjacent coordinates are less than one-half the perimeter of the Earth.
To take full advantage of Spatial features, you must index geodetic data layers using a geodetic R-tree index. (You can create a non-geodetic R-tree or quadtree index on geodetic data by specifying 'geodetic=FALSE' in the PARAMETERS clause of the CREATE INDEX statement; however, this is not recommended. See the Usage Notes for the CREATE INDEX statement in Chapter 10 for more information.) In addition, for Spatial release 9.0.1 and later you must delete (DROP INDEX) and re-create all spatial indexes on geodetic data from a release before 9.0.1.
Tolerance is specified as meters for geodetic layers. If you use tolerance values that are typical for non-geodetic data, these values are interpreted as meters for geodetic data. For example, if you specify a tolerance value of 0.05 for geodetic data, this is interpreted as precise to 5 centimeters. If this value is more precise than your applications need, performance may be affected because of the internal computational steps taken to implement the specified precision. (For more information about tolerance, see Section 1.5.5.)
For geodetic layers, you must specify the dimensional extents in the index metadata as -180,180 for longitude and -90,90 for latitude. The following statement (from Example 6-7 in Section 6.11) specifies these extents (with a 10-meter tolerance value in each dimension) for a geodetic data layer:
INSERT INTO user_sdo_geom_metadata
(TABLE_NAME,
COLUMN_NAME,
DIMINFO,
SRID)
VALUES (
'cola_markets_cs',
'shape',
SDO_DIM_ARRAY(
SDO_DIM_ELEMENT('Longitude', -180, 180, 10), -- 10 meters tolerance
SDO_DIM_ELEMENT('Latitude', -90, 90, 10) -- 10 meters tolerance
),
8307 -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
);
See Section 6.9 for additional notes and restrictions relating to geodetic data.
Spatial provides a level of support for local coordinate systems. Local coordinate systems are often used in CAD systems, and they can also be used in local surveys where the relationship between the surveyed site and the rest of the world is not important.
Several local coordinate systems are predefined and included with Spatial in the SDO_COORD_REF_SYS table (described in Section 6.6.9). These supplied local coordinate systems, whose names start with Non-Earth, define non-Earth Cartesian coordinate systems based on different units of measurement (Meter, Millimeter, Inch, and so on).
In the current release, you cannot perform coordinate system transformation between local and Earth-based coordinate systems; and when transforming a geometry or layer of geometries between local coordinate systems, you can only to convert coordinates in a local coordinate system from one unit of measurement to another (for example, inches to millimeters). However, you can perform all other Spatial operations (for example, using SDO_RELATE, SDO_WITHIN_DISTANCE, and other operators) with local coordinate systems.
Effective with Oracle Database 10g release 2 (10.2), the Oracle Spatial coordinate system support is based on, but is not always identical to, the European Petroleum Survey Group (EPSG) data model and data set (described in detail at http://www.epsg.org). This approach provides the benefits of standardization, expanded support, and flexibility:
The EPSG model is a comprehensive and widely accepted standard for data representation, so users familiar with it can more easily understand Spatial storage and operations.
Support is provided for more coordinate systems and their associated datums, ellipsoids, and projections. For example, some of the EPSG geographic and projected coordinate systems had no counterpart among coordinate systems supported for previous Spatial releases. Their addition results in an expanded set of supported coordinate systems.
Data transformations are more flexible. Instead of there being only one possible Oracle-defined transformation path between a given source and target coordinate system, you can specify alternative paths to be used for a specific area of applicability (that is, use case) or as the systemwide default.
The rest of this section describes this flexibility.
For data transformations (that is, transforming data from one coordinate system to another), you can now control which transformation rules are to be applied. In previous releases, and in the current release by default, Spatial performs transformations based only on the specified source and target coordinate systems, using predetermined intermediate transformation steps. The assumption behind that default approach is that there is a single correct or preferable transformation chain.
By default, then, Spatial applies certain transformation methods for each supported transformation between specific pairs of source and target coordinate systems. For example, there are over 500 supported transformations from specific coordinate systems to the WGS 84 (longitude/latitude) coordinate system, which has the EPSG SRID value of 4326. As one example, for a transformation from SRID 4605 to SRID 4326, Spatial can use the transformation method with the COORD_OP_ID value 1445, as indicated in the SDO_COORD_OPS table (described in Section 6.6.8), which contains one row for each transformation operation between coordinate systems.
However, you can override the default transformation by specifying a different method (from the set of Oracle-supplied methods) for the transformation for any given source and target SRID combination. You can specify a transformation as the new systemwide default, or you can associate the transformation with a named use case that can be specified when transforming a layer of spatial geometries. (A use case is simply a name given to a usage scenario or area of applicability, such as Project XYZ or Mike's Favorite Transformations; there is no relationship between use cases and database users or schemas.)
To specify a transformation as either the systemwide default or associated with a use case, use the SDO_CS.ADD_PREFERENCE_FOR_OP procedure. To remove a previously specified preference, use the SDO_CS.REVOKE_PREFERENCE_FOR_OP procedure.
When it performs a coordinate system transformation, Spatial follows these general steps to determine the specific transformation to use:
If a use case has been specified, the transformation associated with that use case is applied.
If no use case has been specified and if a user-defined systemwide transformation has been created for the specified source and target coordinate system pair, that transformation is applied.
If no use case has been specified and if no user-defined transformation exists for the specified source and target coordinate system pair, the behavior depends on whether or not EPSG rules have been created, such as by the SDO_CS.CREATE_OBVIOUS_EPSG_RULES procedure:
If the EPSG rules have been created and if an EPSG rule is defined for this transformation, the EPSG transformation is applied.
If the EPSG rules have not been created, or if they have been created but no EPSG rule is defined for this transformation, the Oracle Spatial default transformation is applied.
The object type TFM_PLAN is used is by several SDO_CS package subprograms to specify a transformation plan. For example, to create a concatenated operation that consists of two operations specified by a parameter of type TFM_PLAN, use the SDO_CS.CREATE_CONCATENATED_OP procedure.
Oracle Spatial defines the object type TFM_PLAN as:
CREATE TYPE tfm_plan AS OBJECT ( THE_PLAN SDO_TFM_CHAIN);
The SDO_TFM_CHAIN type is defined as VARRAY(1048576) OF NUMBER.
Within the SDO_TFM_CHAIN array:
The first element specifies the SRID of the source coordinate system.
Each pair of elements after the first element specifies an operation ID and the SRID of a target coordinate system.
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. The definitions and data in these tables and views are based on the EPSG data model and data set, as explained in Section 6.4.
The coordinate system tables fit into several general categories:
Coordinate system general information: SDO_COORD_SYS, SDO_COORD_REF_SYS
Elements or aspects of a coordinate system definition: SDO_DATUMS, SDO_ELLIPSOIDS, SDO_PRIME_MERIDIANS
Datum transformation support: SDO_COORD_OPS, SDO_COORD_OP_METHODS, SDO_COORD_OP_PARAM_USE, SDO_COORD_OP_PARAM_VALS, SDO_COORD_OP_PARAMS, SDO_COORD_OP_PATHS, SDO_PREFERRED_OPS_SYSTEM, SDO_PREFERRED_OPS_USER
Others related to coordinate system definition: SDO_COORD_AXES, SDO_COORD_AXIS_NAMES, SDO_UNITS_OF_MEASURE
Several views are provided that are identical to or subsets of coordinate system tables:
SDO_COORD_REF_SYSTEM, which contains the same columns as the SDO_COORD_REF_SYS table. Use the SDO_COORD_REF_SYSTEM view instead of the COORD_REF_SYS table for any insert, update, or delete operations.
Subsets of SDO_DATUMS, selected according to the value in the DATUM_TYPE column: SDO_DATUM_ENGINEERING, SDO_DATUM_GEODETIC, SDO_DATUM_VERTICAL.
Subsets of SDO_COORD_REF_SYS, selected according to the value in the COORD_REF_SYS_KIND column: SDO_CRS_COMPOUND, SDO_CRS_ENGINEERING, SDO_CRS_GEOCENTRIC, SDO_CRS_GEOGRAPHIC2D, SDO_CRS_GEOGRAPHIC3D, SDO_CRS_PROJECTED, SDO_CRS_VERTICAL.
The rest of this section explains these tables and views, in alphabetical order. (Many column descriptions are adapted or taken from EPSG descriptions.)
In addition to the tables and views in this section, Spatial provides several legacy tables whose definitions and data match those of certain Spatial system tables used in previous releases. Section 6.7 describes the legacy tables.
|
Note: You should not modify or delete any Oracle-supplied information in any of the tables or views that are used for coordinate system support.If you want to create a user-defined coordinate system, see Section 6.8. |
The SDO_COORD_AXES table contains one row for each coordinate system axis definition. This table contains the columns shown in Table 6-1.
Table 6-1 SDO_COORD_AXES Table
| Column Name | Data Type | Description |
|---|---|---|
|
COORD_SYS_ID |
NUMBER(10) |
ID number of the coordinate system to which this axis applies. |
|
COORD_AXIS_NAME_ID |
NUMBER(10) |
ID number of a coordinate system axis name. Matches a value in the COORD_AXIS_NAME_ID column of the SDO_COORD_AXIS_NAMES table (described in Section 6.6.2). Example: |
|
COORD_AXIS_ORIENTATION |
VARCHAR2(24) |
The direction of orientation for the coordinate system axis. Example: |
|
COORD_AXIS_ABBREVIATION |
VARCHAR2(24) |
The abbreviation for the coordinate system axis orientation. Example: |
|
UOM_ID |
NUMBER(10) |
ID number of the unit of measure associated with the axis. Matches a value in the UOM_ID column of the SDO_UNITS_OF_MEASURE table (described in Section 6.6.27). |
|
ORDER |
NUMBER(10) |
Position of this axis within the coordinate system (1, 2, or 3). |
The SDO_COORD_AXIS_NAMES table contains one row for each axis that can be used in a coordinate system definition. This table contains the columns shown in Table 6-2.
The SDO_COORD_OP_METHODS table contains one row for each coordinate systems transformation method. This table contains the columns shown in Table 6-3.
Table 6-3 SDO_COORD_OP_METHODS Table
| Column Name | Data Type | Description |
|---|---|---|
|
COORD_OP_METHOD_ID |
NUMBER(10) |
ID number of the coordinate system transformation method. Example: |
|
COORD_OP_METHOD_NAME |
VARCHAR2(50) |
Name of the method. Example: |
|
REVERSE_OP |
NUMBER(1) |
Contains |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Origin of this information. Example: |
|
DATA_SOURCE |
VARCHAR2(40) |
Organization providing the data for this record. Example: |
The SDO_COORD_OP_PARAM_USE table contains one row for each combination of transformation method and transformation operation parameter that is available for use. This table contains the columns shown in Table 6-4.
Table 6-4 SDO_COORD_OP_PARAM_USE Table
| Column Name | Data Type | Description |
|---|---|---|
|
COORD_OP_METHOD_ID |
NUMBER(10) |
ID number of the coordinate system transformation method. Matches a value in the COORD_OP_METHOD_ID column of the COORD_OP_METHODS table (described in Section 6.6.3). |
|
PARAMETER_ID |
NUMBER(10) |
ID number of the parameter for transformation operations. Matches a value in the PARAMETER_ID column of the SDO_COORD_OP_PARAMS table (described in Section 6.6.6). |
|
SORT_ORDER |
NUMBER(5) |
A number indicating the position of this parameter in the sequence of parameters for this method. Example: |
|
PARAM_SIGN_REVERSAL |
VARCHAR2(3) |
|
The SDO_COORD_OP_PARAM_VALS table contains information about parameter values for each coordinate system transformation method. This table contains the columns shown in Table 6-5.
Table 6-5 SDO_COORD_OP_PARAM_VALS Table
| Column Name | Data Type | Description |
|---|---|---|
|
COORD_OP_ID |
NUMBER(10) |
ID number of the coordinate transformation operation. Matches a value in the COORD_OP_ID column of the SDO_COORD_OPS table (described in Section 6.6.8). |
|
COORD_OP_METHOD_ID |
NUMBER(10) |
Coordinate operation method ID. Must match a COORD_OP_METHOD_ID value in the SDO_COORD_OP_METHODS table (see Section 6.6.3). |
|
PARAMETER_ID |
NUMBER(10) |
ID number of the parameter for transformation operations. Matches a value in the PARAMETER_ID column of the SDO_COORD_OP_PARAMS table (described in Section 6.6.6). |
|
PARAMETER_VALUE |
FLOAT(49) |
Value of the parameter for this operation. |
|
PARAM_VALUE_FILE_REF |
VARCHAR2(254) |
Name of the file containing the value data, if a single value for the parameter is not sufficient. |
|
UOM_ID |
NUMBER(10) |
ID number of the unit of measure associated with the operation. Matches a value in the UOM_ID column of the SDO_UNITS_OF_MEASURE table (described in Section 6.6.27). |
The SDO_COORD_OP_PARAMS table contains one row for each available parameter for transformation operations. This table contains the columns shown in Table 6-6.
Table 6-6 SDO_COORD_OP_PARAMS Table
| Column Name | Data Type | Description |
|---|---|---|
|
PARAMETER_ID |
NUMBER(10) |
ID number of the parameter. Example: |
|
PARAMETER_NAME |
VARCHAR2(80) |
Name of the operation. Example: |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Origin of this information. Example: |
|
DATA_SOURCE |
VARCHAR2(40) |
Organization providing the data for this record. Example: |
The SDO_COORD_OP_PATHS table contains one row for each atomic step in a concatenated operation. This table contains the columns shown in Table 6-7.
Table 6-7 SDO_COORD_OP_PATHS Table
| Column Name | Data Type | Description |
|---|---|---|
|
CONCAT_OPERATION_ID |
NUMBER(10) |
ID number of the concatenation operation. Must match a COORD_OP_ID value in the SDO_COORD_OPS table (described in Section 6.6.8) for which the COORD_OP_TYPE value is |
|
SINGLE_OPERATION_ID |
NUMBER(10) |
ID number of the single coordinate operation for this step (atomic operation) in a concatenated operation. Must match a COORD_OP_ID value in the SDO_COORD_OPS table (described in Section 6.6.8). |
|
SINGLE_OP_SOURCE_ID |
NUMBER(10) |
ID number of source coordinate reference system for the single coordinate operation for this step. Must match an SRID value in the SDO_COORD_REF_SYS table (described in Section 6.6.9). |
|
SINGLE_OP_TARGET_ID |
NUMBER(10) |
ID number of target coordinate reference system for the single coordinate operation for this step. Must match an SRID value in the SDO_COORD_REF_SYS table (described in Section 6.6.9). |
|
OP_PATH_STEP |
NUMBER(5) |
Sequence number of this step (atomic operation) within this concatenated operation. |
The SDO_COORD_OPS table contains one row for each transformation operation between coordinate systems. This table contains the columns shown in Table 6-8.
Table 6-8 SDO_COORD_OPS Table
| Column Name | Data Type | Description |
|---|---|---|
|
COORD_OP_ID |
NUMBER(10) |
ID number of the coordinate transformation operation. Example: |
|
COORD_OP_NAME |
VARCHAR2(80) |
Name of the operation. Example: |
|
COORD_OP_TYPE |
VARCHAR2(24) |
Type of operation. One of the following: |
|
SOURCE_SRID |
NUMBER(10) |
SRID of the coordinate system from which to perform the transformation. Example: |
|
TARGET_SRID |
NUMBER(10) |
SRID of the coordinate system into which to perform the transformation. Example: |
|
COORD_TFM_VERSION |
VARCHAR2(24) |
Name assigned by EPSG to the coordinate transformation. Example: |
|
COORD_OP_VARIANT |
NUMBER(5) |
A variant of the more generic method specified in COORD_OP_METHOD_ID. Example: |
|
COORD_OP_METHOD_ID |
NUMBER(10) |
Coordinate operation method ID. Must match a COORD_OP_METHOD_ID value in the SDO_COORD_OP_METHODS table (see Section 6.6.3). Several operations can use a method. Example: |
|
UOM_ID_SOURCE_OFFSETS |
NUMBER(10) |
ID number of the unit of measure for offsets in the source coordinate system. Matches a value in the UOM_ID column of the SDO_UNITS_OF_MEASURE table (described in Section 6.6.27). |
|
UOM_ID_TARGET_OFFSETS |
NUMBER(10) |
ID number of the unit of measure for offsets in the target coordinate system. Matches a value in the UOM_ID column of the SDO_UNITS_OF_MEASURE table (described in Section 6.6.27). |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Origin of this information. Example: |
|
DATA_SOURCE |
VARCHAR2(40) |
Organization providing the data for this record. Example: |
|
SHOW_OPERATION |
NUMBER(3) |
(Currently not used.) |
|
IS_LEGACY |
VARCHAR2(5) |
TRUE if the operation was included in Oracle Spatial before release 10.2; FALSE if the operation is new in Oracle Spatial release 10.2. |
|
LEGACY_CODE |
NUMBER(10) |
For any EPSG coordinate transformation operation that has a semantically identical legacy (in Oracle Spatial before release 10.2) counterpart, the COORD_OP_ID value of the legacy coordinate transformation operation. |
|
REVERSE_OP |
NUMBER(1) |
Contains |
|
IS_IMPLEMENTED_FORWARD |
NUMBER(1) |
Contains |
|
IS_IMPLEMENTED_REVERSE |
NUMBER(1) |
Contains |
The SDO_COORD_REF_SYS table contains one row for each coordinate reference system. This table contains the columns shown in Table 6-9. (The SDO_COORD_REF_SYS table is roughly patterned after the EPSG Coordinate Reference System table.)
|
Note: If you need to perform an insert, update, or delete operation, you must perform it on the SDO_COORD_REF_SYSTEM view, which contains the same columns as the SDO_COORD_REF_SYS table. The SDO_COORD_REF_SYSTEM view is described in Section 6.6.10. |
Table 6-9 SDO_COORD_REF_SYS Table
| Column Name | Data Type | Description |
|---|---|---|
|
SRID |
NUMBER(10) |
ID number of the coordinate reference system. Example: |
|
COORD_REF_SYS_NAME |
VARCHAR2(80) |
Name of the coordinate reference system. Example: |
|
COORD_REF_SYS_KIND |
VARCHAR2(24) |
Category for the coordinate system. Example: |
|
COORD_SYS_ID |
NUMBER(10) |
ID number of the coordinate system used for the coordinate reference system. Must match a COORD_SYS_ID value in the SDO_COORD_SYS table (see Section 6.6.11). |
|
DATUM_ID |
NUMBER(10) |
ID number of the datum used for the coordinate reference system. Null for a projected coordinate system. For a geodetic coordinate system, must match a DATUM_ID value in the SDO_DATUMS table (see Section 6.6.22). Example: |
|
GEOG_CRS_DATUM_ID |
NUMBER(10) |
ID number of the datum used for the coordinate reference system. For a projected coordinate system, must match the DATUM_ID value (in the SDO_DATUMS table, described in Section 6.6.22) of the geodetic coordinate system on which the projected coordinate system is based. For a geodetic coordinate system, must match the DATUM_ID value. Example: |
|
SOURCE_GEOG_SRID |
NUMBER(10) |
For a projected coordinate reference system, the ID number for the associated geodetic coordinate system. |
|
PROJECTION_CONV_ID |
NUMBER(10) |
For a projected coordinate reference system, the COORD_OP_ID value of the conversion operation used to convert the projected coordinated system to and from the source geographic coordinate system. |
|
CMPD_HORIZ_SRID |
NUMBER(10) |
(EPSG-assigned value; not used by Oracle Spatial. The EPSG description is: "For compound CRS only, the code of the horizontal component of the Compound CRS.") |
|
CMPD_VERT_SRID |
NUMBER(10) |
(EPSG-assigned value; not used by Oracle Spatial. The EPSG description is: "For compound CRS only, the code of the vertical component of the Compound CRS.") |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Provider of the definition for the coordinate system ( |
|
DATA_SOURCE |
VARCHAR2(40) |
Organization that supplied the data for this record (if not Oracle). |
|
IS_LEGACY |
VARCHAR2(5) |
|
|
LEGACY_CODE |
NUMBER(10) |
For any EPSG coordinate reference system that has a semantically identical legacy (in Oracle Spatial before release 10.2) counterpart, the SRID value of the legacy coordinate system. |
|
LEGACY_WKTEXT |
VARCHAR2(2046) |
If IS_LEGACY is |
|
LEGACY_CS_BOUNDS |
SDO_GEOMETRY |
For a legacy coordinate system, the dimensional boundary (if any). |
|
IS_VALID |
VARCHAR2(5) |
|
|
SUPPORTS_SDO_GEOMETRY |
VARCHAR2(5) |
|
See also the information about the following views that are defined based on the value of the COORD_REF_SYS_KIND column:
SDO_CRS_COMPOUND (Section 6.6.12)
SDO_CRS_ENGINEERING (Section 6.6.13)
SDO_CRS_GEOCENTRIC (Section 6.6.14)
SDO_CRS_GEOGRAPHIC2D (Section 6.6.15)
SDO_CRS_GEOGRAPHIC3D (Section 6.6.16)
SDO_CRS_PROJECTED (Section 6.6.17)
SDO_CRS_VERTICAL (Section 6.6.18)
The SDO_COORD_REF_SYSTEM view contains the same columns as the SDO_COORD_REF_SYS table, which is described in Section 6.6.9. However, the SDO_COORD_REF_SYSTEM view has a trigger defined on it, so that any insert, update, or delete operations performed on the view cause all relevant Spatial system tables to have the appropriate operations performed on them.
Therefore, if you need to perform an insert, update, or delete operation, you must perform it on the SDO_COORD_REF_SYSTEM view, not the SDO_COORD_REF_SYS table.
The SDO_COORD_SYS table contains rows with information about coordinate systems. This table contains the columns shown in Table 6-10. (The SDO_COORD_SYS table is roughly patterned after the EPSG Coordinate System table, where a coordinate system is described as "a pair of reusable axes.")
Table 6-10 SDO_COORD_SYS Table
| Column Name | Data Type | Description |
|---|---|---|
|
COORD_SYS_ID |
NUMBER(10) |
ID number of the coordinate system. Example: |
|
COORD_SYS_NAME |
VARCHAR2(254) |
Name of the coordinate system. Example: |
|
COORD_SYS_TYPE |
VARCHAR2(24) |
Type of coordinate system. Example: |
|
DIMENSION |
NUMBER(5) |
Number of dimensions represented by the coordinate system. |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Origin of this information. |
|
DATA_SOURCE |
VARCHAR2(40) |
Organization providing the data for this record. |
The SDO_CRS_COMPOUND view contains selected information from the SDO_COORD_REF_SYS table (described in Section 6.6.9) where the COORD_REF_SYS_KIND column value is COMPOUND. This view contains the columns shown in Table 6-11.
Table 6-11 SDO_CRS_COMPOUND View
| Column Name | Data Type | Description |
|---|---|---|
|
SRID |
NUMBER(10) |
ID number of the coordinate reference system. |
|
COORD_REF_SYS_NAME |
VARCHAR2(80) |
Name of the coordinate reference system. |
|
CMPD_HORIZ_SRID |
NUMBER(10) |
(EPSG-assigned value; not used by Oracle Spatial. The EPSG description is: "For compound CRS only, the code of the horizontal component of the Compound CRS.") |
|
CMPD_VERT_SRID |
NUMBER(10) |
(EPSG-assigned value; not used by Oracle Spatial. The EPSG description is: "For compound CRS only, the code of the vertical component of the Compound CRS.") |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Provider of the definition for the coordinate system ( |
|
DATA_SOURCE |
VARCHAR2(40) |
Organization that supplied the data for this record (if not Oracle). |
The SDO_CRS_ENGINEERING view contains selected information from the SDO_COORD_REF_SYS table (described in Section 6.6.9) where the COORD_REF_SYS_KIND column value is ENGINEERING. This view contains the columns shown in Table 6-12.
Table 6-12 SDO_CRS_ENGINEERING View
| Column Name | Data Type | Description |
|---|---|---|
|
SRID |
NUMBER(10) |
ID number of the coordinate reference system. |
|
COORD_REF_SYS_NAME |
VARCHAR2(80) |
Name of the coordinate reference system. |
|
COORD_SYS_ID |
NUMBER(10) |
ID number of the coordinate system used for the coordinate reference system. Must match a COORD_SYS_ID value in the SDO_COORD_SYS table (see Section 6.6.11). |
|
DATUM_ID |
NUMBER(10) |
ID number of the datum used for the coordinate reference system. Must match a DATUM_ID value in the SDO_DATUMS table (see Section 6.6.22). Example: |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Provider of the definition for the coordinate system ( |
|
DATA_SOURCE |
VARCHAR2(40) |
Organization that supplied the data for this record (if not Oracle). |
The SDO_CRS_GEOCENTRIC view contains selected information from the SDO_COORD_REF_SYS table (described in Section 6.6.9) where the COORD_REF_SYS_KIND column value is GEOCENTRIC. This view contains the columns shown in Table 6-13.
Table 6-13 SDO_CRS_GEOCENTRIC View
| Column Name | Data Type | Description |
|---|---|---|
|
SRID |
NUMBER(10) |
ID number of the coordinate reference system. |
|
COORD_REF_SYS_NAME |
VARCHAR2(80) |
Name of the coordinate reference system. |
|
COORD_SYS_ID |
NUMBER(10) |
ID number of the coordinate system used for the coordinate reference system. Must match a COORD_SYS_ID value in the SDO_COORD_SYS table (see Section 6.6.11). |
|
DATUM_ID |
NUMBER(10) |
ID number of the datum used for the coordinate reference system. Must match a DATUM_ID value in the SDO_DATUMS table (see Section 6.6.22). Example: |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Provider of the definition for the coordinate system ( |
|
DATA_SOURCE |
VARCHAR2(40) |
Organization that supplied the data for this record (if not Oracle). |
The SDO_CRS_GEOGRAPHIC2D view contains selected information from the SDO_COORD_REF_SYS table (described in Section 6.6.9) where the COORD_REF_SYS_KIND column value is GEOGRAPHIC2D. This view contains the columns shown in Table 6-14.
Table 6-14 SDO_CRS_GEOGRAPHIC2D View
| Column Name | Data Type | Description |
|---|---|---|
|
SRID |
NUMBER(10) |
ID number of the coordinate reference system. |
|
COORD_REF_SYS_NAME |
VARCHAR2(80) |
Name of the coordinate reference system. |
|
COORD_SYS_ID |
NUMBER(10) |
ID number of the coordinate system used for the coordinate reference system. Must match a COORD_SYS_ID value in the SDO_COORD_SYS table (see Section 6.6.11). |
|
DATUM_ID |
NUMBER(10) |
ID number of the datum used for the coordinate reference system. Must match a DATUM_ID value in the SDO_DATUMS table (see Section 6.6.22). Example: |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Provider of the definition for the coordinate system ( |
|
DATA_SOURCE |
VARCHAR2(40) |
Organization that supplied the data for this record (if not Oracle). |
The SDO_CRS_GEOGRAPHIC3D view contains selected information from the SDO_COORD_REF_SYS table (described in Section 6.6.9) where the COORD_REF_SYS_KIND column value is GEOGRAPHIC3D. This view contains the columns shown in Table 6-15.
Table 6-15 SDO_CRS_GEOGRAPHIC3D View
| Column Name | Data Type | Description |
|---|---|---|
|
SRID |
NUMBER(10) |
ID number of the coordinate reference system. |
|
COORD_REF_SYS_NAME |
VARCHAR2(80) |
Name of the coordinate reference system. |
|
COORD_SYS_ID |
NUMBER(10) |
ID number of the coordinate system used for the coordinate reference system. Must match a COORD_SYS_ID value in the SDO_COORD_SYS table (see Section 6.6.11). |
|
DATUM_ID |
NUMBER(10) |
ID number of the datum used for the coordinate reference system. Must match a DATUM_ID value in the SDO_DATUMS table (see Section 6.6.22). Example: |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Provider of the definition for the coordinate system ( |
|
DATA_SOURCE |
VARCHAR2(40) |
Organization that supplied the data for this record (if not Oracle). |
The SDO_CRS_PROJECTED view contains selected information from the SDO_COORD_REF_SYS table (described in Section 6.6.9) where the COORD_REF_SYS_KIND column value is PROJECTED. This view contains the columns shown in Table 6-16.
Table 6-16 SDO_CRS_PROJECTED View
| Column Name | Data Type | Description |
|---|---|---|
|
SRID |
NUMBER(10) |
ID number of the coordinate reference system. |
|
COORD_REF_SYS_NAME |
VARCHAR2(80) |
Name of the coordinate reference system. |
|
COORD_SYS_ID |
NUMBER(10) |
ID number of the coordinate system used for the coordinate reference system. Must match a COORD_SYS_ID value in the SDO_COORD_SYS table (see Section 6.6.11). |
|
SOURCE_GEOG_SRID |
NUMBER(10) |
ID number for the associated geodetic coordinate system. |
|
PROJECTION_CONV_ID |
NUMBER(10) |
COORD_OP_ID value of the conversion operation used to convert the projected coordinated system to and from the source geographic coordinate system. |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Provider of the definition for the coordinate system ( |
|
DATA_SOURCE |
VARCHAR2(40) |
Organization that supplied the data for this record (if not Oracle). |
The SDO_CRS_VERTICAL view contains selected information from the SDO_COORD_REF_SYS table (described in Section 6.6.9) where the COORD_REF_SYS_KIND column value is VERTICAL. This view contains the columns shown in Table 6-17.
Table 6-17 SDO_CRS_VERTICAL View
| Column Name | Data Type | Description |
|---|---|---|
|
SRID |
NUMBER(10) |
ID number of the coordinate reference system. |
|
COORD_REF_SYS_NAME |
VARCHAR2(80) |
Name of the coordinate reference system. |
|
COORD_SYS_ID |
NUMBER(10) |
ID number of the coordinate system used for the coordinate reference system. Must match a COORD_SYS_ID value in the SDO_COORD_SYS table (see Section 6.6.11). |
|
DATUM_ID |
NUMBER(10) |
ID number of the datum used for the coordinate reference system. Must match a DATUM_ID value in the SDO_DATUMS table (see Section 6.6.22). Example: |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Provider of the definition for the coordinate system ( |
|
DATA_SOURCE |
VARCHAR2(40) |
Organization that supplied the data for this record (if not Oracle). |
The SDO_DATUM_ENGINEERING view contains selected information from the SDO_DATUMS table (described in Section 6.6.22) where the DATUM_TYPE column value is ENGINEERING. This view contains the columns shown in Table 6-18.
Table 6-18 SDO_DATUM_ENGINEERING View
| Column Name | Data Type | Description |
|---|---|---|
|
DATUM_ID |
NUMBER(10) |
ID number of the datum. |
|
DATUM_NAME |
VARCHAR2(80) |
Name of the datum. |
|
ELLIPSOID_ID |
NUMBER(10) |
ID number of the ellipsoid used in the datum definition. Must match an ELLIPSOID_ID value in the SDO_ELLIPSOIDS table (see Section 6.6.23). Example: |
|
PRIME_MERIDIAN_ID |
NUMBER(10) |
ID number of the prime meridian used in the datum definition. Must match a PRIME_MERIDIAN_ID value in the SDO_PRIME_MERIDIANS table (see Section 6.6.26). Example: |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Provider of the definition of the datum. Example: |
|
SHIFT_X |
NUMBER |
Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the x-axis. |
|
SHIFT_Y |
NUMBER |
Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the y-axis. |
|
SHIFT_Z |
NUMBER |
Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the z-axis. |
|
ROTATE_X |
NUMBER |
Number of arc-seconds of rotation about the x-axis. |
|
ROTATE_Y |
NUMBER |
Number of arc-seconds of rotation about the y-axis. |
|
ROTATE_Z |
NUMBER |
Number of arc-seconds of rotation about the z-axis. |
|
SCALE_ADJUST |
NUMBER |
A value to be used in adjusting the X, Y, and Z values after any shifting and rotation, according to the formula: 1.0 + (SCALE_ADJUST * 10-6) |
The SDO_DATUM_GEODETIC view contains selected information from the SDO_DATUMS table (described in Section 6.6.22) where the DATUM_TYPE column value is GEODETIC. This view contains the columns shown in Table 6-19.
Table 6-19 SDO_DATUM_GEODETIC View
| Column Name | Data Type | Description |
|---|---|---|
|
DATUM_ID |
NUMBER(10) |
ID number of the datum. |
|
DATUM_NAME |
VARCHAR2(80) |
Name of the datum. |
|
ELLIPSOID_ID |
NUMBER(10) |
ID number of the ellipsoid used in the datum definition. Must match an ELLIPSOID_ID value in the SDO_ELLIPSOIDS table (see Section 6.6.23). Example: |
|
PRIME_MERIDIAN_ID |
NUMBER(10) |
ID number of the prime meridian used in the datum definition. Must match a PRIME_MERIDIAN_ID value in the SDO_PRIME_MERIDIANS table (see Section 6.6.26). Example: |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Provider of the definition of the datum. Example: |
|
SHIFT_X |
NUMBER |
Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the x-axis. |
|
SHIFT_Y |
NUMBER |
Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the y-axis. |
|
SHIFT_Z |
NUMBER |
Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the z-axis. |
|
ROTATE_X |
NUMBER |
Number of arc-seconds of rotation about the x-axis. |
|
ROTATE_Y |
NUMBER |
Number of arc-seconds of rotation about the y-axis. |
|
ROTATE_Z |
NUMBER |
Number of arc-seconds of rotation about the z-axis. |
|
SCALE_ADJUST |
NUMBER |
A value to be used in adjusting the X, Y, and Z values after any shifting and rotation, according to the formula: 1.0 + (SCALE_ADJUST * 10-6) |
The SDO_DATUM_VERTICAL view contains selected information from the SDO_DATUMS table (described in Section 6.6.22) where the DATUM_TYPE column value is VERTICAL. This view contains the columns shown in Table 6-20.
Table 6-20 SDO_DATUM_VERTICAL View
| Column Name | Data Type | Description |
|---|---|---|
|
DATUM_ID |
NUMBER(10) |
ID number of the datum. |
|
DATUM_NAME |
VARCHAR2(80) |
Name of the datum. |
|
ELLIPSOID_ID |
NUMBER(10) |
ID number of the ellipsoid used in the datum definition. Must match an ELLIPSOID_ID value in the SDO_ELLIPSOIDS table (see Section 6.6.23). Example: |
|
PRIME_MERIDIAN_ID |
NUMBER(10) |
ID number of the prime meridian used in the datum definition. Must match a PRIME_MERIDIAN_ID value in the SDO_PRIME_MERIDIANS table (see Section 6.6.26). Example: |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Provider of the definition of the datum. Example: |
|
SHIFT_X |
NUMBER |
Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the x-axis. |
|
SHIFT_Y |
NUMBER |
Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the y-axis. |
|
SHIFT_Z |
NUMBER |
Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the z-axis. |
|
ROTATE_X |
NUMBER |
Number of arc-seconds of rotation about the x-axis. |
|
ROTATE_Y |
NUMBER |
Number of arc-seconds of rotation about the y-axis. |
|
ROTATE_Z |
NUMBER |
Number of arc-seconds of rotation about the z-axis. |
|
SCALE_ADJUST |
NUMBER |
A value to be used in adjusting the X, Y, and Z values after any shifting and rotation, according to the formula: 1.0 + (SCALE_ADJUST * 10-6) |
The SDO_DATUMS table contains one row for each datum. This table contains the columns shown in Table 6-21.
Table 6-21 SDO_DATUMS Table
| Column Name | Data Type | Description |
|---|---|---|
|
DATUM_ID |
NUMBER(10) |
ID number of the datum. Example: |
|
DATUM_NAME |
VARCHAR2(80) |
Name of the datum. Example: |
|
DATUM_TYPE |
VARCHAR2(24) |
Type of the datum. Example: |
|
ELLIPSOID_ID |
NUMBER(10) |
ID number of the ellipsoid used in the datum definition. Must match an ELLIPSOID_ID value in the SDO_ELLIPSOIDS table (see Section 6.6.23). Example: |
|
PRIME_MERIDIAN_ID |
NUMBER(10) |
ID number of the prime meridian used in the datum definition. Must match a PRIME_MERIDIAN_ID value in the SDO_PRIME_MERIDIANS table (see Section 6.6.26). Example: |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Provider of the definition of the datum. Example: |
|
DATA_SOURCE |
VARCHAR2(40) |
Organization that supplied the data for this record (if not Oracle). Example: |
|
SHIFT_X |
NUMBER |
Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the x-axis. |
|
SHIFT_Y |
NUMBER |
Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the y-axis. |
|
SHIFT_Z |
NUMBER |
Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the z-axis. |
|
ROTATE_X |
NUMBER |
Number of arc-seconds of rotation about the x-axis. |
|
ROTATE_Y |
NUMBER |
Number of arc-seconds of rotation about the y-axis. |
|
ROTATE_Z |
NUMBER |
Number of arc-seconds of rotation about the z-axis. |
|
SCALE_ADJUST |
NUMBER |
A value to be used in adjusting the X, Y, and Z values after any shifting and rotation, according to the formula: 1.0 + (SCALE_ADJUST * 10-6) |
|
IS_LEGACY |
VARCHAR2(5) |
|
|
LEGACY_CODE |
NUMBER(10) |
For any EPSG datum that has a semantically identical legacy (in Oracle Spatial before release 10.2) counterpart, the DATUM_ID value of the legacy datum. |
See also the information about the following views that are defined based on the value of the DATUM_TYPE column: SDO_DATUM_ENGINEERING (Section 6.6.19), SDO_DATUM_GEODETIC (Section 6.6.20), and SDO_DATUM_VERTICAL (Section 6.6.21).
The SDO_ELLIPSOIDS table contains one row for each ellipsoid. This table contains the columns shown in Table 6-22.
Table 6-22 SDO_ELLIPSOIDS Table
| Column Name | Data Type | Description |
|---|---|---|
|
ELLIPSOID_ID |
NUMBER(10) |
ID number of the ellipsoid (spheroid). Example: |
|
ELLIPSOID_NAME |
VARCHAR2(80) |
Name of the ellipsoid. Example: |
|
NUMBER |
Radius in meters along the semi-major axis (one-half of the long axis of the ellipsoid). |
|
|
UOM_ID |
NUMBER |
ID number of the unit of measure for the ellipsoid. Matches a value in the UOM_ID column of the SDO_UNITS_OF_MEASURE table (described in Section 6.6.27). Example: |
|
NUMBER |
Inverse flattening of the ellipsoid. That is, |
|
|
NUMBER |
Radius in meters along the semi-minor axis (one-half of the short axis of the ellipsoid). |
|
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Origin of this information. Example: |
|
DATA_SOURCE |
VARCHAR2(40) |
Organization that supplied the data for this record (if not Oracle). Example: |
|
IS_LEGACY |
VARCHAR2(5) |
|
|
LEGACY_CODE |
NUMBER |
For any EPSG ellipsoid that has a semantically identical legacy (in Oracle Spatial before release 10.2) counterpart, the ELLIPSOID_ID value of the legacy ellipsoid. |
The SDO_PREFERRED_OPS_SYSTEM table contains one row for each specification of the user-defined default preferred coordinate transformation operation for a source and target SRID combination. If you insert a row into the SDO_PREFERRED_OPS_SYSTEM table, you are overriding the Oracle default operation for transformations between the specified source and target coordinate systems. The SDO_CS.CREATE_OBVIOUS_EPSG_RULES procedure inserts many rows into this table. The SDO_CS.DELETE_ALL_EPSG_RULES procedure deletes all rows from this table if the use_case parameter is null. This table contains the columns shown in Table 6-23.
Table 6-23 SDO_PREFERRED_OPS_SYSTEM Table
| Column Name | Data Type | Description |
|---|---|---|
|
SOURCE_SRID |
NUMBER(10) |
ID number of the coordinate system (spatial reference system) from which to perform coordinate transformation, using the operation specified by COORD_OP_ID as the default preferred method for transforming to the specified target SRID. |
|
COORD_OP_ID |
NUMBER(10) |
ID number of the coordinate transformation operation. Matches a value in the COORD_OP_ID column of the SDO_COORD_OPS table (described in Section 6.6.8). |
|
TARGET_SRID |
NUMBER(10) |
ID number of coordinate system (spatial reference system) into which to perform coordinate transformation using the operation specified by COORD_OP_ID. |
The SDO_PREFERRED_OPS_USER table contains one row for each specification of a user-defined source and target SRID and coordinate transformation operation. If you insert a row into the SDO_PREFERRED_OPS_USER table, you create a custom transformation between the source and target coordinate systems, and you can specify the name (the USE_CASE column value) of the transformation operation as the use_case parameter value with several SDO_CS functions and procedures. If you specify a use case with the SDO_CS.DELETE_ALL_EPSG_RULES procedure, rows associated with that use case are deleted from this table. This table contains the columns shown in Table 6-24.
Table 6-24 SDO_PREFERRED_OPS_USER Table
| Column Name | Data Type | Description |
|---|---|---|
|
USE_CASE |
VARCHAR2(32) |
Name of this specification of a source and target SRID and coordinate transformation operation. |
|
SOURCE_SRID |
NUMBER(10) |
ID number of the coordinate system (spatial reference system) from which to perform the transformation. |
|
COORD_OP_ID |
NUMBER(10) |
ID number of the coordinate transformation operation. Matches a value in the COORD_OP_ID column of the SDO_COORD_OPS table (described in Section 6.6.8). |
|
TARGET_SRID |
NUMBER(10) |
ID number of the coordinate system (spatial reference system) into which to perform the transformation. |
The SDO_PRIME_MERIDIANS table contains one row for each prime meridian that can be used in a datum specification. This table contains the columns shown in Table 6-25.
Table 6-25 SDO_PRIME_MERIDIANS Table
| Column Name | Data Type | Description |
|---|---|---|
|
PRIME_MERIDIAN_ID |
NUMBER(10) |
ID number of the prime meridian. Example: |
|
PRIME_MERIDIAN_NAME |
VARCHAR2(80) |
Name of the prime meridian. Example: |
|
GREENWICH_LONGITUDE |
FLOAT(49) |
Longitude of the prime meridian as an offset from the Greenwich meridian. Example: |
|
UOM_ID |
NUMBER(10) |
ID number of the unit of measure for the prime meridian. Matches a value in the UOM_ID column of the SDO_UNITS_OF_MEASURE table (described in Section 6.6.27). Example: |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Origin of this information. Example: |
|
DATA_SOURCE |
VARCHAR2(40) |
Organization that supplied the data for this record (if not Oracle). Example: |
The SDO_UNITS_OF_MEASURE table contains one row for each unit of measure. This table contains the columns shown in Table 6-26.
Table 6-26 SDO_UNITS_OF_MEASURE Table
| Column Name | Data Type | Description |
|---|---|---|
|
UOM_ID |
NUMBER(10) |
ID number of the unit of measure. Example: |
|
UNIT_OF_MEAS_NAME |
VARCHAR2(80) |
Name of the unit of measure. Example: |
|
SHORT_NAME |
VARCHAR2(80) |
Short name (if any) of the unit of measure. |
|
UNIT_OF_MEAS_TYPE |
VARCHAR2(80) |
Type of measure for which the unit is used. Example: |
|
TARGET_UOM_ID |
NUMBER(10) |
ID number of a target unit of measure. Corresponds to the TARGET_UOM_CODE column in the EPSG Unit of Measure table, which has the following description: "Other UOM of the same type into which the current UOM can be converted using the formula (POSC); POSC factors A and D always equal zero for EPSG supplied units of measure." |
|
FACTOR_B |
NUMBER |
Corresponds to the FACTOR_B column in the EPSG Unit of Measure table, which has the following description: "A quantity in the target UOM (y) is obtained from a quantity in the current UOM (x) through the conversion: y = (B/C).x" |
|
FACTOR_C |
NUMBER |
Corresponds to the FACTOR_C column in the EPSG Unit of Measure table. |
|
INFORMATION_SOURCE |
VARCHAR2(254) |
Origin of this information. Example: |
|
DATA_SOURCE |
VARCHAR2(40) |
Organization providing the data for this record. Example: |
|
IS_LEGACY |
VARCHAR2(5) |
TRUE if the unit of measure definition was included in Oracle Spatial before release 10.2; FALSE if the unit of measure definition is new in Oracle Spatial release 10.2. |
|
LEGACY_CODE |
NUMBER(10) |
For any EPSG unit of measure that has a semantically identical legacy (in Oracle Spatial before release 10.2) counterpart, the UOM_ID value of the legacy unit of measure. |
In previous releases of Spatial, the coordinate systems functions and procedures used information provided in the following tables, some of which have new names:
MDSYS.CS_SRS (see Section 6.7.1) defined the valid coordinate systems. It associates each coordinate system with its well-known text description, which is in conformance with the standard published by the Open Geospatial Consortium (http://www.opengeospatial.org).
MDSYS.SDO_ANGLE_UNITS (see Section 6.7.2) defined the valid angle units.
MDSYS.SDO_AREA_UNITS (see Table 2-8 in Section 2.8) defined the valid area units.
MDSYS.SDO_DIST_UNITS (see Table 2-7 in Section 2.8) defined the valid distance units.
MDSYS.SDO_DATUMS_OLD_FORMAT and MDSYS.SDO_DATUMS_OLD_SNAPSHOT (see Section 6.7.3) are based on the MDSYS.SDO_DATUMS table before release 10.2, which defined valid datums.
MDSYS.SDO_ELLIPSOIDS_OLD_FORMAT and MDSYS.SDO_ELLIPSOIDS_OLD_SNAPSHOT (see Section 6.7.4) are based on the MDSYS.SDO_ELLIPSOIDS table before release 10.2, which defined valid ellipsoids.
MDSYS.SDO_PROJECTIONS_OLD_FORMAT and MDSYS.SDO_PROJECTIONS_OLD_SNAPSHOT (see Section 6.7.5) are based on the MDSYS.SDO_PROJECTIONS table before release 10.2, which defined the valid map projections.
|
Note: You should not modify or delete any Oracle-supplied information in these legacy tables.If you refer to a legacy table in a SQL statement, you must include the MDSYS. before the table name. |
The MDSYS.CS_SRS reference table contains over 4000 rows, one for each valid coordinate system. This table contains the columns shown in Table 6-27.
Table 6-27 MDSYS.CS_SRS Table
| Column Name | Data Type | Description |
|---|---|---|
|
CS_NAME |
VARCHAR2(68) |
A well-known name, often mnemonic, by which a user can refer to the coordinate system. |
|
SRID |
NUMBER(38) |
The unique ID number (Spatial Reference ID) for a coordinate system. Currently, SRID values 1-999999 are reserved for use by Oracle Spatial, and values 1000000 (1 million) and higher are available for user-defined coordinate systems. |
|
AUTH_SRID |
NUMBER(38) |
An optional ID number that can be used to indicate how the entry was derived; it might be a foreign key into another coordinate table, for example. |
|
AUTH_NAME |
VARCHAR2(256) |
An authority name for the coordinate system. Contains |
|
WKTEXT |
VARCHAR2(2046) |
The well-known text (WKT) description of the SRS, as defined by the Open Geospatial Consortium. For more information, see Section 6.7.1.1. |
|
CS_BOUNDS |
SDO_GEOMETRY |
An optional SDO_GEOMETRY object that is a polygon with WGS 84 longitude and latitude vertices, representing the spheroidal polygon description of the zone of validity for a projected coordinate system. Must be null for a geographic or non-Earth coordinate system. Is null in all supplied rows. |
The WKTEXT column of the MDSYS.CS_SRS table contains the well-known text (WKT) description of the SRS, as defined by the Open Geospatial Consortium. The following is the WKT EBNF syntax.
<coordinate system> ::=
<horz cs> | <local cs>
<horz cs> ::=
<geographic cs> | <projected cs>
<projected cs> ::=
PROJCS [ "<name>", <geographic cs>, <projection>,
{<parameter>,}* <linear unit> ]
<projection> ::=
PROJECTION [ "<name>" ]
<parameter> ::=
PARAMETER [ "<name>", <number> ]
<geographic cs> ::=
GEOGCS [ "<name>", <datum>, <prime meridian>, <angular unit> ]
<datum> ::=
DATUM [ "<name>", <spheroid>
{, <shift-x>, <shift-y>, <shift-z>
, <rot-x>, <rot-y>, <rot-z>, <scale_adjust>}
]
<spheroid> ::=
SPHEROID ["<name>", <semi major axis>, <inverse flattening> ]
<prime meridian> ::=
PRIMEM ["<name>", <longitude> ]
<longitude> ::=
<number>
<semi-major axis> ::=
<number>
<inverse flattening> ::=
<number>
<angular unit> ::= <unit>
<linear unit> ::= <unit>
<unit> ::=
UNIT [ "<name>", <conversion factor> ]
<local cs> ::=
LOCAL_CS [ "<name>", <local datum>, <linear unit>,
<axis> {, <axis>}* ]
<local datum> ::=
LOCAL_DATUM [ "<name>", <datum type>
{, <shift-x>, <shift-y>, <shift-z>
, <rot-x>, <rot-y>, <rot-z>, <scale_adjust>}
]
<datum type> ::=
<number>
<axis> ::=
AXIS [ "<name>", NORTH | SOUTH | EAST |
WEST | UP | DOWN | OTHER ]
Each <parameter> specification is one of the following:
Standard_Parallel_1 (in decimal degrees)
Standard_Parallel_2 (in decimal degrees)
Central_Meridian (in decimal degrees)
Latitude_of_Origin (in decimal degrees)
Azimuth (in decimal degrees)
False_Easting (in the unit of the coordinate system; for example, meters)
False_Northing (in the unit of the coordinate system; for example, meters)
Perspective_Point_Height (in the unit of the coordinate system; for example, meters)
Landsat_Number (must be 1, 2, 3, 4, or 5)
Path_Number
Scale_Factor
The default value for each <parameter> specification is 0 (zero). That is, if a specification is needed for a projection but no value is specified in the WKT, Spatial uses a value of 0.
The prime meridian (PRIMEM) is specified in decimal degrees of longitude.
An example of the WKT for a geodetic (geographic) coordinate system is:
'GEOGCS [ "Longitude / Latitude (Old Hawaiian)", DATUM ["Old Hawaiian", SPHEROID ["Clarke 1866", 6378206.400000, 294.978698]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]]'
The WKT definition of the coordinate system is hierarchically nested. The Old Hawaiian geographic coordinate system (GEOGCS) is composed of a named datum (DATUM), a prime meridian (PRIMEM), and a unit definition (UNIT). The datum is in turn composed of a named spheroid and its parameters of semi-major axis and inverse flattening.
An example of the WKT for a projected coordinate system (a Wyoming State Plane) is:
'PROJCS["Wyoming 4901, Eastern Zone (1983, meters)", GEOGCS [ "GRS 80", DATUM ["GRS 80", SPHEROID ["GRS 80", 6378137.000000, 298.257222]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Transverse Mercator"], PARAMETER ["Scale_Factor", 0.999938], PARAMETER ["Central_Meridian", -105.166667], PARAMETER ["Latitude_Of_Origin", 40.500000], PARAMETER ["False_Easting", 200000.000000], UNIT ["Meter", 1.000000000000]]'
The projected coordinate system contains a nested geographic coordinate system as its basis, as well as parameters that control the projection.
Oracle Spatial supports all common geodetic datums and map projections.
An example of the WKT for a local coordinate system is:
LOCAL_CS [ "Non-Earth (Meter)", LOCAL_DATUM ["Local Datum", 0], UNIT ["Meter", 1.0], AXIS ["X", EAST], AXIS["Y", NORTH]]
For more information about local coordinate systems, see Section 6.3.
You can use the SDO_CS.VALIDATE_WKT function, described in Chapter 13, to validate the WKT of any coordinate system defined in the MDSYS.CS_SRS table.
If you insert or delete a row in the SDO_COORD_REF_SYSTEM view (described in Section 6.6.10), Spatial automatically updates the WKTEXT column in the MDSYS.CS_SRS table. (The format of the WKTEXT column is described in Section 6.7.1.1.) However, if you update an existing row in the SDO_COORD_REF_SYSTEM view, the well-known text (WKT) value is not automatically updated.
In addition, information relating to coordinate reference systems is also stored in several other system tables, including SDO_DATUMS (described in Section 6.6.22), SDO_ELLIPSOIDS (described in Section 6.6.23), and SDO_PRIME_MERIDIANS (described in Section 6.6.26). If you add, delete, or modify information in these tables, the WKTEXT values in the MDSYS.CS_SRS table are not automatically updated. For example, if you update an ellipsoid flattening value in the SDO_ELLIPSOIDS table, the well-known text string for the associated coordinate system is not updated.
However, you can manually update the WKTEXT values in the in the MDSYS.CS_SRS table by using any of several procedures whose names start with UPDATE_WKTS_FOR (for example, SDO_CS.UPDATE_WKTS_FOR_ALL_EPSG_CRS and SDO_CS.UPDATE_WKTS_FOR_EPSG_DATUM). If the display of SERVEROUTPUT information is enabled, these procedures display a message identifying the SRID value for each row in the MDSYS.CS_SRS table whose WKTEXT value is being updated. These procedures are described in Chapter 13.
The MDSYS.SDO_ANGLE_UNITS reference table contains one row for each valid UNIT specification in the well-known text (WKT) description in the coordinate system definition. The WKT is described in Section 6.7.1.1.
The MDSYS.SDO_ANGLE_UNITS table contains the columns shown in Table 6-28.
Table 6-28 MDSYS.SDO_ANGLE_UNITS Table
| Column Name | Data Type | Description |
|---|---|---|
|
SDO_UNIT |
VARCHAR2(32) |
Name of the angle unit (often a shortened form of the UNIT_NAME value). Use the SDO_UNIT value with the |
|
VARCHAR2(100) |
Name of the angle unit. Specify a value from this column in the UNIT specification of the WKT for any user-defined coordinate system. Examples: |
|
|
NUMBER |
The ratio of the specified unit to one radian. For example, the ratio of |
The MDSYS.SDO_DATUMS_OLD_FORMAT and MDSYS.SDO_DATUMS_OLD_SNAPSHOT reference tables contain one row for each valid DATUM specification in the well-known text (WKT) description in the coordinate system definition. (The WKT is described in Section 6.7.1.1.)
MDSYS.SDO_DATUMS_OLD_FORMAT contains the new data in the old format (that is, EPSG-based datum specifications in a table using the format from before release 10.2).
MDSYS.SDO_DATUMS_OLD_SNAPSHOT contains the old data in the old format (that is, datum specifications and table format from before release 10.2).
These tables contain the columns shown in Table 6-29.
Table 6-29 MDSYS.SDO_DATUMS_OLD_FORMAT and SDO_DATUMS_OLD_SNAPSHOT Tables
| Column Name | Data Type | Description |
|---|---|---|
|
NAME |
VARCHAR2(80) for OLD_FORMAT VARCHAR2(64) for OLD_SNAPSHOT |
Name of the datum. Specify a value (Oracle-supplied or user-defined) from this column in the DATUM specification of the WKT for any user-defined coordinate system. Examples: |
|
SHIFT_X |
NUMBER |
Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the x-axis. |
|
SHIFT_Y |
NUMBER |
Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the y-axis. |
|
SHIFT_Z |
NUMBER |
Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the z-axis. |
|
ROTATE_X |
NUMBER |
Number of arc-seconds of rotation about the x-axis. |
|
ROTATE_Y |
NUMBER |
Number of arc-seconds of rotation about the y-axis. |
|
ROTATE_Z |
NUMBER |
Number of arc-seconds of rotation about the z-axis. |
|
SCALE_ADJUST |
NUMBER |
A value to be used in adjusting the X, Y, and Z values after any shifting and rotation, according to the formula: 1.0 + (SCALE_ADJUST * 10-6) |
The following are the names (in tabular format) of the datums in these tables:
| Adindan | Afgooye | Ain el Abd 1970 |
| Anna 1 Astro 1965 | Arc 1950 | Arc 1960 |
| Ascension Island 1958 | Astro B4 Sorol Atoll | Astro Beacon E |
| Astro DOS 71/4 | Astronomic Station 1952 | Australian Geodetic 1966 |
| Australian Geodetic 1984 | Belgium Hayford | Bellevue (IGN) |
| Bermuda 1957 | Bogota Observatory | CH 1903 (Switzerland) |
| Campo Inchauspe | Canton Astro 1966 | Cape |
| Cape Canaveral | Carthage | Chatham 1971 |
| Chua Astro | Corrego Alegre | DHDN (Potsdam/Rauenberg) |
| DOS 1968 | Djakarta (Batavia) | Easter Island 1967 |
| European 1950 | European 1979 | European 1987 |
| GRS 67 | GRS 80 | GUX 1 Astro |
| Gandajika Base | Geodetic Datum 1949 | Guam 1963 |
| Hito XVIII 1963 | Hjorsey 1955 | Hong Kong 1963 |
| Hu-Tzu-Shan | ISTS 073 Astro 1969 | Indian (Bangladesh, etc.) |
| Indian (Thailand/Vietnam) | Ireland 1965 | Johnston Island 1961 |
| Kandawala | Kerguelen Island | Kertau 1948 |
| L.C. 5 Astro | Liberia 1964 | Lisboa (DLx) |
| Luzon (Mindanao Island) | Luzon (Philippines) | Mahe 1971 |
| Marco Astro | Massawa | Melrica 1973 (D73) |
| Merchich | Midway Astro 1961 | Minna |
| NAD 27 (Alaska) | NAD 27 (Bahamas) | NAD 27 (Canada) |
| NAD 27 (Canal Zone) | NAD 27 (Caribbean) | NAD 27 (Central America) |
| NAD 27 (Continental US) | NAD 27 (Cuba) | NAD 27 (Greenland) |
| NAD 27 (Mexico) | NAD 27 (Michigan) | NAD 27 (San Salvador) |
| NAD 83 | NTF (Greenwich meridian) | NTF (Paris meridian) |
| NWGL 10 | Nahrwan (Masirah Island) | Nahrwan (Saudi Arabia) |
| Nahrwan (Un. Arab Emirates) | Naparima, BWI | Netherlands Bessel |
| Observatorio 1966 | Old Egyptian | Old Hawaiian |
| Oman | Ordinance Survey Great Brit | Pico de las Nieves |
| Pitcairn Astro 1967 | Provisional South American | Puerto Rico |
| Pulkovo 1942 | Qatar National | Qornoq |
| RT 90 (Sweden) | Reunion | Rome 1940 |
| Santo (DOS) | Sao Braz | Sapper Hill 1943 |
| Schwarzeck | South American 1969 | South Asia |
| Southeast Base | Southwest Base | Timbalai 1948 |
| Tokyo | Tristan Astro 1968 | Viti Levu 1916 |
| WGS 60 | WGS 66 | WGS 72 |
| WGS 84 | Wake-Eniwetok 1960 | Yacare |
| Zanderij |
The MDSYS.SDO_ELLIPSOIDS_OLD_FORMAT and MDSYS.SDO_ELLIPSOIDS_OLD_SNAPSHOT reference tables contain one row for each valid SPHEROID specification in the well-known text (WKT) description in the coordinate system definition. (The WKT is described in Section 6.7.1.1.)
MDSYS.SDO_ELLIPSOIDS_OLD_FORMAT contains the new data in the old format (that is, EPSG-based ellipsoid specifications in a table using the format from before release 10.2).
MDSYS.SDO_ELLIPSOIDS_OLD_SNAPSHOT contains the old data in the old format (that is, ellipsoid specifications and table format from before release 10.2).
These tables contain the columns shown in Table 6-30.
Table 6-30 MDSYS.SDO_ELLIPSOIDS_OLD_FORMAT and SDO_ELLIPSOIDS_OLD_SNAPSHOT Tables
| Column Name | Data Type | Description |
|---|---|---|
|
NAME |
VARCHAR2(80) for OLD_FORMAT VARCHAR2(64) for OLD_SNAPSHOT |
Name of the ellipsoid (spheroid). Specify a value from this column in the SPHEROID specification of the WKT for any user-defined coordinate system. Examples: |
|
NUMBER |
Radius in meters along the semi-major axis (one-half of the long axis of the ellipsoid). |
|
|
NUMBER |
Inverse flattening of the ellipsoid. That is, |
The following are the names (in tabular format) of the ellipsoids in these tables:
| Airy 1930 | Airy 1930 (Ireland 1965) | Australian |
| Bessel 1841 | Bessel 1841 (NGO 1948) | Bessel 1841 (Schwarzeck) |
| Clarke 1858 | Clarke 1866 | Clarke 1866 (Michigan) |
| Clarke 1880 | Clarke 1880 (Arc 1950) | Clarke 1880 (IGN) |
| Clarke 1880 (Jamaica) | Clarke 1880 (Merchich) | Clarke 1880 (Palestine) |
| Everest | Everest (Kalianpur) | Everest (Kertau) |
| Everest (Timbalai) | Fischer 1960 (Mercury) | Fischer 1960 (South Asia) |
| Fischer 1968 | GRS 67 | GRS 80 |
| Hayford | Helmert 1906 | Hough |
| IAG 75 | Indonesian | International 1924 |
| Krassovsky | MERIT 83 | NWL 10D |
| NWL 9D | New International 1967 | OSU86F |
| OSU91A | Plessis 1817 | South American 1969 |
| Sphere (6370997m) | Struve 1860 | WGS 60 |
| WGS 66 | WGS 72 | WGS 84 |
| Walbeck | War Office |
The MDSYS.SDO_PROJECTIONS_OLD_FORMAT and MDSYS.SDO_PROJECTIONS_OLD_SNAPSHOT reference tables contain one row for each valid PROJECTION specification in the well-known text (WKT) description in the coordinate system definition. (The WKT is described in Section 6.7.1.1.)
MDSYS.SDO_PROJECTIONS_OLD_FORMAT contains the new data in the old format (that is, EPSG-based projection specifications in a table using the format from before release 10.2).
MDSYS.SDO_PROJECTIONS_OLD_SNAPSHOT contains the old data in the old format (that is, projection specifications and table format from before release 10.2).
These tables contains the column shown in Table 6-31.
Table 6-31 MDSYS.SDO_PROJECTIONS_OLD_FORMAT and SDO_PROJECTIONS_OLD_SNAPSHOT Tables
| Column Name | Data Type | Description |
|---|---|---|
|
NAME |
VARCHAR2(80) for OLD_FORMAT VARCHAR2(64) for OLD_SNAPSHOT |
Name of the map projection. Specify a value from this column in the PROJECTION specification of the WKT for any user-defined coordinate system. Examples: |
The following are the names (in tabular format) of the projections in these tables:
| Alaska Conformal | Albers Conical Equal Area |
| Azimuthal Equidistant | Bonne |
| Cassini | Cylindrical Equal Area |
| Eckert IV | Eckert VI |
| Equidistant Conic | Equirectangular |
| Gall | General Vertical Near-Side Perspective |
| Geographic (Lat/Long) | Gnomonic |
| Hammer | Hotine Oblique Mercator |
| Interrupted Goode Homolosine | Interrupted Mollweide |
| Lambert Azimuthal Equal Area | Lambert Conformal Conic |
| Lambert Conformal Conic (Belgium 1972) | Mercator |
| Miller Cylindrical | Mollweide |
| New Zealand Map Grid | Oblated Equal Area |
| Orthographic | Polar Stereographic |
| Polyconic | Robinson |
| Sinusoidal | Space Oblique Mercator |
| State Plane Coordinates | Stereographic |
| Swiss Oblique Mercator | Transverse Mercator |
| Transverse Mercator Danish System 34 Jylland-Fyn | Transverse Mercator Danish System 45 Bornholm |
| Transverse Mercator Finnish KKJ | Transverse Mercator Sjaelland |
| Universal Transverse Mercator | Van der Grinten |
| Wagner IV | Wagner VII |
If the coordinate systems supplied by Oracle are not sufficient for your needs, you can create user-defined coordinate reference systems.
|
Note: As mentioned in Section 6.1.1, the terms coordinate system and coordinate reference system (CRS) are often used interchangeably, although coordinate reference systems must be Earth-based. |
The exact steps for creating a user-defined CRS depend on whether it is geodetic or projected. In both cases, supply information about the coordinate system (coordinate axes, axis names, unit of measure, and so on). For a geodetic CRS, supply information about the datum (ellipsoid, prime meridian, and so on), as explained in Section 6.8.1. For a projected CRS, supply information about the source (geodetic) CRS and the projection (operation and parameters), as explained in Section 6.8.2.
For any user-defined coordinate system, the SRID value should be 1000000 (1 million) or higher.
If the necessary unit of measure, coordinate axes, SDO_COORD_SYS table row, ellipsoid, prime meridian, and datum are already defined, insert a row into the SDO_COORD_REF_SYSTEM view (described in Section 6.6.10) to define the new geodetic CRS.
Example 6-2 inserts the definition for a hypothetical geodetic CRS named My Own NAD27 (which, except for its SRID and name, is the same as the NAD27 CRS supplied by Oracle).
Example 6-2 Creating a User-Defined Geodetic Coordinate Reference System
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 (
9994267,
'My Own NAD27',
'GEOGRAPHIC2D',
6422,
6267,
6267,
NULL,
NULL,
NULL,
NULL,
NULL,
'EPSG',
'FALSE',
NULL,
NULL,
NULL,
'TRUE',
'TRUE');
If the necessary information for the definition does not already exist, follow these steps, as needed, to define the information before you insert the row into the SDO_COORD_REF_SYSTEM view:
If the unit of measure is not already defined in the SDO_UNITS_OF_MEASURE table (described in Section 6.6.27), insert a row into that table to define the new unit of measure.
If the coordinate axes are not already defined in the SDO_COORD_AXES table (described in Section 6.6.1), insert one row into that table for each new coordinate axis.
If an appropriate entry for the coordinate system does not already exist in the SDO_COORD_SYS table (described in Section 6.6.11), insert a row into that table. Example 6-3 inserts the definition for a fictitious coordinate system.
Example 6-3 Inserting a Row into the SDO_COORD_SYS Table
INSERT INTO SDO_COORD_SYS (
COORD_SYS_ID,
COORD_SYS_NAME,
COORD_SYS_TYPE,
DIMENSION,
INFORMATION_SOURCE,
DATA_SOURCE)
VALUES (
9876543,
'My custom CS. Axes: lat, long. Orientations: north, east. UoM: deg',
'ellipsoidal',
2,
'Myself',
'Myself');
If the ellipsoid is not already defined in the SDO_ELLIPSOIDS table (described in Section 6.6.23), insert a row into that table to define the new ellipsoid.
If the prime meridian is not already defined in the SDO_PRIME_MERIDIANS table (described in Section 6.6.26), insert a row into that table to define the new prime meridian.
If the datum is not already defined in the SDO_DATUMS table (described in Section 6.6.22), insert a row into that table to define the new datum.
If the necessary unit of measure, coordinate axes, SDO_COORD_SYS table row, source coordinate system, projection operation, and projection parameters are already defined, insert a row into the SDO_COORD_REF_SYSTEM view (described in Section 6.6.10) to define the new projected CRS.
Example 6-4 inserts the definition for a hypothetical projected CRS named My Own NAD27 / Cuba Norte (which, except for its SRID and name, is the same as the NAD27 / Cuba Norte CRS supplied by Oracle).
Example 6-4 Creating a User-Defined Projected Coordinate Reference System
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 (
9992085,
'My Own NAD27 / Cuba Norte',
'PROJECTED',
4532,
NULL,
6267,
4267,
18061,
NULL,
NULL,
'Institut Cubano di Hidrografia (ICH)',
'EPSG',
'FALSE',
NULL,
NULL,
NULL,
'TRUE',
'TRUE');
If the necessary information for the definition does not already exist, follow these steps, as needed, to define the information before you insert the row into the SDO_COORD_REF_SYSTEM view:
If the unit of measure is not already defined in the SDO_UNITS_OF_MEASURE table (described in Section 6.6.27), insert a row into that table to define the new unit of measure.
If the coordinate axes are not already defined in the SDO_COORD_AXES table (described in Section 6.6.1), insert one row into that table for each new coordinate axis.
If an appropriate entry for the coordinate system does not already exist in SDO_COORD_SYS table (described in Section 6.6.11), insert a row into that table. (See Example 6-3 in Section 6.8.1).
If the projection operation is not already defined in the SDO_COORD_OPS table (described in Section 6.6.8), insert a row into that table to define the new projection operation. Example 6-5 shows the statement used to insert information about coordinate operation ID 18061, which is supplied by Oracle.
Example 6-5 Inserting a Row into the SDO_COORD_OPS Table
INSERT INTO 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 (
18061,
'Cuba Norte',
'CONVERSION',
NULL,
NULL,
NULL,
NULL,
9801,
NULL,
NULL,
NULL,
'EPSG',
1,
'FALSE',
NULL,
1,
1,
1);
If the parameters for the projection operation are not already defined in the SDO_COORD_OP_PARAM_VALS table (described in Section 6.6.5), insert one row into that table for each new parameter. Example 6-6 shows the statement used to insert information about parameters with ID values 8801, 8802, 8805, 8806, and 8807, which are supplied by Oracle.
Example 6-6 Inserting a Row into the SDO_COORD_OP_PARAM_VALS Table
INSERT INTO SDO_COORD_OP_PARAM_VALS (
COORD_OP_ID,
COORD_OP_METHOD_ID,
PARAMETER_ID,
PARAMETER_VALUE,
PARAM_VALUE_FILE_REF,
UOM_ID)
VALUES (
18061,
9801,
8801,
22.21,
NULL,
9110);
INSERT INTO SDO_COORD_OP_PARAM_VALS (
COORD_OP_ID,
COORD_OP_METHOD_ID,
PARAMETER_ID,
PARAMETER_VALUE,
PARAM_VALUE_FILE_REF,
UOM_ID)
VALUES (
18061,
9801,
8802,
-81,
NULL,
9110);
INSERT INTO SDO_COORD_OP_PARAM_VALS (
COORD_OP_ID,
COORD_OP_METHOD_ID,
PARAMETER_ID,
PARAMETER_VALUE,
PARAM_VALUE_FILE_REF,
UOM_ID)
VALUES (
18061,
9801,
8805,
.99993602,
NULL,
9201);
INSERT INTO SDO_COORD_OP_PARAM_VALS (
COORD_OP_ID,
COORD_OP_METHOD_ID,
PARAMETER_ID,
PARAMETER_VALUE,
PARAM_VALUE_FILE_REF,
UOM_ID)
VALUES (
18061,
9801,
8806,
500000,
NULL,
9001);
INSERT INTO SDO_COORD_OP_PARAM_VALS (
COORD_OP_ID,
COORD_OP_METHOD_ID,
PARAMETER_ID,
PARAMETER_VALUE,
PARAM_VALUE_FILE_REF,
UOM_ID)
VALUES (
18061,
9801,
8807,
280296.016,
NULL,
9001);
The following notes and restrictions apply to coordinate systems support in the current release of Oracle Spatial.
If you have geodetic data, see Section 6.2 for additional considerations, guidelines, and restrictions.
For Spatial operators (described in Chapter 11) that take two geometries as input parameters, if the geometries are based on different coordinate systems, the query window (the second geometry) is transformed to the coordinate system of the first geometry before the operation is performed. This transformation is a temporary internal operation performed by Spatial; it does not affect any stored query-window geometry.
For SDO_GEOM package geometry functions (described in Chapter 15) that take two geometries as input parameters, both geometries must be based on the same coordinate system.
In the current release, the 3D formats of LRS functions (explained in Section 7.4) are not supported with geodetic data.
In the current release, the following functions are supported by approximations with geodetic data:
When these functions are used on data with geodetic coordinates, they internally perform the operations in an implicitly generated local-tangent-plane Cartesian coordinate system and then transform the results to the geodetic coordinate system. For SDO_GEOM.SDO_BUFFER, generated arcs are approximated by line segments before the back-transform.
The following coordinate reference systems are provided for Oracle internal use and for other possible special uses:
unknown CRS (SRID 999999) means that the coordinate system is unknown, and its space could be geodetic or Cartesian. Contrast this with specifying a null coordinate reference system, which indicates an unknown coordinate system with a Cartesian space.
NaC (SRID 999998) means Not-a-CRS. Its name is patterned after the NaN (Not-a-Number) value in Java. It is intended for potential use with nonspatial geometries.
The following restrictions apply to geometries based on the unknown CRS and NaC coordinate reference systems:
You cannot perform coordinate system transformations on these geometries.
Operations that require a coordinate system will return a null value when performed on these geometries. These operations include finding the area or perimeter of a geometry, creating a buffer, densifying an arc, and computing the aggregate centroid.
The U.S. National Grid is a point coordinate representation using a single alphanumeric coordinate (for example, 18SUJ2348316806479498). This approach contrasts with the use of numeric coordinates to represent the location of a point, as is done with Oracle Spatial and EPSG. A good description of the U.S. National Grid is available at http://www.ngs.noaa.gov/TOOLS/usng.html.
To support the U.S. National Grid in Spatial, the SDO_GEOMETRY type cannot be used because it is based on numeric coordinates. Instead, a point in U.S. National Grid format is represented as a single string of type VARCHAR2. To allow conversion between the SDO_GEOMETRY format and the U.S. National grid format, the SDO_CS package (documented in Chapter 13) contains the following functions:
This section presents a simplified example that uses coordinate system transformation functions and procedures. It refers to concepts that are explained in this chapter and uses functions documented in Chapter 13.
Example 6-7 uses mostly the same geometry data (cola markets) as in Section 2.1, except that instead of null SDO_SRID values, the SDO_SRID value 8307 is used. That is, the geometries are defined as using the coordinate system whose SRID is 8307 and whose well-known name is "Longitude / Latitude (WGS 84)". This is probably the most widely used coordinate system, and it is the one used for global positioning system (GPS) devices. The geometries are then transformed using the coordinate system whose SRID is 8199 and whose well-known name is "Longitude / Latitude (Arc 1950)".
Example 6-7 uses the geometries illustrated in Figure 2-1 in Section 2.1, except that cola_d is a rectangle (here, a square) instead of a circle, because arcs are not supported with geodetic coordinate systems.
Example 6-7 does the following:
Creates a table (COLA_MARKETS_CS) to hold the spatial data
Inserts rows for four areas of interest (cola_a, cola_b, cola_c, cola_d), using the SDO_SRID value 8307
Updates the USER_SDO_GEOM_METADATA view to reflect the dimension of the areas, using the SDO_SRID value 8307
Creates a spatial index (COLA_SPATIAL_IDX_CS)
Performs some transformation operations (single geometry and entire layer)
Example 6-8 includes the output of the SELECT statements in Example 6-7.
Example 6-7 Simplified Example of Coordinate System Transformation
-- Create a table for cola (soft drink) markets in a
-- given geography (such as city or state).
CREATE TABLE cola_markets_cs (
mkt_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape SDO_GEOMETRY);
-- The next INSERT statement creates an area of interest for
-- Cola A. This area happens to be a rectangle.
-- The area could represent any user-defined criterion: for
-- example, where Cola A is the preferred drink, where
-- Cola A is under competitive pressure, where Cola A
-- has strong growth potential, and so on.
INSERT INTO cola_markets_cs VALUES(
1,
'cola_a',
SDO_GEOMETRY(
2003, -- two-dimensional polygon
8307, -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,1), -- polygon
SDO_ORDINATE_ARRAY(1,1, 5,1, 5,7, 1,7, 1,1) -- All vertices must
-- be defined for rectangle with geodetic data.
)
);
-- The next two INSERT statements create areas of interest for
-- Cola B and Cola C. These areas are simple polygons (but not
-- rectangles).
INSERT INTO cola_markets_cs VALUES(
2,
'cola_b',
SDO_GEOMETRY(
2003, -- two-dimensional polygon
8307,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)
)
);
INSERT INTO cola_markets_cs VALUES(
3,
'cola_c',
SDO_GEOMETRY(
2003, -- two-dimensional polygon
8307,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,1), --one polygon (exterior polygon ring)
SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5, 3,3)
)
);
-- Insert a rectangle (here, square) instead of a circle as in the original,
-- because arcs are not supported with geodetic coordinate systems.
INSERT INTO cola_markets_cs VALUES(
4,
'cola_d',
SDO_GEOMETRY(
2003, -- two-dimensional polygon
8307, -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,1), -- polygon
SDO_ORDINATE_ARRAY(10,9, 11,9, 11,10, 10,10, 10,9) -- All vertices must
-- be defined for rectangle with geodetic data.
)
);
---------------------------------------------------------------------------
-- UPDATE METADATA VIEW --
---------------------------------------------------------------------------
-- Update the USER_SDO_GEOM_METADATA view. This is required
-- before the Spatial index can be created. Do this only once for each
-- layer (table-column combination; here: cola_markets_cs and shape).
INSERT INTO user_sdo_geom_metadata
(TABLE_NAME,
COLUMN_NAME,
DIMINFO,
SRID)
VALUES (
'cola_markets_cs',
'shape',
SDO_DIM_ARRAY(
SDO_DIM_ELEMENT('Longitude', -180, 180, 10), -- 10 meters tolerance
SDO_DIM_ELEMENT('Latitude', -90, 90, 10) -- 10 meters tolerance
),
8307 -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
);
-------------------------------------------------------------------
-- CREATE THE SPATIAL INDEX --
-------------------------------------------------------------------
CREATE INDEX cola_spatial_idx_cs
ON cola_markets_cs(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
-------------------------------------------------------------------
-- TEST COORDINATE SYSTEM TRANSFORMATION --
-------------------------------------------------------------------
-- 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';
-- Same as preceding, but using to_srname parameter.
SELECT c.name, SDO_CS.TRANSFORM(c.shape, m.diminfo, 'Longitude / Latitude (Arc 1950)')
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 entire SHAPE layer and put results in the table
-- named cola_markets_cs_8199, which the procedure will create.
CALL SDO_CS.TRANSFORM_LAYER('COLA_MARKETS_CS','SHAPE','COLA_MARKETS_CS_8199',8199);
-- Select all from the old (existing) table.
SELECT * from cola_markets_cs;
-- Select all from the new (layer transformed) table.
SELECT * from cola_markets_cs_8199;
-- Show metadata for the new (layer transformed) table.
DESCRIBE cola_markets_cs_8199;
-- Use a geodetic MBR with SDO_FILTER.
SELECT c.name FROM cola_markets_cs c WHERE
SDO_FILTER(c.shape,
SDO_GEOMETRY(
2003,
8307, -- SRID for WGS 84 longitude/latitude
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,3),
SDO_ORDINATE_ARRAY(6,5, 10,10))
) = 'TRUE';
Example 6-8 shows the output of the SELECT statements in Example 6-7. Notice the slight differences between the coordinates in the original geometries (SRID 8307) and the transformed coordinates (SRID 8199) -- for example, (1, 1, 5, 1, 5, 7, 1, 7, 1, 1) and (1.00078604, 1.00274579, 5.00069354, 1.00274488, 5.0006986, 7.00323528, 1.00079179, 7.00324162, 1.00078604, 1.00274579) for cola_a.
Example 6-8 Output of SELECT Statements in Coordinate System Transformation Example
SQL> -- Return the transformation of cola_c using to_srid 8199
SQL> -- ('Longitude / Latitude (Arc 1950)')
SQL> SELECT c.name, SDO_CS.TRANSFORM(c.shape, m.diminfo, 8199)
2 FROM cola_markets_cs c, user_sdo_geom_metadata m
3 WHERE m.table_name = 'COLA_MARKETS_CS' AND m.column_name = 'SHAPE'
4 AND c.name = 'cola_c';
NAME
--------------------------------
SDO_CS.TRANSFORM(C.SHAPE,M.DIMINFO,8199)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z)
--------------------------------------------------------------------------------
cola_c
SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(3.00074114, 3.00291482, 6.00067068, 3.00291287, 6.0006723, 5.00307625, 4.0007
1961, 5.00307838, 3.00074114, 3.00291482))
SQL>
SQL> -- Same as preceding, but using to_srname parameter.
SQL> SELECT c.name, SDO_CS.TRANSFORM(c.shape, m.diminfo, 'Longitude / Latitude (Arc 1950)')
2 FROM cola_markets_cs c, user_sdo_geom_metadata m
3 WHERE m.table_name = 'COLA_MARKETS_CS' AND m.column_name = 'SHAPE'
4 AND c.name = 'cola_c';
NAME
--------------------------------
SDO_CS.TRANSFORM(C.SHAPE,M.DIMINFO,'LONGITUDE/LATITUDE(ARC1950)')(SDO_GTYPE, SDO
--------------------------------------------------------------------------------
cola_c
SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(3.00074114, 3.00291482, 6.00067068, 3.00291287, 6.0006723, 5.00307625, 4.0007
1961, 5.00307838, 3.00074114, 3.00291482))
SQL>
SQL> -- Transform the entire SHAPE layer and put results in the table
SQL> -- named cola_markets_cs_8199, which the procedure will create.
SQL> CALL SDO_CS.TRANSFORM_LAYER('COLA_MARKETS_CS','SHAPE','COLA_MARKETS_CS_8199',8199);
Call completed.
SQL>
SQL> -- Select all from the old (existing) table.
SQL> SELECT * from cola_markets_cs;
MKT_ID NAME
---------- --------------------------------
SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
1 cola_a
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(1, 1, 5, 1, 5, 7, 1, 7, 1, 1))
2 cola_b
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(5, 1, 8, 1, 8, 6, 5, 7, 5, 1))
3 cola_c
MKT_ID NAME
---------- --------------------------------
SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(3, 3, 6, 3, 6, 5, 4, 5, 3, 3))
4 cola_d
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(10, 9, 11, 9, 11, 10, 10, 10, 10, 9))
SQL>
SQL> -- Select all from the new (layer transformed) table.
SQL> SELECT * from cola_markets_cs_8199;
SDO_ROWID
------------------
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
AAABZzAABAAAOa6AAA
SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(1.00078604, 1.00274579, 5.00069354, 1.00274488, 5.0006986, 7.00323528, 1.0007
9179, 7.00324162, 1.00078604, 1.00274579))
AAABZzAABAAAOa6AAB
SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(5.00069354, 1.00274488, 8.00062191, 1.00274427, 8.00062522, 6.00315345, 5.000
6986, 7.00323528, 5.00069354, 1.00274488))
SDO_ROWID
------------------
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
AAABZzAABAAAOa6AAC
SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(3.00074114, 3.00291482, 6.00067068, 3.00291287, 6.0006723, 5.00307625, 4.0007
1961, 5.00307838, 3.00074114, 3.00291482))
AAABZzAABAAAOa6AAD
SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(10.0005802, 9.00337775, 11.0005553, 9.00337621, 11.0005569, 10.0034478, 10.00
SDO_ROWID
------------------
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
05819, 10.0034495, 10.0005802, 9.00337775))
SQL>
SQL> -- Show metadata for the new (layer transformed) table.
SQL> DESCRIBE cola_markets_cs_8199;
Name Null? Type
----------------------------------------- -------- ----------------------------
SDO_ROWID ROWID
GEOMETRY SDO_GEOMETRY
SQL>
SQL> -- Use a geodetic MBR with SDO_FILTER
SQL> SELECT c.name FROM cola_markets_cs c WHERE
2 SDO_FILTER(c.shape,
3 SDO_GEOMETRY(
4 2003,
5 8307, -- SRID for WGS 84 longitude/latitude
6 NULL,
7 SDO_ELEM_INFO_ARRAY(1,1003,3),
8 SDO_ORDINATE_ARRAY(6,5, 10,10))
9 ) = 'TRUE';
NAME
--------------------------------
cola_c
cola_b
cola_d
