Skip Headers

Oracle® Spatial User's Guide and Reference
Release 9.2
Part No. A96630-01
Go To Documentation Library
Library
Go To Product List
Product
Go To Table Of Contents
Contents

Master Index
Go To Index
Index

Feedback

Previous Next

5
Coordinate Systems (Spatial Reference Systems)

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, see Chapter 13.

5.1 Terms and Concepts

This section explains important terms and concepts related to coordinate system support in Oracle Spatial.

5.1.1 Coordinate System (Spatial Reference System)

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.

5.1.2 Cartesian Coordinates

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.

5.1.3 Geodetic Coordinates (Geographic Coordinates)

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 5.1.6). For more information about geodetic coordinate system support, see Section 5.2.

5.1.4 Projected Coordinates

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.

5.1.5 Local Coordinates

Local coordinates are Cartesian coordinates in a non-Earth (non-georeferenced) coordinate system. Section 5.3 describes local coordinate system support in Spatial.

5.1.6 Geodetic Datum

A geodetic datum is a means of representing the figure of the Earth, usually as an oblate ellipsoid of revolution, that approximates the surface of the Earth locally or globally, and is the reference for the system of geodetic coordinates.

5.1.7 Authalic Sphere

An authalic sphere is a sphere that has the same surface area as a particular oblate ellipsoid of revolution representing the figure of the Earth.

5.1.8 Transformation

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.

5.2 Geodetic Coordinate Support

With Oracle9i, Spatial provides a rational and complete treatment of geodetic coordinates. Before Oracle 9i, 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.

With the current release, ellipsoidal surface computations consider the curvatures of arcs in the specified geodetic coordinate system and return correct, accurate results. In other words, with the current release, Spatial queries return the right answers all the time.

5.2.1 Geodesy and Two-Dimensional Geometry

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.

With Oracle9i, 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.

5.2.2 Choosing a Geodetic or Projected 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, and take longer to complete. In general, a Spatial operation using geodetic coordinates will take two to three times longer than the same operation using Cartesian coordinates.

5.2.3 Other Considerations and Requirements with Geodetic Data

The following geometries are not permitted if a geodetic coordinate system is used:

  • Circles

  • Circular arcs

  • Optimized rectangles (rectangles defined specifying only two points)

    However, you can use the SDO_CS.VIEWPORT_TRANSFORM function to convert optimized longitude/latitude rectangles to valid geodetic polygons for use with the SDO_FILTER operator.

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 11) 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.

  • No line element can have a length longer than 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 an element representing all the ocean surface of the Earth; however, you can create multiple elements, each representing part of the overall ocean surface.

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 8 for more information.) In addition, for Spatial release 9.0.1 and higher 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. Note that if you use tolerance values typical for non-geodetic data, these values are interpreted as meters for geodetic data. For example, if you specify a tolerance value of 0.005 for geodetic data, this is interpreted as precise to 5 millimeters. 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 5-3 in Section 5.8) specifies these extents (with a 10-meter tolerance value in each dimension) for a geodetic data layer:

INSERT INTO USER_SDO_GEOM_METADATA 
  VALUES (
  'cola_markets_cs',
  'shape',
  MDSYS.SDO_DIM_ARRAY(
    MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, 10),  -- 10 meters tolerance
    MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, 10)  -- 10 meters tolerance
     ),
  8307   -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
);

See Section 5.7 for additional notes and restrictions relating to geodetic data.

5.3 Local Coordinate Support

With Oracle9i, 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 MDSYS.CS_SRS table (described in Section 5.4.1). 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 can use these local coordinate systems only to convert coordinates in a local coordinate system from one unit of measurement to another (for example, inches to millimeters) by transforming a geometry or a layer of geometries.

5.4 Coordinate Systems Data Structures

The coordinate systems functions and procedures use information provided in the following tables supplied with Oracle Spatial:

5.4.1 MDSYS.CS_SRS Table

The MDSYS.CS_SRS reference table contains over 900 rows, one for each valid coordinate system.


Note:

You should probably not modify, delete, or add any information in the MDSYS.CS_SRS table. If you plan to add any user-defined coordinate systems, be sure to use SRID values of 1000000 (1 million) or higher, and follow the guidelines in Section 5.5.

