Release 8.1.7
Part Number A85337-01
 Library |
 Product |
 Contents |
 Index |
| Oracle Spatial User's Guide and Reference Release 8.1.7 Part Number A85337-01 |
|
This appendix describes the coordinate system transformation capabilities of Oracle Spatial. The coordinate systems application programming interface (API) integrates support into Oracle8i for storing and manipulating SDO_GEOMETRY objects in a variety of coordinate systems. (Coordinate systems are sometimes called spatial reference systems.)
For reference information about coordinate systems functions and procedures, see Chapter 8.
Before Oracle Spatial release 8.1.6, geometries (objects of type SDO_GEOMETRY) were stored as strings of coordinates without reference to any specific coordinate system. For definitions of SDO_GEOMETRY objects, users were instructed to set the SDO_SRID value (intended for future coordinate system support use) to a null value, and in fact this instruction appeared in the Oracle8i Spatial User's Guide and Reference for release 8.1.6. The Spatial functions and operators always assumed a coordinate system that had the properties of an orthogonal Cartesian system. With such a system, if Earth-based geometries are stored in latitude and longitude coordinates, Spatial functions and operators sometimes do not provide correct results in these coordinates.
With coordinate system support in Oracle Spatial, you can freely convert data from one coordinate system to another coordinate system, and Spatial functions, operators, and utilities provide correct and unambiguous results in whatever coordinate system the data is stored, particularly relating to measurements on the Earth's surface. Moreover, Spatial operators for queries and joins perform accurate computations with data that uses different coordinate systems. (However, see Section D.5 for any restrictions and problems in the current release.)
This section explains important terms and concepts related to coordinate systems 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 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 D.2.5).
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.
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, specifically datum transformation, is 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.
The coordinate systems functions and procedures use information provided in a table and other objects supplied with Oracle Spatial:
The MDSYS.CS_SRS table associates each coordinate system with its well-known text description, which is in conformance with the standard published by the OpenGIS Consortium (http://www.opengis.org).
The MDSYS.CS_SRS reference table is included with Oracle Spatial, and it is used by coordinate systems functions and procedures. This table contains over 900 rows, one for each valid coordinate system.
The MDSYS.CS_SRS table contains the columns shown in Table D-1.
| 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 |
INTEGER |
The unique ID number (Spatial Reference ID) for a coordinate system. |
|
AUTH_SRID |
INTEGER |
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 D.3.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. |
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. 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.
Underlying the CS_SRS table are data and code to represent the ellipsoids and projections in common use around the world. Table D-2 lists the supported map projections.
Table D-3 lists the supported ellipsoids.
The current release of Oracle Spatial includes the following functions and procedures:
Reference information about these functions and procedures is in Chapter 8.
Support for additional functions and procedures is planned for future releases of Oracle Spatial.
The current release of Oracle Spatial provides the first phase of support for coordinate systems. Further support is planned for future releases.
The following restrictions and problems apply to the current release.
In the current release, Spatial functions and operators do not necessarily return precisely correct results with geometries whose coordinates are expressed as longitude and latitude values. 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.
As a workaround, first transform the geometries of interest to a projection coordinate system that is conformant to the local space of the geometries. Then, use the Spatial functions and operators with the transformed geometries.
In a future release, support is planned for correct results in all cases with Spatial functions and operators using geometries with longitude/latitude coordinates.
This section presents a simplified example that uses coordinate system functions and procedures. It refers to concepts that were explained in this appendix and uses functions documented in Chapter 8.
Example D-1 uses the same geometry data (cola markets) as in Section 2.1, except that instead of null SRID values, the 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 D-1 uses the geometries illustrated in Figure 2-1 in Section 2.1.
Example D-1 does the following:
Example D-2 includes the output of the SELECT statements in Example D-1.
CREATE TABLE cola_markets ( mkt_id NUMBER PRIMARY KEY, name VARCHAR2(32), shape MDSYS.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 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,3), -- one rectangle (1003 = exterior) MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to -- define rectangle (lower left and upper right) ) ); -- 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 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 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) ) ); -- Now insert an area of interest for Cola D. This is a -- circle with a radius of 2. It is completely outside the -- first three areas of interest. INSERT INTO cola_markets VALUES( 4, 'cola_d', MDSYS.SDO_GEOMETRY( 2003, -- 2-dimensional polygon 8307, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,4), -- one circle MDSYS.SDO_ORDINATE_ARRAY(8,7, 10,9, 8,11) ) ); --------------------------------------------------------------------------- -- 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 and shape). INSERT INTO USER_SDO_GEOM_METADATA VALUES ( 'cola_markets', 'shape', MDSYS.SDO_DIM_ARRAY( -- 20X20 grid, virtually zero tolerance MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005), MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005) ), 8307 -- SRID for ''Longitude / Latitude (WGS 84)' coordinate system ); ------------------------------------------------------------------- -- CREATE THE SPATIAL INDEX -- ------------------------------------------------------------------- CREATE INDEX cola_spatial_idx ON cola_markets(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('SDO_LEVEL = 8'); ------------------------------------------------------------------- -- TEST COORDINATE SYSTEMS FUNCTIONS AND PROCEDURES -- ------------------------------------------------------------------- -- 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 c, user_sdo_geom_metadata m WHERE m.table_name = 'COLA_MARKETS' 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 c, user_sdo_geom_metadata m WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' AND c.name = 'cola_c'; -- Transform the entire SHAPE layer and put results in the table -- named cola_markets_8199, which the procedure will create. EXECUTE SDO_CS.TRANSFORM_LAYER('COLA_MARKETS','SHAPE','COLA_MARKETS_8199',8199); -- Select all from the old (existing) table. SELECT * from cola_markets; -- Select all from the new (layer transformed) table. SELECT * from cola_markets_8199; -- Show metadata for the new (layer transformed) table. DESCRIBE cola_markets_8199;
Example D-2 shows the output of the SELECT statements in Example D-1. Notice the slight differences between the coordinates in the original geometries (SRID 8307) and the transformed coordinates (SRID 8199) -- for example, (1, 1, 5, 7) and (1.00078606, 1.00272755, 5.00069866, 7.00321633) for cola_a.
SQL> -- Return the transformation of cola_c using to_srid 8199 ('Longitude / Latitude (Arc 1950)') SQL> SELECT c.name, SDO_CS.TRANSFORM(c.shape, m.diminfo, 8199) 2 FROM cola_markets c, user_sdo_geom_metadata m 3 WHERE m.table_name = 'COLA_MARKETS' 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.00074116, 3.00289624, 6.0006707, 3.00289431, 6.00067234, 5.00305745, 4.0007 1964, 5.00305956, 3.00074116, 3.00289624)) 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 c, user_sdo_geom_metadata m 3 WHERE m.table_name = 'COLA_MARKETS' 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.00074116, 3.00289624, 6.0006707, 3.00289431, 6.00067234, 5.00305745, 4.0007 1964, 5.00305956, 3.00074116, 3.00289624)) SQL> -- Transform the entire SHAPE layer and put results in the table SQL> -- named cola_markets_8199, which the procedure will create. SQL> EXECUTE SDO_CS.TRANSFORM_LAYER('COLA_MARKETS', 'SHAPE', 'COLA_MARKETS_ 8199', 8199); PL/SQL procedure successfully completed. SQL> SQL> -- Select all from the old (existing) table. SQL> SELECT * from cola_markets; 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, 3), SDO_ORDINATE_ARR AY(1, 1, 5, 7)) 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, 4), SDO_ORDINATE_ARR AY(8, 7, 10, 9, 8, 11)) SQL> SQL> -- Select all from the new (layer transformed) table. SQL> SELECT * from cola_markets_8199; SDO_ROWID ------------------ GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- AAAA1BAABAAACcHAAA SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR AY(1.00078606, 1.00272755, 5.00069866, 7.00321633)) AAAA1BAABAAACcHAAB SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(5.00069355, 1.00272665, 8.00062193, 1.00272605, 8.00062526, 6.00313458, 5.000 69866, 7.00321633, 5.00069355, 1.00272665)) SDO_ROWID ------------------ GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- AAAA1BAABAAACcHAAC SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(3.00074116, 3.00289624, 6.0006707, 3.00289431, 6.00067234, 5.00305745, 4.0007 1964, 5.00305956, 3.00074116, 3.00289624)) AAAA1BAABAAACcHAAD SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 4), SDO_ORDINATE_ARR AY(8.00062651, 7.00321213, 10.0005803, 9.00335882, 8.00063347, 11.0035044)) SQL> -- Show metadata for the new (layer transformed) table. SQL> DESCRIBE cola_markets_8199; Name Null? Type ----------------------------------------- -------- ---------------------------- SDO_ROWID ROWID GEOMETRY MDSYS.SDO_GEOMETRY
This section lists the coordinate systems error messages, including the cause and recommended user action for each.
ORA-13276 internal error [%s] in coordinate transformation
Cause: OCI internal error.
Action: Contact Oracle Support Services with the exact error message text.
ORA-13278 failure to convert SRID to native format
Cause: OCI internal error.
Action: Contact Oracle Support Services with the exact error message text.
ORA-13281 failure in execution of sql statement to retrieve WKT
Cause: OCI internal error or SRID does not match a table entry.
Action: Check that a valid SRID is being used.
ORA-13282 failure on initialization of coordinate transformation
Cause: Parsing error on source or destination coordinate system WKT or incompatible coordinate systems.
Action: Check validity of WKT for table entries and the legitimacy of the requested transformation.
ORA-13283 failure to get new geometry object for conversion in place
Cause: OCI internal error.
Action: Contact Oracle Support Services with the exact error message text.
ORA-13284 failure to copy geometry object for conversion in place
Cause: OCI internal error.
Action: Contact Oracle Support Services with the exact error message text.
ORA-13285 Geometry coordinate transformation error
Cause: A coordinate pair was out of valid range for a conversion/projection.
Action: Check that data to be transformed is consistent with the desired conversion/projection.
ORA-13287 can't transform unknown gtype
Cause: A geometry with an SDO_GTYPE value of <= 0 was specified for transformation. Only an SDO_GTYPE value >= 1 is allowed.
Action: Specify an appropriate SDO_GTYPE value.
ORA-13288 Point coordinate transformation error
Cause: An internal error occurred while transforming points.
Action: Check the accompanying error messages.
ORA-13294 Cannot transform geometry containing circular arcs
Cause: It is impossible to transform a 3-point representation of a circular arc without distortion.
Action: Make sure a geometry does not contain circular arcs.
ORA-13300 Single point transform error
Cause: Low-level coordinate transformation error trap.
Action: Check the accompanying error messages.
ORA-13303 failure to retrieve a geometry object from a table
Cause: OCI internal error.
Action: Contact Oracle Support Services with the exact error message text.
ORA-13304 failure to insert a transformed geometry object in a table
Cause: OCI internal error.
Action: Contact Oracle Support Services with the exact error message text.
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|