Oracle® Spatial User's Guide and Reference 10g Release 1 (10.1) Part Number B1082601 


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, see Chapter 15.
This chapter contains the following major sections:
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.
Cartesian coordinates are coordinates that measure the position of a point from a defined origin along axes that are perpendicular in the represented twodimensional or threedimensional 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 system 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 nonEarth (nongeoreferenced) coordinate system. Section 6.3 describes local coordinate system support in Spatial.
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.
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.
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 arcs in the specified geodetic coordinate system and return correct, accurate results. In other words, Spatial queries return the right answers all the time.
A twodimensional 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, 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.
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 22 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 61 requests the names of all cola markets that are likely to interact spatially with a geodetic MBR.
Example 61 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 61 produces the following output (assuming the data as defined in Example 64 in Section 6.8):
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 lowerleft Y coordinate (minY) must be less than the upperright Y coordinate (maxY). If the lowerleft X coordinate (minX) is greater than the upperright 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 threefourths 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 onedegree intervals. This might affect results for objects within a few meters of the edge of the MBR (especially objects near the North and South Poles).
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 13) 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 onehalf 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 the entire 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 Rtree index. (You can create a nongeodetic Rtree 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 higher you must delete (DROP INDEX) and recreate 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 nongeodetic 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 64 in Section 6.8) specifies these extents (with a 10meter tolerance value in each dimension) for a geodetic data layer:
INSERT INTO USER_SDO_GEOM_METADATA 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.7 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 MDSYS.CS_SRS table (described in Section 6.4.1). These supplied local coordinate systems, whose names start with NonEarth, define nonEarth 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.
The coordinate systems functions and procedures use information provided in the following tables supplied with Oracle Spatial:
MDSYS.CS_SRS (see Section 6.4.1) defines the valid coordinate systems. It associates each coordinate system with its wellknown text description, which is in conformance with the standard published by the Open GIS Consortium (http://www.opengis.org
).
MDSYS.SDO_ANGLE_UNITS (see Section 6.4.2) defines the valid angle units. The angle unit is part of the wellknown text description.
MDSYS.SDO_DIST_UNITS (see Table 26 in Section 2.6) defines the valid distance units. The distance unit is included in the wellknown text description.
MDSYS.SDO_DATUMS (see Section 6.4.3) defines the valid datums. The datum is part of the wellknown text description.
MDSYS.SDO_ELLIPSOIDS (see Section 6.4.4) defines the valid ellipsoids. The ellipsoid (SPHEROID specification) is part of the wellknown text description.
MDSYS.SDO_PROJECTIONS (see Section 6.4.5) defines the valid map projections. The map projection is part of the wellknown text description.
Note: You should not modify or delete any Oraclesupplied information in any of the tables that are used for coordinate system support.You should not add any information to the MDSYS.CS_SRS table unless you are creating a userdefined coordinate system. (Do not add information to the MDSYS.SDO_DATUMS, MDSYS.SDO_ELLIPSOIDS, or MDSYS.PROJECTIONS tables.) Section 6.5 describes how to create a userdefined coordinate system. 
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 do plan to modify this table, you should connect to the database as the MDSYS user.If you plan to add any userdefined coordinate systems, be sure to use SRID values of 1000000 (1 million) or higher, and follow the guidelines in Section 6.5. 
The MDSYS.CS_SRS table contains the columns shown in Table 61.
Table 61 MDSYS.CS_SRS Table
Column Name  Data Type  Description 