The MDSYS.CS_SRS table contains the columns shown in Table 5-1.

Table 5-1 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 'Oracle' in the supplied table. Users can specify any value in any rows that they add.
WKTEXT VARCHAR2(2046) The well-known text (WKT) description of the SRS, as defined by the OpenGIS Consortium. For more information, see Section 5.4.1.1.
CS_BOUNDS MDSYS.SDO_GEOMETRY 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.

5.4.1.1 Well-Known Text (WKTEXT)

The WKTEXT column of the MDSYS.CS_SRS table contains the well-known text (WKT) description of the SRS, as defined by the OpenGIS Consortium.

The following is the WKT EBNF syntax. All user-defined coordinate systems must strictly comply with this 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 ]

The prime meridian (PRIMEM) must be 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 semimajor 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 the 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]]

Local coordinate systems are described in Section 5.3.

5.4.2 MDSYS.SDO_ANGLE_UNITS Table

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 5.4.1.1.

The MDSYS.SDO_ANGLE_UNITS table contains the columns shown in Table 5-2.

Table 5-2 MDSYS.SDO_ANGLE_UNITS Table

Column Name Data Type Description
SDO_UNIT VARCHAR2(32) (Reserved for future use by Oracle Spatial.)
UNIT_NAME 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: Decimal Degree, Radian, Decimal Second, Decimal Minute, Gon, Grad
CONVERSION_FACTOR NUMBER The ratio of the specified unit to one Radian. For example, the ratio of Decimal Degree to Radian is 0.017453293.

5.4.3 MDSYS.SDO_DIST_UNITS Table

The MDSYS.SDO_DIST_UNITS reference table contains one row for each valid distance unit specification in the well-known text (WKT) description in the coordinate system definition. The WKT is described in Section 5.4.1.1.

The MDSYS.SDO_DIST_UNITS table contains the columns shown in Table 5-3.

Table 5-3 MDSYS.SDO_DIST_UNITS Table

Column Name Data Type Purpose
SDO_UNIT VARCHAR2 Unit string identifier. Examples: M, KM, CM, MM, MILE, NAUT_MILE, FOOT, INCH. Do not use this in the WKT definition; instead, use a value from UNIT_NAME.
UNIT_NAME VARCHAR2 Descriptive name of the unit, to be used in the WKT specification. Examples: Meter, Kilometer, Centimeter, Millimeter, Mile, Nautical Mile, Foot, Inch
CONVERSION_FACTOR NUMBER Ratio of the unit to 1 meter. For example, the conversion factor for a meter is 1.0, and the conversion factor for a mile is 1609.344.

5.4.4 MDSYS.SDO_DATUMS Table

The MDSYS.SDO_DATUMS reference table contains 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 5.4.1.1.

The MDSYS.SDO_DATUMS table contains the columns shown in Table 5-4.

Table 5-4 MDSYS.SDO_DATUMS Table

Column Name Data Type Description
NAME VARCHAR2(64) 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: Adindan, Afgooye, Ain el Abd 1970, Anna 1 Astro 1965, Arc 1950, Arc 1960, Ascension Island 1958.
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 supported datums:

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    

5.4.5 MDSYS.SDO_ELLIPSOIDS Table

The MDSYS.SDO_ELLIPSOIDS reference table contains 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 5.4.1.1.

The MDSYS.SDO_ELLIPSOIDS table contains the columns shown in Table 5-5.

Table 5-5 MDSYS.SDO_ELLIPSOIDS Table

Column Name Data Type Description
NAME VARCHAR2(64) 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: Clarke 1866, WGS 72, Australian, Krassovsky, International 1924.
SEMI_MAJOR_AXIS NUMBER Radius in meters along the semi-major axis (one-half of the long axis of the ellipsoid).
INVERSE_FLATTENING NUMBER Inverse flattening of the ellipsoid. That is, 1/f, where f = (a-b)/a, and a = semi-major axis and b = semi-minor axis.

The following are the names (in tabular format) of the supported ellipsoids:

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  

5.4.6 MDSYS.SDO_PROJECTIONS Table

