Three-Dimensional Spatial Objects

Oracle Spatial supports the storage and retrieval of three-dimensional spatial data, which can include points, point clouds (collections of points), lines, polygons, surfaces, and solids.

Note: Three-dimensional spatial objects are supported only if Oracle JVM is enabled on your Oracle Autonomous AI Database Serverless deployments. To enable Oracle JVM, see Use Oracle Java in Using Oracle Autonomous AI Database Serverless for more information.

Table: SDO_GEOMETRY Attributes for Three-Dimensional Geometries shows the SDO_GTYPE and element-related attributes of the SDO_GEOMETRY type that are relevant to three-dimensional geometries. (The SDO_GEOMETRY type is explained in SDO_GEOMETRY Object Type.)

Table: SDO_GEOMETRY Attributes for Three-Dimensional Geometries

Type of 3-D Data SDO_GTYPE Element Type, Interpretation in SDO_ELEM_INFO
Point 3001 Does not apply. Specify all 3 dimension values in the SDO_POINT_TYPE attribute.
Line 3002 2, 1
Polygon 3003

1003, 1: planar exterior polygon

2003, 1: planar interior polygon

1003, 3: planar exterior rectangle

2003, 3: planar interior rectangle
Surface 3003 1006, 1: surface (followed by element information for the polygons)
Collection 3004 Same considerations as for two-dimensional
Multipoint (point cloud) 3005 1, n (where n is the number of points)
Multiline 3006 2, 1 (same as for Line)
Multisurface 3007 Element definitions for one or more surfaces
Solid 3008

Simple solid formed by a single closed surface: one element type 1007, followed by one element type 1006 (the external surface) and optionally one or more element type 2006 (internal surfaces)

Composite solid formed by multiple adjacent simple solids: one element type 1008 (holding the count of simple solids), followed by any number of element type 1007 (each describing one simple solid)
Multisolid 3009 Element definitions for one or more simple solids (element type 1007) or composite solids (element type 1008)

The following spatial operators consider all three dimensions in their computations:

The other operators consider only the first two dimensions. For some of preceding operators the height information is ignored when dealing with geodetic data, as explained later in this section. (Spatial operators are described in Spatial Operators.)

The SDO_GEOM.SDO_VOLUME function applies only to solid geometries, which are by definition three-dimensional. For information about support for three-dimensional geometries with other SDO_GEOM subprograms, see the usage information in SDO_GEOM Package (Geometry).

For distance computations with three-dimensional geometries:

To have any functions, procedures, or operators consider all three dimensions, you must specify PARAMETERS ('sdo_indx_dims=3') in the CREATE INDEX statement when you create the spatial index on a spatial table containing Geographic3D data (longitude, latitude, ellipsoidal height). If you do not specify that parameter in the CREATE INDEX statement, a two-dimensional index is created.

For spatial functions, procedures, and operators that consider all three dimensions, distance and length computations correctly factor in the height or elevation. For example, consider two three-dimensional points, one at the origin of a Cartesian space (0,0,0), and the other at X=3 on the Y axis and a height (Z) of 4 (3,0,4).

However, for the following operators and subprograms, when dealing with geodetic data, the distances with three-dimensional geometries are computed between the “ground” representations (for example, the longitude/latitude extent of the footprint of a building), and the height information is approximated:

For a two-dimensional query window with three-dimensional data, you can use the SDO_FILTER operator, but not any other spatial operators.

For examples of creating different types of three-dimensional spatial geometries, see Three-Dimensional Geometry Types. That section also includes an example showing how to update the spatial metadata and create spatial indexes for three-dimensional geometries.

For information about support for three-dimensional coordinate reference systems, see Three-Dimensional Coordinate Reference System Support.

Three-dimensional support does not apply to many spatial aggregate functions and PL/SQL packages and subprograms. The following are supported for two-dimensional geometries only:

Table: How Geodetic 3D Calculations Are Performed describes how Oracle Spatial internally performs certain geodetic three-dimensional calculations.

Table: How Geodetic 3D Calculations Are Performed

