Oracle spatial 

Contents:

• Introduction
• R-Tree spatial indexing
• SDO_GEOMETRY

Introduction

Oracle spatial is an integrated component of oracle enterprise edition and it enables us to store, access and analyze spatial data in oracle database.

When we install and configure oracle database software, a schema named MDYS is created by default on the database.

MDSYS schema is the owner of the spatial types, procedures, functions, packages and Meta data. And it is locked & expired by default.

D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Wed Apr 6 19:39:44 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

SQL> conn sys/orcl@orcl as sysdba
Connected.
SQL> col username format a12;
SQL> col account_status format a12;
SQL> col created format a12;
SQL> col profile format a12;
SQL> select  username,user_id, account_status,created,profile from dba_users where username='MDSYS';

USERNAME        USER_ID ACCOUNT_STATUS       CREATED      PROFILE
------------ ---------- -------------------- ------------ ------------
MDSYS                90 EXPIRED & LOCKED     08-MAR-17    DEFAULT

SQL>

CAD/CAM system data such as floor plan, road map , earth-relative spatial data (for example ATM location) are common examples of spatial data.

The spatial information that is stored in the database is displayed in maps and has two components:

• Location: Defines the x and y position of a feature.
• Non-spatial attributes: Represents the characteristics of a feature. For instance, information such as the owner of ATM machine, branch code and branch name, and so on.

Oracle spatial supports many geometric primitive types such as point, oriented point, line string, Arc line string, compound line string, polygon, arc polygon, and so on.

R-Tree spatial indexing

Oracle spatial uses R-Tree spatial index for indexing of elements within geometry just to improve spatial query performance. Moreover, while creating a spatial index for geometry column of a table, an approximation of each geometry will also created and stored in the database. Besides, R-Tree indexing is based on minimum bounding rectangles (MBR).

SDO_GEOMETRY

Geometry is stored as an object in oracle database table, in a single row and in a spatial column of SDO_GEOMETRY object type. Furthermore, the SDO_GEOMETRY object type has many methods that provide access to its attributes.

And the structure of SDO_GEOMETRY object type is:

SQL> describe sdo_geometry;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 SDO_GTYPE                                          NUMBER

 SDO_SRID                                           NUMBER

 SDO_POINT                                          MDSYS.SDO_POINT_TYPE

 SDO_ELEM_INFO                                      MDSYS.SDO_ELEM_INFO_ARRAY

 SDO_ORDINATES                                      MDSYS.SDO_ORDINATE_ARRAY

As you can see in the above query, the SDO_GEOMETRY object has five fields as follows:

SDO_GTYPE: Defines the type of geometry such as the number of dimensions, the linear reference system (LRS) measure position and the geometry type stored in the object.
SDO_SRID:  Indicates the coordinate system associated with the geometry.
SDO_POINT_TYPE:  It is a field in the SDO_GEOMETRY object and stores point geometries. And the points can be two, three or four dimensional.
SDO_ELEM_INFO_ARRAY: It is also a field in the SDO_GEOMETRY object that describes the elements stored in the geometry.
SDO_ORDINATE_ARRAY: Contains all the ordinates for all the elements of the geometry. We can create tables as we did in native oracle data type and add spatial column of SDO_GEOMETRY object type to store geometry.

Example:

SQL> conn mereba/mereba@orcl

Connected.

SQL> Create table ATMaddress (  ATM_name varchar2(30),

      Branch_code  number,  Geom SDO_GEOMETRY);

Table created.

--- To describe the structure of atmaddress table
SQL> describe   atmaddress;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ATM_NAME                                           VARCHAR2(30)

 BRANCH_CODE                                        NUMBER

 GEOM                                               PUBLIC.SDO_GEOMETRY

SQL>

SDO_GEOMETRY  member methods:

Some of the member methods of the sdo_geometry object type includes, but not limited to:

• GET_GTYPE () Method: Returns the geometry type of a geometry object.
• GET_DIMS () Method:  Returns the number of dimensions of a geometry object.
• GET_WKB () Method: Returns the WKB format of the geometry.
• GET_WKT () Method: Returns the WKT format of the geometry.
• ST_CoordDim () Method: Has the same functionality with GET_DIMS () method.
• ST_IsValid () Method: Validates the geometry and returns 1 if valid unless returns 0.