Spatial data types are used to store and process geospatial data. The Oracle mode of OceanBase Database supports the SDO_GEOMETRY spatial data type. SDO_GEOMETRY is used to store and process geometric data. It is a composite data type used to represent a two-dimensional or three-dimensional geometric figure.
The current version of OceanBase Database in Oracle mode supports only the following spatial object types defined by OpenGIS Consortium (OGC): Point, Linestring, Polygon, Multipoint, Multilinestring, Multipolygon, and Collection. Such a spatial object consists of a group of concatenated sequences of points. Both two-dimensional data and three-dimensional data are supported.
Notice
The current version of OceanBase Database does not support concatenating points by using arcs.
SDO_GEOMETRY features
Feature 1: SDO_GEOMETRY support
You can specify SDO_GEOMETRY as the data type and operate data of the SDO_GEOMETRY type in PL/SQL.
Feature 2: SDO_GEOMETRY member functions
The SDO_GEOMETRY data type provides member functions for the construction, query, and format conversion of spatial objects.
Construction: Spatial objects of the
SDO_GEOMETRYtype can be constructed by the default functionSDO_GEOMETRY()from well-known text (WKT) or well-known binary (WKB) data.Query:
SDO_GEOMETRYprovides some member functions for querying attributes of spatial objects. For example, you can callGET_DIMS()to obtain the number of dimensions of a geometry, callGET_GTYPE()to get the type code of a geometry, and callST_ISVALID()to test whether a geometry is valid.Format conversion:
SDO_GEOMETRYsupports conversion of spatial objects to other formats. For example, you can callGET_WKB()to convertSDO_GEOMETRYto the WKB format, callGET_WKT()to convertSDO_GEOMETRYto the WKT format, and callGET_GEOJSON()to convertSDO_GEOMETRYto the JSON format.
For more information about member functions of SDO_GEOMETRY, see Overview.
Feature 3: SDO_GEOMETRY data storage
You can define SDO_GEOMETRY columns in a table, which are then stored as BLOB columns.
Feature 4: SDO_GEOMETRY attribute access
SDO_GEOMETRY is a compound data type that contains multiple intrinsic attributes. You can access these attributes in PL/SQL.
SDO_GEOMETRY definition
CREATE TYPE sdo_geometry AS OBJECT (
SDO_GTYPE NUMBER,
SDO_SRID NUMBER,
SDO_POINT SDO_POINT_TYPE,
SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
SDO_ORDINATES SDO_ORDINATE_ARRAY);
Attributes
SDO_GTYPE
SDO_GTYPE indicates the type code of the geometry. The value is a NUMBER value in the DLTT format.
Fields in DLTT
D: the number of dimensions. For example, the value2indicates a 2D geometry, while the value3indicates a 3D geometry.L: the linear referencing system. For a 2D geometry, the value ofLis0.Note
In the current version of OceanBase Database,
Ldoes not support other values.TT: the specific geometry type. The value ranges from 01 to 07.
| Value | Geometry type | Description |
|---|---|---|
| DL01 | POINT | A geometry that contains one point. |
| DL02 | LINE | A geometry that contains one straight line. |
| DL03 | POLYGON | A geometry that contains one polygon. |
| DL04 | COLLECTION | A geometry that is a collection of different types of spatial objects. |
| DL05 | MULTIPOINT | A geometry that contains one or more points. |
| DL06 | MULTILINE | A geometry that contains one or more line segments. The current version of OceanBase Database supports straight line segments only. |
| DL07 | MULTIPOLYGON | A geometry that contains one or more polygons. |
SDO_SRID
SDO_SRID identifies the spatial reference system (SRS) of a geometry. The value is a NUMBER value. It determines the coordinate system and projection of the geometry.
Note
SDO_SRID has the same meaning as SRID in MySQL tenants, which specifies the ID of a SRS. For more information about the SRS in MySQL tenants, see SRS.
SDO_POINT
SDO_POINT defines the point coordinates of the geometry. The attribute contains the X, Y, and optional Z coordinates of the geometry.
SDO_POINT is an attribute of SDO_GEOMETRY objects and is used for storing coordinate values for Point-type geometries. When both the SDO_ELEM_INFO and SDO_ORDINATES attributes are NULL, and the SDO_POINT attribute has a valid value, an object is considered a Point-type geometry. In this case, SDO_POINT stores the specific coordinate values of the point. If the SDO_ELEM_INFO and SDO_ORDINATES attributes are not NULL, the SDO_POINT attribute is ignored. The coordinates of the geometry are stored in the SDO_ELEM_INFO and SDO_ORDINATES attributes.
SDO_POINT_TYPE object definition
CREATE TYPE sdo_point_type AS OBJECT (
X NUMBER,
Y NUMBER,
Z NUMBER
);
SDO_ELEM_INFO
SDO_ELEM_INFO defines geometric element information of the geometry. SDO_ELEM_INFO is a variable-length array consisting of NUMBER values, which can be divided into multiple triplets that explain the SDO_ORDINATES attribute. Each triplet set of numbers is interpreted as follows:
SDO_STARTING_OFFSET: the offset within theSDO_ORDINATESarray where the first coordinate for this element is stored. Offset values start at 1.SDO_ETYPE: the element type.SDO_ETYPEvalues 1, 2, 1003 (polygon outer ring, which must be specified in counterclockwise order), and 2003 (polygon inner ring, which must be specified in clockwise order) are considered simple elements. They are defined by a single triplet entry in theSDO_ELEM_INFOarray.SDO_INTERPRETATION: the connection sequence and other features of a simple element.
Values and semantics of SDO_ELEM_INFO
| SDO_ETYPE | SDO_INTERPRETATION | Meaning |
|---|---|---|
| 1 | 1 | A point. |
| 1 | n > 1 | A point cluster with n points. MultiPoint objects in a Collection object are also expanded to this type. |
| 2 | 1 | A line string whose vertices are connected by straight line segments. |
| 1003 or 2003 | 1 | A simple polygon whose vertices are connected by straight line segments. The last point specified must be exactly the same point as the first. 1003 indicates an outer ring and 2003 indicates an inner ring. |
| 1003 or 2003 | 3 | A rectangle (sometimes called optimized rectangle). Only the coordinates of the lower-left vertex and upper-right vertex need to be recorded. |
Example of SDO_ELEM_INFO_ARRAY
SDO_ELEM_INFO_ARRAY(1, 1003, 1)
In the above example, the first number 1 indicates to start from the first member of SDO_ORDINATE_ARRAY, the second number 1003 indicates a polygon outer ring, and the third number 1 indicates connection by straight line segments.
SDO_ORDINATES
SDO_ORDINATES is a variable-length array of no more than 1,048,576 NUMBER values, which indicates the coordinate values that make up the boundary of a geometry. This array must always be used in conjunction with the SDO_ELEM_INFO attribute to achieve self-interpretation of a geometry.
Example of SDO_ORDINATE_ARRAY
SDO_ORDINATE_ARRAY(10,105, 15,105, 20,110, 10,110, 10,105)
The above example defines an array of coordinates that represent a simple polygon. The array contains the X and Y coordinates of five vertices: (10,105), (15,105), (20,110), (10,110), and (10, 105). These coordinate values in sequence describe a simple polygon. Based on geometric element information provided by the SDO_ELEM_INFO attribute, these coordinate values can be interpreted to determine the boundary of the polygon.
Note
In the above example, each group of coordinate values defines a point on a 2D plane, representing the position of the geometry in the corresponding dimension. This example only specifies the X and Y coordinates, so the coordinate array does not have any Z coordinate values.
Examples
Create a 2D point with the type code 2001 and coordinates X = 10, Y = 20, and Z = NULL. Do not specify the SRID, SDO_ELEM_INFO, or SDO_ORDINATE_ARRAY.
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(10, 20, NULL), NULL, NULL)
References
- For more information about the member functions of
SDO_GEOMETRY, see Overview. - For more information about how to access the attributes of
SDO_GEOMETRY, see SDO_GEOMETRY attribute access.