This chapter contains descriptions of the triangulated irregular network (TIN) subprograms shown in Table 29-1.
| Subprogram | Description |
|---|---|
|
Performs a clip operation on a TIN. |
|
|
Creates a TIN using the points specified in the |
|
|
Drops the dependencies between a TIN block table and a specified base table and column. |
|
|
Initializes a TIN. |
|
|
Projects two-dimensional points onto a TIN, thereby determining point heights. |
|
|
Creates a DEM (Digital Elevation Model) GeoRaster object from a TIN. |
|
|
Returns a geometry object representing all or part of a TIN. |
To use the subprograms in this package, you must understand the main concepts related to three-dimensional geometries, including the use of triangulated irregular networks (TINs) to model surfaces. Section 1.11 describes support for three-dimensional geometries, Section 1.11.1 describes the use of TINs to model surfaces, and Section 2.5 describes data types related to TINs.
SDO_TIN_PKG.CLIP_TIN(
inp IN SDO_TIN,
qry IN SDO_GEOMETRY,
qry_min_res IN NUMBER,
qry_max_res IN NUMBER,
blkid IN NUMBER DEFAULT NULL
) RETURN SDO_TIN_BLK_TYPE;
TIN on which to perform the clip operation.
Window from which to select objects to be returned; typically a polygon for two-dimensional geometries or a frustum for three-dimensional geometries.
Minimum resolution value. Objects in qry with resolutions equal to or greater than qry_min_res and less than or equal to qry_max_res are returned by the clip operation.
Maximum resolution value. Objects in qry with resolutions equal to or greater than qry_min_res and less than or equal to qry_max_res are returned by the clip operation.
Block ID number of the block to which to restrict the objects returned by the clip operation. If this parameter is null, all objects that satisfy the other parameters are returned.
This function returns triangles from a TIN that are within a specified query window and that satisfy any other requirements specified by the parameters. A common use of this function is to perform queries on TINs. You can maximize the performance of a TIN query by minimizing the number of objects that the function needs to consider for the operation.
The SDO_TIN and SDO_TIN_BLK_TYPE data types are described in Section 2.5.
Section 1.11.1 describes how to use TINs to model surfaces.
The following example performs a clip operation on a TIN. It is taken from the $ORACLE_HOME/md/demo/TIN/examples/plsql/tin.sql example program, which is available if you installed the files from the Oracle Database Examples media (see Oracle Database Examples Installation Guide).
. . .
declare
inp sdo_tin;
begin
select tin INTO inp from base where rownum=1;
insert into restst
select * from
table(sdo_tin_pkg.clip_tin
(
inp, -- Input TIN object
sdo_geometry(2003, null, null,
mdsys.sdo_elem_info_array(1, 1003, 3),
mdsys.sdo_ordinate_array(-74.1, -73.9, 39.99999,40.00001)), -- Query
null, null));
end;
. . .
SDO_TIN_PKG.CREATE_TIN(
inp IN SDO_TIN,
inptable IN VARCHAR2,
clstPcdataTbl IN VARCHAR2 DEFAULT NULL);
SDO_TIN object to be used. This object must have been created by the SDO_TIN_PKG.INIT function
Name of the table or view containing the input TIN data. This table or view should have the following columns:
RID (VARCHAR2(24)): Unique ID for each point
VAL_D1 (NUMBER): Ordinate in dimension 1
VAL_D2 (NUMBER): Ordinate in dimension 2
. . .
VAL_Dn (NUMBER): Ordinate in dimension n, where n is the highest-numbered dimension. n should match the tin_tot_dimensions parameter value in the call to the SDO_TIN_PKG.INIT function when the TIN was initialized.
Name of the table for storing the resulting point data. If you do not specify a value, this table is not created. For more information about the table, see the Usage Notes.
The first few dimensions of the TIN are indexed and can later be searched using the SDO_TIN_PKG.CLIP_TIN function. The exact number of dimensions to index is determined by the dimensionality of the TIN extent in the initialized TIN object, specifically: inp.tin_extent.sdo_gtype/1000
If you specify a view name in the inptable parameter, the query SELECT ROWID FROM <view-name> must not return any errors.
If you specify a table name in the clstPcdataTbl parameter, the table must exist and have the following columns:
PTN_ID (NUMBER)
POINT_ID (NUMBER)
RID (VARCHAR2(24): Unique ID for each point
VAL_D1 (NUMBER): Ordinate in dimension 1
VAL_D2 (NUMBER): Ordinate in dimension 2
. . .
VAL_Dn (NUMBER): Ordinate in dimension n, where n is the highest-numbered dimension. n should match the tin_tot_dimensions parameter value in the call to the SDO_TIN_PKG.INIT function when the TIN was initialized.
If a value is specified for the clstPcdataTbl parameter, this function populates the table by assigning appropriate values for PTN_ID and POINT_ID and by copying the values from the inptable table or view for other attributes. This table can be created as an index organized table. It can be used in applications for searching using SQL queries on dimensions other than those reflected in the index dimensionality. (For an explanation of index dimensionality and total dimensionality, see the explanation of the tin_tot_dimensions parameter of the SDO_TIN_PKG.INIT function.)
The SDO_TIN data type is described in Section 2.5.
Section 1.11.1 describes how to use TINs to model surfaces.
The following example creates a TIN. It is taken from the $ORACLE_HOME/md/demo/TIN/examples/plsql/tin.sql example program, which is available if you installed the files from the Oracle Database Examples media (see Oracle Database Examples Installation Guide).
. . . -- Create the blocks for the TIN. sdo_tin_pkg.create_tin( tin, -- Initialized TIN object 'INPTAB', -- Name of input table to ingest into the pointcloud 'RES' -- Name of output table that stores the points (with ptn_id,pt_id) ); / . . .
Name of a base table that was specified (in the basetable parameter of the SDO_TIN_PKG.INIT function) when the TIN was initialized.
Name of a column in base table that was specified in the basecol parameter of the SDO_TIN_PKG.INIT function.
This procedure truncates the TIN block table and removes the association between the block table and the base table and column combination.
After you execute this procedure, you can drop the TIN block table or associate the table with another base table and column combination. For more information, see the Usage Notes for the SDO_TIN_PKG.INIT function.
SDO_TIN_PKG.INIT(
basetable IN VARCHAR2,
basecol IN VARCHAR2,
blktable IN VARCHAR2,
ptn_params IN VARCHAR2,
tin_extent IN SDO_GEOMETRY,
tin_tol IN NUMBER DEFAULT 0.000000000000005,
tin_tot_dimensions IN NUMBER DEFAULT 2,
tin_domain IN SDO_ORGSCL_TYPE DEFAULT NULL,
tin_break_lines IN SDO_GEOMETRY DEFAULT NULL,
tin_stop_lines IN SDO_GEOMETRY DEFAULT NULL,
tin_void_rgns IN SDO_GEOMETRY DEFAULT NULL,
tin_val_attr_tables IN SDO_STRING_ARRAY DEFAULT NULL,
tin_other_attrs IN XMLTYPE DEFAULT NULL,
) RETURN SDO_TIN;
Name of the base table containing a column of type SDO_TIN.
Name of the column of type SDO_TIN in the base table.
Name of the TIN block table, which is used for storing the blocks of the TIN. This table must exist, and must have been created by a statement in the following form: CREATE TABLE <table-name> AS select * from mdsys.sdo_tin_blk_table;
Each TIN block table can only be associated with only one basetable and basecol combination.
Parameters for partitioning the TIN, specified as a quoted string with keywords delimited by commas. For example: 'blk_capacity=1000,work_tablespace=my_work_ts'. If this parameter is null, the TIN is not partitioned. The following keywords are permitted:
blk_capacity=n, where n is the maximum number of rows in each partition. The default value is 5000. If specified, must be a number greater than or equal to 50.
work_tablespace=x, where x is the name of the tablespace in which to create temporary tables during the partitioning operations.
SDO_GEOMETRY object representing the spatial extent of the TIN (the minimum bounding object enclosing all objects in the TIN. This parameter must not be null.
For geodetic data, this geometry must have two dimensions; otherwise, it can have up to four dimensions. The dimensionality of this geometry is used as the minimum value permitted for the tin_tot_dimensions parameter, as explained in the description of that parameter.
Tolerance value for objects in the TIN. (For information about spatial tolerance, see Section 1.5.5.) If this parameter is null, the default value is 0.0000000000005.
A number specifying the total dimensionality of the TIN object. For each point in the TIN blocks, tin_tot_dimensions ordinates (values) are stored.
The total dimensionality must be greater than or equal to the index dimensionality, which is the number of dimensions in the tin_extent geometry. Specifying total dimensionality greater than index dimensionality enables necessary nonspatial attributes to be retrieved in the same fetch operation with spatial data. The maximum total dimensionality value is 8. The default value for this parameter is 2.
(Not currently used.)
(Not currently used.)
(Not currently used.)
(Not currently used.)
SDO_STRING_ARRAY object specifying the names of any value attribute tables for the TIN. If this parameter is null, the TIN has no associated value attribute tables. Type SDO_STRING_ARRAY is defined as VARRAY(1048576) OF VARCHAR2(32).
XMLTYPE object specifying any other attributes of the TIN. If this parameter is null, the TIN has no other attributes.
This parameter can include metadata on TIN pyramiding, as explained in the Usage Notes.
After you use this function to initialize an SDO_TIN object, you can create a TIN by specifying this object as input to the SDO_TIN_PKG.CREATE_TIN procedure.
The SDO_TIN data type is described in Section 2.5.
Section 1.11.1 describes how to use TINs to model surfaces.
The tin_other_attrs parameter can be used to specify metadata for TIN pyramiding, for example:
xmltype(
'<opc:sdoTinObjectMetadata
xmlns:opc="http://xmlns.oracle.com/spatial/vis3d/2011/sdovis3d.xsd"
xmlns:las="http://liblas.org/schemas/LAS/1.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<opc:sdoTinPyramid/>
</opc:sdoTinObjectMetadata>')
TIN pyramiding creates multiple pyramid levels from level 1 (most detailed) to level n (least detailed). In contrast to point cloud pyramiding (described in the Usage Notes for SDO_PC_PKG.INIT), there is no option relating to preserving level 1 with TIN pyramiding. A TIN with pyramiding will require more storage space than one without pyramiding, because level 1 is basically equal to the TIN without pyramiding, and all other levels require additional space.
The following example initializes a TIN by creating an SDO_TIN object. It is taken from the $ORACLE_HOME/md/demo/TIN/examples/plsql/tin.sql example program, which is available if you installed the files from the Oracle Database Examples media (see Oracle Database Examples Installation Guide).
declare
tin sdo_tin;
begin
-- Initialize the TIN object.
tin := sdo_tin_pkg.init(
'BASE', -- Table that has the SDO_TIN column defined
'TIN', -- Column name of the SDO_TIN object
'BLKTAB', -- Table to store blocks of the TIN
'blk_capacity=1000', -- max # of points per block
mdsys.sdo_geometry(2003, null, null,
mdsys.sdo_elem_info_array(1,1003,3),
mdsys.sdo_ordinate_array(-180, -90, 180, 90)), -- Extent
0.0000000005, -- Tolerance for TIN
3, -- Total number of dimensions
null);
. . .
SDO_TIN_PKG.PROJECT_ORDINATES_ONTO_TIN(
ordinates2D SDO_ORDINATE_ARRAY,
tin SDO_TIN
) RETURN SDO_ORDINATE_ARRAY;
SDO_ORDINATE_ARRAY object with the two-dimensional points to be projected. These cannot be oriented points (which are described in Section 2.7.6).
SDO_TIN object onto which to project the points.
This function can be thought of as performing height interpolation for the specified two-dimensional input points.
The SDO_TIN data type is described in Section 2.5.
Section 1.11.1 describes how to use TINs to model surfaces.
The following example projects two-dimensional points (2.3, 3.4), (4.5, 5.6), (6.7, 7.8) onto a TIN, yielding three-dimensional points (2.3, 3.4, 5.7), (4.5, 5.6, 10.1), (6.7, 7.8, 14.5). In this example, the TIN shape is an inclined surface with zi=xi+yi.
SELECT
sdo_tin_pkg.project_ordinates_onto_tin(
ordinates2d => sdo_ordinate_array(
2.3, 3.4,
4.5, 5.6,
6.7, 7.8),
tin => (select tin from tins where tin_id = 1))
FROM DUAL;
SDO_TIN_PKG.PROJECT_ORDINATES_ONTO_TIN(ORDINATES2D=>SDO_ORDINATE_ARRAY(2.3,3.4,4
--------------------------------------------------------------------------------
SDO_ORDINATE_ARRAY(2.3, 3.4, 5.7, 4.5, 5.6, 10.1, 6.7, 7.8, 14.5)
1 row selected.
SDO_TIN_PKG.TO_DEM(
tin IN SDO_TIN,
dem IN OUT SDO_GEORASTER,
blocksize IN NUMBER,
crs_units_per_pixel IN NUMBER);
TIN object. (The SDO_TIN data type is described in Section 2.5.)
DEM GeoRaster object.(The SDO_GEORASTER data type is described in Oracle Spatial and Graph GeoRaster Developer's Guide.)
Pixel block size for the DEM.
TIN and DEM coordinate reference system (SRID) units for each pixel. For example, if the SRID unit of measure is decimal degree and if each pixel represents 1/100 of a degree, the crs_units_per_pixel value is 0.01.
This procedure modifies the specified GeoRaster object (dem parameter) based on information in the input TIN.
The TIN and the DEM must have the same coordinate reference system (SRID).
For the dem parameter, the input SDO_GEORASTER object can be obtained by inserting a GeoRaster object into a table and returning the GeoRaster object into a variable; for example:
INSERT INTO raster_table VALUES (1, sdo_geor.init('raster_data_table'))
RETURNING raster_image INTO geor;
To determine the horizontal extent in pixels of the DEM, divide the horizontal extent in SRID units by the crs_units_per_pixel parameter value. For example, assume the following:
The TIN and DEM SRID is 4326.
The SRID unit is decimal degrees.
The input TIN has a horizontal extent of 7 decimal degrees.
The crs_units_per_pixel value is 0.01.
In this example, the DEM horizontal extent is 700 pixels (7/.01 = 700).
The SDO_TIN data type is described in Section 2.5.
Section 1.11.1 describes how to use TINs to model surfaces.
The following example creates a DEM from a TIN. It is taken from the $ORACLE_HOME/md/demo/TIN/examples/plsql/tin.sql example program, which is available if you installed the files from the Oracle Database Examples media (see Oracle Database Examples Installation Guide).
create table raster_table (id number, raster_image sdo_georaster);
create table raster_data_table of sdo_raster
(primary key (rasterId, pyramidLevel, bandBlockNumber, rowBlockNumber, columnBlockNumber))
lob(rasterblock) store as (nocache nologging);
DECLARE
inp sdo_tin;
geor sdo_georaster;
BEGIN
select tin INTO inp from tins_hawaii_4326 where rownum=1;
insert into raster_table values (1, sdo_geor.init('raster_data_table')) returning raster_image into geor;
sdo_tin_pkg.to_dem(
tin => inp,
dem => geor,
blocksize => 128,
crs_units_per_pixel => 0.01);
update raster_table set raster_image = geor where id = 1;
END;
/
SDO_TIN_PKG.TO_GEOMETRY(
pts IN BLOB,
trs IN BLOB,
num_pts IN NUMBER,
num_trs IN NUMBER,
tin_ind_dim IN NUMBER,
tin_tot_dim IN NUMBER,
srid IN NUMBER DEFAULT NULL,
blk_domain IN SDO_ORGSCL_TYPE DEFAULT NULL,
get_ids IN NUMBER DEFAULT NULL
) RETURN SDO_GEOMETRY;
BLOB containing points.
BLOB containing triangles.
Maximum number of points to be included in the resulting geometry.
Maximum number of triangles to be included in the resulting geometry.
Number of spatial dimensions that are indexed.
Number of spatial dimensions defined for the data.
Spatial reference (coordinate system) ID associated with the data. If this parameter is null, no SRID value is associated with the data.
(Not currently used.)
Null or 0 (the default) does not include the block ID and point ID for each point in the returned geometry; 1 includes the block ID and point ID for each point in the returned geometry. If get_ids is 1, each point in the returned geometry has its spatial dimensions and two additional dimensions. For example: (x, y, z, blk_id, pt_id).
This function returns a single collection SDO_GEOMETRY object that represents all point geometries in the pts parameter and all triangle geometries in the trs parameter. For example, the points and triangles could reflect the result of a clip operation or the contents of an entire block.
Section 1.11.1 describes how to use TINs to model surfaces.
The following example returns a multipoint collection geometry object representing a TIN. It is taken from the $ORACLE_HOME/md/demo/TIN/examples/plsql/tin.sql example program, which is available if you installed the files from the Oracle Database Examples media (see Oracle Database Examples Installation Guide).
. . .
-- Return points in blk_id of the TIN as a multipoint collection.
select sdo_tin_pkg.to_geometry(
a.points, -- point LOB
a.triangles, -- point LOB
a.num_points, -- # of points in the LOB
a.num_triangles, -- # of points in the LOB
2, -- index dimensionality (gtype dim in extent in INIT)
3, -- total dimensionality
null -- SRID
) from blktab a where blk_id=0;
. . .