Type of Calculation Internal Calculations Performed
ANYINTERACT The input geometries are transformed using Gnomonic transformation; then the ANYINTERACT relationship is computed with the resulting geometries.
Area The input geometry is projected onto a local tangent plane; then the area is computed with the resulting input geometry.
Distance or Length The 2D precise ellipsoidal distance is computed using the longitude/latitude of the two closest points of approach; then the height or length difference is included using an approximation.
Volume The input geometry is projected onto a local tangent plane; then the volume is computed with the resulting input geometry.

Modeling Surfaces

A surface contains an area but not a volume, and it can have two or three dimensions. A surface is often constructed by a set of planar regions.

Surfaces can be modeled as surface-type SDO_GEOMETRY objects or, if they are very large, as SDO_TIN objects. The surface-type in SDO_GEOMETRY can be an arbitrary surface defining a contiguous area bounded by adjacent three-dimensional polygons. The number of polygons in the SDO_GEOMETRY is limited by the number of ordinates that can be in the SDO_ORDINATES_ARRAY. An SDO_TIN object, on the other hand, models the surface as a network of triangles with no explicit limit on the number of triangles.

Surfaces are stored as a network of triangles, called triangulated irregular networks, or TINs. The TIN model represents a surface as a set of contiguous, non-overlapping triangles. Within each triangle the surface is represented by a plane. The triangles are made from a set of points called mass points. If mass points are carefully selected, the TIN represents an accurate representation of the model of the surface. Well-placed mass points occur where there is a major change in the shape of the surface, for example, at the peak of a mountain, the floor of a valley, or at the edge (top and bottom) of cliffs.

TINs are generally computed from a set of three-dimensional points specifying coordinate values in the longitude (x), latitude (y), and elevation (z) dimensions. Oracle TIN generation software uses the Delaunay triangulation algorithm, but it is not required that TIN data be formed using only Delaunay triangulation techniques.

The general process for working with a TIN is as follows:

  1. Initialize the TIN, using the SDO_TIN_PKG.INIT function.

  2. Create the TIN, using the SDO_TIN_PKG.CREATE_TIN procedure.

  3. As needed for queries, clip the TIN, using the SDO_TIN_PKG.CLIP_TIN function.

  4. If necessary, use the SDO_TIN_PKG.TO_GEOMETRY function (for example, to convert the result of a clip operation into a single SDO_GEOMETRY object).

For a Java example of working with TINs, see the following files:

$ORACLE_HOME/md/demo/TIN/examples/java/README.txt
$ORACLE_HOME/md/demo/TIN/examples/java/readTIN.java

3D Mesh Modeling

Oracle Spatial support for TIN is extended to support 3D meshes. A mesh can model a 3D triangulation of objects, including vertical surfaces, overhangs, and closed volumes which are currently not supported by TIN. Although a sloping surface is better represented by a TIN, 3D meshes can be used for modeling more complex landscapes, waterfalls, cliffs, buildings, cars, and so on.

In contrast to the Delaunay triangulation of a TIN, a 3D mesh is generated from a point set based on the Ball-Pivoting Algorithm. The shape of the mesh blocks are different from the slim vertical blocks generated for a TIN.

A 3D mesh is also represented by a SDO_TIN data type. To distinguish between a TIN and a 3D mesh, you can query the object metadata using the following functions:

The following shows an example which queries the metadata of the SDO_TIN objects in the meshes table:

SQL> SELECT
  2 (cols.table_name || ':' || cols.column_name) "Column Name",
  3 meshes.tin.tin_id "TIN ID",
  4 sdo_tin_pkg.get_blocking_method(tin) "Model",
  5 sdo_tin_pkg.get_num_points(tin) "# Pts",
  6 sdo_tin_pkg.get_tin_block_sort_order(meshes.tin) "Blk Sort Order"
  7 FROM
  8 TABLE(sdo_tin_pkg.list_tin_columns()) cols,
  9 TABLE(sdo_tin_pkg.list_tins(cols.table_name, cols.column_name)) meshes
 10 ORDER BY
 11 cols.table_name,
 12 cols.column_name,
 13 meshes.tin.tin_id;

The example query produces the following output:

Column Name      TIN ID     Model                  # Pts      Blk Sort Order
---------------- ---------- ---------------------- ---------- --------------------------------
MESHES:TIN                2 TIN                             4 TIN by Delaunay triangulation
MESHES:TIN                3 Mesh                         1097 Mesh by consecutive submesh

2 rows selected.

See Also:

Modeling Solids