CS_NAME  VARCHAR2(68)  A wellknown 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 1999999 are reserved for use by Oracle Spatial, and values 1000000 (1 million) and higher are available for userdefined 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 wellknown text (WKT) description of the SRS, as defined by the Open GIS Consortium. For more information, see Section 6.4.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 nonEarth coordinate system. Is null in all supplied rows. 
The WKTEXT column of the MDSYS.CS_SRS table contains the wellknown text (WKT) description of the SRS, as defined by the Open GIS Consortium.
The following is the WKT EBNF syntax. All userdefined 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> {, <shiftx>, <shifty>, <shiftz> , <rotx>, <roty>, <rotz>, <scale_adjust>} ] <spheroid> ::= SPHEROID ["<name>", <semi major axis>, <inverse flattening> ] <prime meridian> ::= PRIMEM ["<name>", <longitude> ] <longitude> ::= <number> <semimajor 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> {, <shiftx>, <shifty>, <shiftz> , <rotx>, <roty>, <rotz>, <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 common geodetic datums and map projections.
An example of the WKT for a local coordinate system is:
LOCAL_CS [ "NonEarth (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 15, to validate the WKT of any coordinate system defined in the MDSYS.CS_SRS table.
The MDSYS.SDO_ANGLE_UNITS reference table contains one row for each valid UNIT specification in the wellknown text (WKT) description in the coordinate system definition. The WKT is described in Section 6.4.1.1.
The MDSYS.SDO_ANGLE_UNITS table contains the columns shown in Table 62.
Table 62 MDSYS.SDO_ANGLE_UNITS Table
The MDSYS.SDO_DATUMS reference table contains one row for each valid DATUM specification in the wellknown text (WKT) description in the coordinate system definition. The WKT is described in Section 6.4.1.1.
The MDSYS.SDO_DATUMS table contains the columns shown in Table 63.
Table 63 MDSYS.SDO_DATUMS Table
Column Name  Data Type  Description 

NAME  VARCHAR2(64)  Name of the datum. Specify a value (Oraclesupplied or userdefined) from this column in the DATUM specification of the WKT for any userdefined 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 xaxis. 
SHIFT_Y  NUMBER  Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the yaxis. 
SHIFT_Z  NUMBER  Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the zaxis. 
ROTATE_X  NUMBER  Number of arcseconds of rotation about the xaxis. 
ROTATE_Y  NUMBER  Number of arcseconds of rotation about the yaxis. 
ROTATE_Z  NUMBER  Number of arcseconds of rotation about the zaxis. 
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 
HuTzuShan  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  WakeEniwetok 1960  Yacare 
Zanderij 
The MDSYS.SDO_ELLIPSOIDS reference table contains one row for each valid SPHEROID specification in the wellknown text (WKT) description in the coordinate system definition. The WKT is described in Section 6.4.1.1.
The MDSYS.SDO_ELLIPSOIDS table contains the columns shown in Table 64.
Table 64 MDSYS.SDO_ELLIPSOIDS Table
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 
The MDSYS.SDO_PROJECTIONS reference table contains one row for each valid PROJECTION specification in the wellknown text (WKT) description in the coordinate system definition. The WKT is described in Section 6.4.1.1.
The MDSYS.SDO_PROJECTIONS table contains the column shown in Table 65.
Table 65 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 userdefined 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 NearSide 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 JyllandFyn  Transverse Mercator Danish System 45 Bornholm 
Transverse Mercator Finnish KKJ  Transverse Mercator Sjaelland 
Universal Transverse Mercator  Van der Grinten 
Wagner IV  Wagner VII 
To create a userdefined coordinate system, add a row to the MDSYS.CS_SRS table. See Section 6.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 6.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:
MDSYS.SDO_ANGLE_UNITS (see Section 6.4.2) in a UNIT specification for angle units
MDSYS.SDO_DIST_UNITS (see Table 26 in Section 2.6) in a UNIT specification for distance units
MDSYS.SDO_DATUMS (see Section 6.4.3) in the DATUM specification, or a userdefined datum not in MDSYS.SDO_DATUMS
If you supply a userdefined datum, the datum name must be different from any datum name in the MDSYS.SDO_DATUMS table, and the WKT must specify at least the datum name and the spheroid (or ellipsoid) information listed in Section 6.4.1.1. If the shift, rotation, and scale parameters are all zero, you can omit them; however, if any of these parameter values are nonzero, you must specify them all.
MDSYS.SDO_ELLIPSOIDS (see Section 6.4.4) in the SPHEROID specification
If you supply a userdefined ellipsoid, the ellipsoid name must be different from any ellipsoid name in the MDSYS.SDO_ELLIPSOIDS table. You must also specify the semimajor axis and inverse flattening for a userdefined ellipsoid.
MDSYS.SDO_PROJECTIONS (see Section 6.4.5) in the PROJECTION specification
The name in each PARAMETER specification must be one of the following, depending on the projection that you use:
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 meters)
False_Northing
(in meters)
Perspective_Point_Height
(in meters)
Landsat_Number
(must be 1, 2, 3, 4, or 5)
Path_Number
Scale_Factor
Some of these parameters are appropriate for several projections. They are not all appropriate for every projection.
Example 62 creates a userdefined 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 62 Creating a UserDefined 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 63 creates a userdefined 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 semimajor 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 63 Creating a UserDefined 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);
The current release of Oracle Spatial includes the following functions and procedures for data transformation using coordinate systems:
SDO_CS.TRANSFORM function: Transforms a geometry representation using a coordinate system (specified by SRID or name).
SDO_CS.TRANSFORM_LAYER procedure: Transforms an entire layer of geometries (that is, all geometries in a specified column in a table).
SDO_CS.VALIDATE_WKT function: Validates the wellknown text (WKT) description associated with a specified SRID.
SDO_CS.VIEWPORT_TRANSFORM function: Transforms an optimized rectangle into a valid polygon for use with Spatial operators and functions.
Reference information about these functions and procedures is in Chapter 15.
Support for additional functions and procedures is planned for future releases of Oracle Spatial.
The following notes and restrictions apply to coordinate systems support in the current release of Spatial.
If you have geodetic data, see also Section 6.2 for considerations, guidelines, and additional restrictions.
For Spatial operators (described in Chapter 12) 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 querywindow geometry.
For SDO_GEOM package geometry functions (described in Chapter 13) that take two geometries as input parameters, both geometries must be based on the same coordinate system.
In the current release, the following functions are not supported with geodetic data:
All 3D formats of LRS functions (explained in Section 7.4)
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 localtangentplane 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 backtransform.
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 15.
Example 64 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 wellknown 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 wellknown name is "Longitude / Latitude (Arc 1950)".
Example 64 uses the geometries illustrated in Figure 21 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 64 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 65 includes the output of the SELECT statements in Example 64.
Example 64 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);  Note about areas of interest: cola_a (rectangle) and  cola_b (foursided polygon) are side by side (share one border).  cola_c is a small foursided 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 userdefined 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,  twodimensional 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,  twodimensional 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,  twodimensional 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,  twodimensional 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 (tablecolumn combination; here: cola_markets_cs and shape). INSERT INTO USER_SDO_GEOM_METADATA 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 65 shows the output of the SELECT statements in Example 64. 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 65 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