Overview of spatial data types

Spatial data types are used to store and process geographic space information. OceanBase Database in Oracle-compatible mode supports the SDO_GEOMETRY spatial data type. The SDO_GEOMETRY data type is a composite data type used to store and process geometric data, representing two-dimensional or three-dimensional geometric shapes.

OceanBase Database in Oracle-compatible mode currently supports only seven spatial object types defined by the OpenGIS Consortium (OGC): Point (point), Linestring (line), Polygon (polygon), Multipoint (multiple points), Multilinestring (multiple lines), Multipolygon (multiple polygons), and Collection (collection). These spatial objects are composed of a sequence of connected points. In terms of spatial dimensions, it supports two-dimensional and three-dimensional spatial data.

SDO_GEOMETRY feature

Feature 1: SDO_GEOMETRY support

You can define a column of the SDO_GEOMETRY data type and perform operations on the SDO_GEOMETRY data type in PL/SQL.

Feature 2: SDO_GEOMETRY member functions

The SDO_GEOMETRY data type provides member functions for constructing, querying, and converting spatial objects.

• Constructor functions: You can create SDO_GEOMETRY objects by using the default constructor (SDO_GEOMETRY()), Well-Known-Binary (WKB) data format, and Well-Known-Text (WKT) data format.

• Query functions: The SDO_GEOMETRY data type provides member functions for querying the properties of spatial objects. For example, the get_dims function is used to obtain the dimension information of a geometry, the get_gtype function is used to obtain the type code of a geometry, and the st_isvalid function is used to check whether a geometry is valid.

• Conversion functions: The SDO_GEOMETRY data type supports converting spatial objects to other formats. For example, the get_wkb function is used to convert SDO_GEOMETRY to the Well-Known-Binary (WKB) format, the get_wkt function is used to convert SDO_GEOMETRY to the Well-Known-Text (WKT) format, and the get_geojson function is used to convert SDO_GEOMETRY to the JSON format.

For more information about the member functions of SDO_GEOMETRY, see Overview of spatial functions.

Feature 3: SDO_GEOMETRY type storage

You can define a column of the SDO_GEOMETRY data type and store the SDO_GEOMETRY data type as a BLOB column.

Feature 4: Access to SDO_GEOMETRY properties

As a complex data type, SDO_GEOMETRY contains multiple intrinsic properties. You can access these properties in SQL/PL.

Definition of SDO_GEOMETRY

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);

Parameters

SDO_GTYPE

The SDO_GTYPE parameter specifies the type code of the geometry, which is a NUMBER value in the format DLTT.

Description of the DLTT format:

• D: represents the spatial dimension. The value can be 2 or 3, indicating a two-dimensional or three-dimensional space.

• L: represents the linear referencing system. For a two-dimensional spatial object, the value of L is 0.

  Note

  At present, OceanBase Database does not support other values for L.

• TT: represents the geometry type, which can be 01 to 07.

SDO_SRID

The SDO_SRID parameter specifies the spatial reference identifier, which is a NUMBER value. It determines the coordinate system and projection method of the geometry.

SDO_POINT

The SDO_POINT parameter specifies the coordinates of the geometry. It contains the X, Y, and optional Z coordinates of the geometry.

SDO_POINT is a property of the SDO_GEOMETRY object, used to store the coordinates of a Point geometry. When both the SDO_ELEM_INFO and SDO_ORDINATES parameters are set to NULL and the SDO_POINT parameter has valid values, the object is considered a Point geometry. In this case, the SDO_POINT property stores the specific coordinate values of the Point. If the SDO_ELEM_INFO and SDO_ORDINATES parameters are not NULL, the content stored in the SDO_POINT parameter is ignored, and the specific coordinates of the geometry are stored using the SDO_ELEM_INFO and SDO_ORDINATES parameters.

Definition of the SDO_POINT_TYPE object:

CREATE TYPE sdo_point_type AS OBJECT (
  X NUMBER,
  Y NUMBER,
  Z NUMBER
);

SDO_ELEM_INFO

The SDO_ELEM_INFO parameter specifies the geometry elements of the geometry. SDO_ELEM_INFO is a variable-length array of NUMBER values. This array consists of multiple 3-tuples that describe the SDO_ORDINATES parameter. The 3-tuples include:

• SDO_STARTING_OFFSET: specifies the starting index in the SDO_ORDINATES array of the coordinates of the first element of the geometry. The index starts from 1.
• SDO_ETYPE: specifies the type of the geometry. 1 or 2 indicates a single linear segment. 1003 indicates the outer ring of a polygon, which must be counterclockwise. 2003 indicates the inner ring of a polygon, which must be clockwise.
• SDO_INTERPRETATION: specifies how to connect the points of a single element. For a single point, this parameter is set to 1.

The values and their meanings of SDO_ELEM_INFO:

Example of the SDO_ELEM_INFO_ARRAY:

SDO_ELEM_INFO_ARRAY(1, 1003, 1)

In the preceding example, the value 1 of the first parameter indicates that the coordinates start from the first member of the SDO_ORDINATE_ARRAY. The value 1003 of the second parameter indicates that the geometry is the outer ring of a polygon. The value 1 of the third parameter indicates that the points are connected by straight lines.

SDO_ORDINATES

The SDO_ORDINATES parameter specifies the coordinates of the geometry. It is a variable-length array of NUMBER values with a maximum length of 1,048,576. The SDO_ORDINATES array stores the coordinates of the spatial object and, when used in conjunction with the SDO_ELEM_INFO parameter, describes the geometry self-explanatorily.

Example of the SDO_ORDINATE_ARRAY:

SDO_ORDINATE_ARRAY(10,105, 15,105, 20,110, 10,110, 10,105)

In the preceding example, the coordinate array represents a simple polygon. The array contains the coordinates of five vertices. Each vertex has X and Y coordinates, as follows: (10, 105), (15, 105), (20, 110), (10, 110), and (10, 105). The coordinates are arranged in sequence to form a simple polygon. The geometry elements provided by the SDO_ELEM_INFO parameter are used to explain the coordinates and determine the boundaries of the polygon.

Examples

Create a two-dimensional point geometry object with type code 2001 and coordinates X=10, Y=20, and Z= NULL. No spatial reference identifier (SRID), geometry element information SDO_ELEM_INFO, or specific coordinate values SDO_ORDINATE_ARRAY are specified.

SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(10, 20, NULL), NULL, NULL)

References

• For more information about the SDO_GEOMETRY member functions, see Overview of spatial functions.
• For more information about accessing the SDO_GEOMETRY attributes, see Accessing SDO_GEOMETRY attributes.