The MDSYS.SDO_PROJECTIONS reference table contains 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 5.4.1.1.

The MDSYS.SDO_PROJECTIONS table contains the column shown in Table 5-6.

Table 5-6 MDSYS.SDO_PROJECTIONS Table

Column Name Data Type Description
NAME VARCHAR2(64) 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: Geographic (Lat/Long), Universal Transverse Mercator, State Plane Coordinates, Albers Conical Equal Area.

The following are the names (in tabular format) of the supported projections:

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

5.5 Creating a User-Defined Coordinate System

To create a user-defined coordinate system, add a row to the MDSYS.CS_SRS table. See Section 5.4.1 for information about this table, including the requirements for values in each column.

To specify the WKTEXT column in the MDSYS.CS_SRS table, follow the syntax specified in Section 5.4.1.1. See also the examples in that section.

When you specify the WKTEXT column entry, use valid values from several Spatial reference tables:

The name in each PARAMETER specification must be one of the following, depending on the projection that you use:

Some of these parameters are appropriate for several projections. They are not all appropriate for every projection.

Example 5-1 creates a user-defined projected coordinate system. The first four columns are not the WKT information, but specify other fields in the MSDYD.CS_SRS table. The WKT information starts with PROJCS. This example is similar to an existing coordinate system, but has a different name, SRID, and central meridian.

Example 5-1 Creating a User-Defined Projected Coordinate System

INSERT INTO mdsys.cs_srs VALUES ('UTM Zone 44.5, Northern Hemisphere (WGS 84)',
1082378, 1082378, 'Oracle',
'PROJCS["UTM Zone 44.5, Northern Hemisphere (WGS 84)",
GEOGCS [ "WGS 84",
DATUM ["WGS 84 ",
SPHEROID ["WGS 84", 6378137.000000, 298.257224]],
PRIMEM [ "Greenwich", 0.000000 ],
UNIT ["Decimal Degree", 0.01745329251994330]],
PROJECTION ["Transverse Mercator"],
PARAMETER ["Scale_Factor", 0.999600],
PARAMETER ["Central_Meridian", 84.000000],
PARAMETER ["False_Easting", 500000.000000],
UNIT ["Meter", 1.000000000000]]',NULL);

Example 5-2 creates a user-defined geodetic coordinate system. The first four columns are not the WKT information, but specify other fields in the MSDYD.CS_SRS table. The WKT information starts with GEOGCS. This example includes an ellipsoid (SPHEROID) definition in which the semi-major axis and inverse flattening parameters are slightly changed from the WGS 84 coordinate system, as well as a different datum definition. Because the shift_x and shift_y parameter values are specified, all the shift, rotation, and scaling values must be specified. There is no projection information included for a geodetic coordinate system.

Example 5-2 Creating a User-Defined Geodetic Coordinate System

INSERT INTO mdsys.cs_srs  VALUES
( 'Longitude / Latitude (WGS 90)', 1008307, 1008307, 'Oracle',
'GEOGCS [ "Longitude / Latitude (WGS 90)",
DATUM ["WGS 90",
SPHEROID ["WGS 90", 6378137.032499, 298.257236], 100, 100, 0, 0, 0, 0, 0],
PRIMEM [ "Greenwich", 0.000000 ],
UNIT ["Decimal Degree", 0.01745329251994330]]',NULL);

5.6 Coordinate System Transformation Functions

The current release of Oracle Spatial includes the following functions and procedures for data transformation using coordinate systems:

Reference information about these functions and procedures is in Chapter 13.

Support for additional functions and procedures is planned for future releases of Oracle Spatial.

5.7 Notes and Restrictions with Coordinate Systems Support

The following notes and restrictions apply to coordinate systems support in the current release of Spatial.

If you have geodetic data, see also Section 5.2 for considerations, guidelines, and additional restrictions.

5.7.1 Different Coordinate Systems for Geometries with Operators and Functions

For Spatial operators (described in Chapter 10) 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 11) that take two geometries as input parameters, both geometries must be based on the same coordinate system.

5.7.2 Functions Not Supported with Geodetic Data

In the current release, the following functions are not supported with geodetic data:

5.7.3 Functions Supported by Approximations 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.