The simplest types of solids can be represented as cuboids, such as a cube or a brick. A more complex solid is a frustum, which is a pyramid formed by cutting a larger pyramid (with three or more faces) by a plane parallel to the base of that pyramid. Frustums are sometimes used as query windows to spatial operators. Frustums and cubes are typically modeled as solid-type SDO_GEOMETRY objects. Figure: Frustum as Query Window for Spatial Objects shows a frustum as a query window, with two spatial objects at different distances from the view point.

Figure: Frustum as Query Window for Spatial Objects

Description of the illustration frustum_view.png

Point clouds, which are large collections of points, can sometimes be used to model the shape or structure of solid and surface geometries. Most applications that use point cloud data contain queries based on location. Applications can also go outside Spatial to add visibility logic to perform queries based on both location and visibility.

Most applications that use point cloud data seek to minimize data transfer by retrieving objects based on their distance from a view point. For example, in Figure: Frustum as Query Window for Spatial Objects, object B is farther from the view point than object A, and therefore the application might retrieve object A in great detail (high resolution) and object B in less detail (low resolution). In most scenarios, the number of objects or points increases significantly as the distance from the view point increases; and if farther objects are retrieved at lower resolutions than nearer objects, the number of bytes returned by the query and the rendering time for the objects decrease significantly.

For storage of point cloud data, you can use either an SDO_PC object or is a flat table. The approach to use depends on your hardware environment and usage patterns. An advantage of the flat format is its efficient and dynamic nature, because updates to the point data do not require reblocking.

The general process for working with a point cloud is as follows, depending on whether the point cloud data will be stored in an SDO_PC object or in a flat table.

For a Java example of working with point clouds, see the following files:

$ORACLE_HOME/md/demo/PointCloud/examples/java/README.txt
$ORACLE_HOME/md/demo/PointCloud/examples/java/readPointCloud.java

Three-Dimensional Optimized Rectangles

Instead of specifying all the vertices for a three-dimensional rectangle (a polygon in the shape of rectangle in three-dimensional space), you can represent the rectangle by specifying just the two corners corresponding to the minimum ordinate values (min-corner) and the maximum ordinate values (max-corner) for the X, Y, and Z dimensions.

The orientation of a three-dimensional rectangle defined in this way is as follows:

For example, if the rectangle is in the XY plane and the order of the vertices is <min-corner, max-corner>, the normal is along the positive Z-axis; but if the order is <max-corner, min-corner>, the normal is along the negative Z-axis.

Using these orientation rules for rectangles, you can specify the order of the min-corner and max-corner vertices for a rectangle appropriately so that the following requirements are met:

Using Texture Data

Note: This section describes concepts that you will need to understand for using texture data with Spatial. However, the texture metadata is not yet fully implemented in Oracle Spatial, and a viewer is not yet supported. This section will be updated when texture support is released.

A texture is an image that represents one or more parts of a feature. Textures are commonly used with visualizer applications (viewers) that display objects stored as spatial geometries. For example, a viewer might display an office building (three-dimensional solid) using textures, to allow a more realistic visualization than using just colors. Textures can be used with two-dimensional and three-dimensional geometries.

In the simplest case, a rectangular geometry can be draped with a texture bitmap. However, often only a subregion of a texture bitmap is used, as in the following example cases:

Figure: Faces and Textures shows a large rectangular surface that, when viewed, appears to consist of three textures, each of which is repeated multiple times in various places on the surface.

Figure: Faces and Textures

Description of the illustration textures_2.gif

As shown in Figure: Faces and Textures:

Figure: Texture Mapped to a Face shows a texture bitmap mapped to a triangular face.

Figure: Texture Mapped to a Face

Description of the illustration textures_3.gif

As shown in Figure: Texture Mapped to a Face:

To minimize the storage requirements for image data representing surfaces, you should store images for only the distinct textures that will be needed. The data type for storing a texture is SDO_ORDINATE_ARRAY, which is used in the SDO_GEOMETRY type definition (explained in SDO_GEOMETRY Object Type).

For example, assume that the large surface in Figure: Faces and Textures has the following definition:

SDO_GEOMETRY(
  SDO_POLYGON2D,  -- two-dimensional polygon
  NULL,
  NULL,
  SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
  SDO_ORDINATE_ARRAY(1,1. 1,13, 13,13, 1,13, 1,1)
)

