1 / 14

Oracle Spatial

Oracle Spatial. Extension of the RDBMS Oracle by spatial data types and operations introduced in version 8 (current version is 10G Release 2) is based on the object relational extensions of Oracle user defined data types and object classes is possible

wind
Download Presentation

Oracle Spatial

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Spatial • Extension of the RDBMS Oracle by spatial data types and operations • introduced in version 8 (current version is 10G Release 2) • is based on the object relational extensions of Oracle • user defined data types and object classes is possible • Fields may also contain objects and nested tables (Non-First Normal Form, NF2) • Object classes may be augmented by methods • Support of class hierarchies (incl. inheritance) • besides normal relations also ‘object tables‘ can be defined • Spatial data type is realised by the object class SDO_GEOMETRY and its methods

  2. Features of Oracle Spatial 10G (I) • Representation of 0-2 dimensional geometric and topological primitives, complexes, and aggregates • Coordinate values can have 2-4 dimensions • Representation of 3D objects is possible • allerdings keine Konzepte für Volumina enthalten: keine 3D-Topologie; keine dreidimensionalen Funktionen und Operatoren • pragmatisches Vorgehen: Speicherung der Boundary Representation eines 3D-Objekts als Flächenaggregat mit 3D-Koordinatenwerten • Geometric and topological functions (only for 2D coordinates) • among others: Egenhofer-relations and 2D CSG operations • Spatial indexing using R-Trees (2D-4D) and Quadtrees (2D) • especially important for spatial joins

  3. Features of Oracle Spatial 10G (II) • Coordinate Reference Systems (CRS) within the DB • geodetic and projective CRS • linear referencing systems (e.g. for street networks or hydrography) • methods for coordinate transformations are included within the DB • Graph algorithms for network analyses (shortest paths, maximum flows) • Functions for spatial analysis • clustering • Geocoding of spatial data • e.g. association of addresses and coordinates • Representation of regular grids of (nearly) arbitrary size (GeoRaster) • each raster cell can hold a n-dimensional vector of values • GeoRaster are georeferenced • efficient access methods (e.g. retrieval of a specific rectangular area)

  4. Spatial Datatype in Oracle: SDO_GEOMETRY • Object class, that is defined within the Oracle user accound MDSYS • Access or referencing: MDSYS.SDO_GEOMETRY Attributes of class SDO_GEOMETRY: • SDO_GTYPE NUMBER • Geometry type (e.g. 2003 = Polygon with two-dimensional coordinates) • SDO_SRID NUMBER • Identifier (ID) of the spatial reference system within the DB • SDO_POINT MDSYS.SDO_POINT_TYPE • Point geometry • SDO_ELEM_INFO MDSYS.SDO_ELEM_INFO_ARRAY • Structure and interpretation of the coordinate list • SDO_ORDINATES MDSYS.SDO_ORDINATE_ARRAY • List of coordinates (cf. ‘Spaghetti‘ representation)

  5. Example for a Table with a Spatial Attribute CREATE TABLE TabWithGeometry ( ID NUMBER(8), DESCRIPTION VARCHAR2(30), GEOMETRY MDSYS.SDO_GEOMETRY);

  6. Geometry Types • All geometries may have 2-4D coordinates • Polygons can have holes • Besides, there also exist aggregates, in which geometries of the same type or of mixed type can be contained, e.g.: • MultiPoint • MultiPolygon • MultiGeometry

  7. Geometry Metadata • Any column of type SDO_GEOMETRY must be explicitly registered within the DBMS • Ensures automatic updating of spatial indices • Naming of coordinate axes and restriction of value domains • Definition of a tolerance value for spatial queries and operations • System tables for the storage and query of geometry metadata • USER_SDO_GEOM_METADATA • Metadata for all spatial attributes of the current user • ALL_SDO_GEOM_METADATA • Metadata for all spatial attributes of the whole database (of all user accounts) with read permission for the current user • DBA_SDO_GEOM_METADATA • Metadata for all spatial attributes of the whole database (of all user accounts); only accessible by database administrators

  8. USER_SDO_GEOM_METADATA For any column of type SDO_GEOMETRY a row has to be inserted into USER_SDO_GEOM_METADATA. Attributes: • TABLE_NAME VARCHAR2(32) • Name of the table containing the column of type SDO_GEOMETRY • COLUMN_NAME VARCHAR2(32) • Name of the column containing the SDO_GEOMETRY • DIMINFO MDSYS.SDO_DIM_ARRAY • Identifier, extent, and tolerance of each dimension. Here, objects from the class MDSYS.SDO_DIM_ARRAY have to be specified. • SRID NUMBER • ID of the spatial reference system (foreign key referring to the system table MDSYS.CS_SRS). If unknown or undefined, enter NULL value.

  9. SDO_DIM_ARRAY • SDO_DIM_ARRAY consists of one SDO_DIM_ELEMENT per dimension of the coordinate values of the geometry Structure of SDO_DIM_ELEMENT: • SDO_DIMNAME VARCHAR2(64) • Identifier (name) of the dimension; e.g. ‘easting‘ or ‘latitude‘ • SDO_LB NUMBER • Minimum coordinate value of that dimension • SDO_UB NUMBER • Maximum coordinate value of that dimension • SDO_TOLERANCE NUMBER • minimal distance up to which two coordinate values are considered as being equal

  10. Continuation of the Example: Creation of Metadata INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES(‘TabWithGeometry‘, ‘Geometry‘, MDSYS.SDO_DIM_ARRAY( MDSYS.SDO_DIM_ELEMENT(‘X‘, -180.0, 180.0, 0.00000005), MDSYS.SDO_DIM_ELEMENT(‘Y‘, -90.0, 90.0, 0.00000005) ), null );

  11. SDO_GTYPE Spatial refe-rence system SDO_ELEM_INFO SDO_ORDINATES Insertion of Spatial Data (I) INSERT INTO TabWithGeometry VALUES (1, ‘A simple point‘, MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(10,11,NULL), NULL, NULL) ); inserts a tuple with a point object at coordinates (10,11) into the table.

  12. Point in theWell-Known-Text format Constructor of the class SDO_GEOMETRY creates from Well-Known-Text geometries corresponding Oracle Spatial geometries Insertion of Spatial Data (II) INSERT INTO TabWithGeometry VALUES (1, ‘A simple point‘, MDSYS.SDO_GEOMETRY(‘POINT(10 11)‘) ); inserts a tuple with a point object at coordinates (10,11) into the table. Same example as before, but for the specification of the geometry object the Well-Known-Text format (WKT) is used (cf. lecture 8, slide 16)

  13. SDO_GTYPE: Polygon Spatial refe- rence system 1st coordinate starts at 1st position; outer ring; straight line segments It is not a simple point! 5 coordinate pairs define the closed ring; the last pair has to match the first pair Insertion of Spatial Data (III) INSERT INTO TabWithGeometry VALUES (2, ‘A polygon‘, MDSYS.SDO_GEOMETRY(2003, NULL, NULL, MDSYS.SDO_ELEM_INFO(1,1003,1), MDSYS.SDO_ORDINATES(9,9, 12,9, 12,10, 9,10, 9,9) ) ); inserts a tuple with a polygon into the table.

  14. SDO_GTYPE: Polygon Spatial refe- rence system 1st coordinate starts at 1st position; outer ring; straight line segments It is not a simple point! 1st coordinate begins at 11th position; inner ring; straight line segments The first 5 coordinate pairs define the outer ring; the following 4 the inner ring Insertion of Spatial Data (IV) INSERT INTO TabWithGeometry VALUES (3, ‘Polygon with hole‘, MDSYS.SDO_GEOMETRY(2003, NULL, NULL, MDSYS.SDO_ELEM_INFO(1,1003,1, 11,2003,1), MDSYS.SDO_ORDINATES(9,9, 12,9, 12,10, 9,10, 9,9, 10,10, 10.5,10.5, 11,10, 10,10) ) ); inserts a polygon (rectangle) with (triangular) hole into the table.

More Related