5.8 Example of Coordinate System Transformation

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 5-3 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 5-3 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 5-3 does the following:

Example 5-4 includes the output of the SELECT statements in Example 5-3.

Example 5-3 Simplified Example of Coordinate System Transformation

-- Create a table for cola (soft drink) markets in a
-- given geography (such as city or state).
-- Each row will be an area of interest for a specific
-- cola (for example, where the cola is most preferred
-- by residents, where the manufacturer believes the
-- cola has growth potential, etc.

CREATE TABLE cola_markets_cs (
  mkt_id NUMBER PRIMARY KEY,
  name VARCHAR2(32),
  shape MDSYS.SDO_GEOMETRY);

-- Note re. areas of interest: cola_a (rectangle) and
-- cola_b (4-sided polygon) are side by side (share 1 border).
-- cola_c is a small 4-sided polygon that overlaps parts of
-- cola_a and cola_b. A rough sketch:
--     ---------+
--     |    a   |  b   \
--     |     +------+     |   
--     |   /___c____|     |
--     |        |         |
--     ---------+---------|

-- 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',
  MDSYS.SDO_GEOMETRY(
    2003,  -- 2-dimensional polygon
    8307,  -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), -- polygon
    MDSYS.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',
  MDSYS.SDO_GEOMETRY(
    2003,  -- 2-dimensional polygon
    8307,
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
    MDSYS.SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)
  )
);

INSERT INTO cola_markets_cs VALUES(
  3,
  'cola_c',
  MDSYS.SDO_GEOMETRY(
    2003,  -- 2-dimensional polygon
    8307,
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), --one polygon (exterior polygon ring)
    MDSYS.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',
  MDSYS.SDO_GEOMETRY(
    2003,  -- 2-dimensional polygon
    8307,  -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), -- polygon
    MDSYS.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 (i.e., table-column combination; here: cola_markets_cs and shape).

INSERT INTO USER_SDO_GEOM_METADATA 
  VALUES (
  'cola_markets_cs',
  'shape',
  MDSYS.SDO_DIM_ARRAY(
    MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, 10),  -- 10 meters tolerance
    MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, 10)  -- 10 meters tolerance
     ),
  8307   -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
);

-------------------------------------------------------------------
-- CREATE THE SPATIAL INDEX --
-------------------------------------------------------------------
-- Must be R-tree; quadtree not supported for geodetic data.
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.
EXECUTE 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;

-- Viewport_Transform
SELECT c.name FROM cola_markets_cs c WHERE
   SDO_FILTER(c.shape, SDO_CS.VIEWPORT_TRANSFORM(
       MDSYS.SDO_GEOMETRY(
           2003,
           0,    -- SRID = 0 (special case)
           NULL,
           MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
           MDSYS.SDO_ORDINATE_ARRAY(-180,-90,180,90)),
       8307), 'querytype=window') = 'TRUE';

Example 5-4 shows the output of the SELECT statements in Example 5-3. 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 5-4 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> EXECUTE SDO_CS.TRANSFORM_LAYER('COLA_MARKETS_CS','SHAPE','COLA_MARKETS_CS_8199',8199);

PL/SQL procedure successfully 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                                           MDSYS.SDO_GEOMETRY

SQL> 
SQL> -- Viewport_Transform
SQL> SELECT c.name FROM cola_markets_cs c WHERE
  2     SDO_FILTER(c.shape, SDO_CS.VIEWPORT_TRANSFORM(
  3         MDSYS.SDO_GEOMETRY(
  4         2003,
  5         0,    -- SRID = 0 (special case)
  6         NULL,
  7         MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
  8         MDSYS.SDO_ORDINATE_ARRAY(-180,-90,180,90)),
  9         8307), 'querytype=window') = 'TRUE';

NAME                                                                            
--------------------------------                                                
cola_a                                                                          
cola_c                                                                          
cola_b                                                                          
cola_d 





Previous Next
Oracle Logo
Copyright © 1999, 2002 Oracle Corporation

All rights reserved
Go To Documentation Library
Library
Go To Product List
Product
Go To Table Of Contents
Contents

Master Index
Go To Index
Index

Feedback