Assume that you have a MY_TEXTURE_COORDINATES table with the following definition:

CREATE TABLE my_texture_coordinates (
  texture_coord_id NUMBER PRIMARY KEY,
  texture_name VARCHAR2(32),
  texture_coordinates SDO_ORDINATE_ARRAY);

Example: Inserting Texture Coordinate Definitions inserts three texture coordinate definitions into this table. For each texture, its coordinates reflect one of the appropriate smaller rectangles shown in Figure: Faces and Textures; however, you can choose any one of the appropriate rectangles for each texture. In Example: Inserting Texture Coordinate Definitions, the SDO_ORDINATE_ARRAY definitions for each texture reflect a polygon near the top of Figure: Faces and Textures.

Example: Inserting Texture Coordinate Definitions

INSERT INTO my_texture_coordinates VALUES(
  1,
  'Texture_A',
   SDO_ORDINATE_ARRAY(1,9, 1,5, 5,12, 1,12, 1,9)
  );

INSERT INTO my_texture_coordinates VALUES(
  2,
  'Texture_B',
  SDO_ORDINATE_ARRAY(5,9, 9,9, 9,12, 5,12, 5,9)
  );

INSERT INTO my_texture_coordinates VALUES(
  3,
  'Texture_C',
  SDO_ORDINATE_ARRAY(1,12, 13,12, 13,13, 1,13, 1,12)
  );

Schema Considerations with Texture Data

Texture bitmaps (stored as BLOBs or as URLs in VARCHAR2 format) and texture coordinate arrays (stored using type SDO_ORDINATE_ARRAY) can be stored in the same table as the SDO_GEOMETRY column or in separate tables; however, especially for the texture bitmaps, it is usually better to use separate tables. Texture bitmaps are likely to be able to be shared among features (such as different office buildings), but texture coordinate definitions are less likely to be sharable among features. (For example, many office buildings may share the same general type of glass exterior, but few of the buildings have the same number of windows and floors. In designing your textures and applications, you must consider how many buildings use the same texture subregion or drape the texture in the same size of repetitive matrix.)

An exception is a texture coordinate array that drapes an entire texture bitmap over a rectangular geometric face. In this case, the texture coordinate array can be specified as (0,0, 1,0, 1,1, 0,1, 1,1), defined by vertices “lower left”, “lower right”, “upper right”, “upper left”, and closing with “lower left”. Many data sets use this texture coordinate array extensively, because they have primarily rectangular faces and they store one facade for each texture bitmap.

If you used separate tables, you could link them to the surface geometries using foreign keys, as in Example: Creating Tables for Texture Coordinates, Textures, and Surfaces.

Example: Creating Tables for Texture Coordinates, Textures, and Surfaces

-- One row for each texture coordinates definition.
CREATE TABLE my_texture_coordinates (
  texture_coord_id NUMBER PRIMARY KEY,
  texture_coordinates SDO_ORDINATE_ARRAY);

-- One row for each texture.
CREATE TABLE my_textures(
  texture_id NUMBER PRIMARY KEY,
  texture BLOB);

-- One row for each surface (each individual "piece" of a
-- potentially larger surface).
CREATE TABLE my_surfaces(
  surface_id NUMBER PRIMARY KEY,
  surface_geometry SDO_GEOMETRY,
  texture_id NUMBER,
  texture_coord_id NUMBER,
  CONSTRAINT texture_id_fk
    FOREIGN KEY (texture_id) REFERENCES my_textures(texture_id),
  CONSTRAINT texture_coord_id_fk
    FOREIGN KEY (texture_coord_id) REFERENCES
      my_texture_coordinates(texture_coord_id));

Validation Checks for Three-Dimensional Geometries

The SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT and SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT subprograms can validate two-dimensional and three-dimensional geometries. For a three-dimensional geometry, these subprograms perform any necessary checks on any two-dimensional geometries (see the Usage Notes for SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT) within the overall three-dimensional geometry, but also several checks specific to the three-dimensional nature of the overall object.

For a simple solid (one outer surface and any number of inner surfaces), these subprograms perform the following checks:

For a composite solid (one or more solids connected to each other), these subprograms perform the following checks:

For a multisolid (one or more solids, each of which is a simple or composite solid), these subprograms perform the following check: