Oracle Spatial - (Coordinate|Spatial Reference) System (SRID|EPSG)
Table of Contents
1 - About
In 1618, Rene Descartes had an idea… while lying in bed and watching a fly on the ceiling.
He could describe the location of the fly in terms of two numbers: its distance from the two walls.
He realized that this works even if the two walls were not perpendicular.
He realized that you could express geometry in algebra.
- The walls play role of what we now call axes.
- The two numbers are what we now call coordinates
A coordinate system (also called a spatial reference system) is a means of assigning coordinates to a location and establishing relationships between sets of such coordinates. It enables the interpretation of a set of coordinates as a representation of a position in a real world space.
The SDO_SRID attribute identify the associated coordinate System with the geometry. Its value can be null or one of the SDO_COORD_REF_SYS table.
|8307||Longitude / Latitude (WGS 84)||GEOGRAPHIC2D|
|90112||Netherlands National System||PROJECTED||onto a plane|
|28992||Amersfoort / RD New||PROJECTED|
The SRID 8307 (well-known name is “Longitude / Latitude (WGS 84)”) is probably the most widely used coordinate system, and it is the one used for global positioning system (GPS) devices. Google Maps uses the 3785.
SRID sometimes is called an “EPSG” code. The SRID/EPSG code is a defacto short-hand for the Well-Known-Text representations of projections.
2 - Articles Related
3 - Type of Coordinate System
3.1 - 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.
Local coordinates are Cartesian coordinates in a non-Earth (non-georeferenced) coordinate system.
3.2 - Geo coordinates
3.2.1 - Type
22.214.171.124 - 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.
126.96.36.199 - 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
A geodetic datum (or datum) is a means of shifting and rotating an ellipsoid to represent the figure of the Earth, usually as an oblate spheroid, that approximates the surface of the Earth locally or globally, and is the reference for the system of geodetic coordinates.
Each geodetic coordinate system is based on a datum.
3.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:
188.8.131.52 - 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.
184.108.40.206 - Performance
Spherical computations use more computing resources than Cartesian computations. Some operations using geodetic coordinates may take longer to complete than the same operations using Cartesian coordinates.
3.2.3 - Geodesy and Two-Dimensional Geometry (Cartesian coordinates)
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.
3.3 - Local Coordinate Support
Spatial provides a level of support for local coordinate systems. Local coordinate systems are often used in :
- CAD systems
- the relationship between the surveyed site and the rest of the world is not important
3.4 - Linear Referencing System
Linear referencing is a natural and convenient means to associate attributes or events to locations or portions of a linear feature.
It has been widely used in :
- transportation applications (such as for highways, railroads, and transit routes)
- utilities applications (such as for gas and oil pipelines).
The major advantage of linear referencing is its capability of locating attributes and events along a linear feature with only one parameter (usually known as measure) instead of two (such as longitude/latitude or x/y in Cartesian space). Sections of a linear feature can be referenced and created dynamically by indicating the start and end locations along the feature without explicitly storing them.
4 - MetaData
The coordinate systems functions and procedures use information provided in the tables and views supplied with Oracle Spatial. The tables and views are part of the MDSYS schema; however, public synonyms are defined, so you do not need to specify MDSYS. before the table or view name.
All tables are explained in this paragraph from the user guide.
5 - Coordinate System Transformation
This section presents a simplified example that uses coordinate system transformation functions and procedures.
5.1 - Polygon
-- 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'; -- Transform the geometry to the coordinate system 90112 -- and create a table to store the new geometries CREATE TABLE europa_landen_90112 AS SELECT C.Code, C.Name, C.COUNTRY_UK, Sdo_Cs.Transform(C.Geometry, M.Diminfo, 90112) geometry FROM europa_landen c, USER_SDO_GEOM_METADATA M WHERE M.TABLE_NAME = 'EUROPA_LANDEN' AND M.COLUMN_NAME = 'GEOMETRY';
Don't forget to create the metadata before using this new layer
INSERT INTO User_Sdo_Geom_Metadata VALUES ( 'EUROPA_LANDEN_90112', 'GEOMETRY', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, .005), MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, .005)), 90112 ); CREATE INDEX EUROPA_LANDEN_90112_sdx ON EUROPA_LANDEN_90112 ( GEOMETRY ) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
5.2 - Point
6 - EPSG TO
6.1 - EPSG to SRID
SELECT SDO_CS.MAP_EPSG_SRID_TO_ORACLE(28992) FROM DUAL; SELECT SDO_CS.MAP_ORACLE_SRID_TO_EPSG(28992) FROM dual; --Gets the version number of the EPSG dataset used by Oracle Spatial. SELECT SDO_CS.GET_EPSG_DATA_VERSION FROM DUAL;
6.2 - PRJ TO EPSG
A shapefile has a prj file that can be converted to a EPSG through this web site: