29.3 SDO_PC_PKG.CLIP_PC_FLAT_STRING

Format

SDO_PC_PKG.CLIP_PC_FLAT_STRING(
     geometry       IN SDO_GEOMETRY, 
     table_name     IN VARCHAR2, 
     tolerance      IN NUMBER, 
     other_dim_qry  IN SDO_MBR, 
     mask           IN VARCHAR2 DEFAULT NULL,
     dop            IN NUMBER DEFAULT NULL,
     sdo_level      IN NUMBER DEFAULT 4,
     sdo_ntiles     IN NUMBER DEFAULT 100
     ) RETURN CLOB;

Description

Generates a SQL query string to perform a clip operation on a flat-format point cloud, in which the point cloud data is stored in a flat table. The query is not executed, but merely generated for either subsequent execution or inspection.

Parameters

geometry

Two-dimensional geometry to serve as the clip window. For example:

geometry =>
  SDO_GEOMETRY(
    2003,
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1, 1003, 3),
    SDO_ORDINATE_ARRAY(
      0.75, 0.75,
      1.25, 1.25))
table_name

Name of the table or view containing the point cloud data. The first two columns in the table or view must be named X and Y and be of type Oracle NUMBER. Optionally, following X and Y, the table or view can contain a Z column of type NUMBER, and additional nonspatial point cloud attributes. The additional nonspatial point cloud attributes can be any data type, but must be type Oracle NUMBER if they will participate as constraints in the other_dim_qry parameter.

tolerance

Tolerance value for objects in the point cloud (see Tolerance for information about spatial tolerance). If this parameter is null, the default value is 0.0000000000005

other_dim_qry

Minimum bounding rectangle (MBR) to provide a user defined query ranges for any Oracle NUMBER type columns (not including the X and Y columns).

The SDO_MBR type is defined as (LOWER_LEFT SDO_VPOINT_TYPE, UPPER_RIGHT SDO_VPOINT_TYPE) and SDO_V_POINT_TYPE is defined as VARRAY(64) OF NUMBER.

Specify NULL in the SDO_V_POINT_TYPE array for Oracle NUMBER type columns that are not constrained by a query range.

As an example:

other_dim_qry => MDSYS.SDO_MBR(
                   SDO_VPOINT_TYPE(10, 81),
                   SDO_VPOINT_TYPE(11, 82))

means that the third value (x and y being first and second) is between 10 and 11. The fourth value should be between 81 and 82.

mask

The topological relationship of interest, as applicable to points interacting with polygons. Valid values are 'mask=<value>' where <value> is one or more of the mask values valid for the SDO_RELATE operator (TOUCH, OVERLAPBDYDISJOINT, OVERLAPBDYINTERSECT, EQUAL, INSIDE, COVEREDBY, CONTAINS, COVERS, ANYINTERACT, ON), or FILTER, which performs an approximate check. FILTER will return all the candidates that intersect the polygon, and possibly a few more. Multiple masks are combined with the logical Boolean operator OR (for example, 'mask=inside+touch'); however, FILTER cannot be combined with any other mask.

If this parameter is null or contains an empty string, mask=ANYINTERACT is assumed.

dop

Degree of parallelism for the clip operation. This may appear in the generated SQL statement, for example, as: select /*+ PARALLEL ( 16 )*/ * from “LIDAR_DATA" …

sdo_level

Specifies the level of tessellation. A value between 1 and 5 is common, and 4 is the default. This affects the generated tiles, for example, (x >= 2.2500000125 and x < 2.500000025 and y >= 2.12500000625 and y < 2.7500000375).

sdo_ntiles

Specifies the maximum number of tiles to generate. A value between 1 and 200 is common, and 100 is the default. This affects the generated tiles.

Usage Notes

This function is similar to the SDO_PC_PKG.CLIP_PC_FLAT function, except that it:

  • Does not execute the query, but merely generates a SQL query string.
  • Provides more tuning capability with the sdo_level and sdo_ntiles parameters. For most queries, the values sdo_level=1 and sdo_ntiles=10 are optimal.

See also the Usage Notes for SDO_PC_PKG.CLIP_PC_FLAT.

Examples

The following example creates a table named LIDAR_DATA, with columns X, Y, Z, and VAL (for some values). It then generates, and subsequently even executes (in a separate statement), a clip operation SQL statement on the point cloud.

SQL> create table lidar_data(x number, y number, z number, val number);

Table created.

SQL> 
SQL> begin
  2    for y in 1..1024 loop
  3      for x in 1..1024 loop
  4        insert into lidar_data (x, y, z, val) values (x, y, x + y, x * y);
  5      end loop;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> SELECT
  2    sdo_pc_pkg.clip_pc_flat_string (
  3      geometry      => SDO_GEOMETRY(
  4                         2003,
  5                         8307,
  6                         NULL,
  7                         SDO_ELEM_INFO_ARRAY(1, 1003, 1),
  8                         SDO_ORDINATE_ARRAY(
  9                           1, 1,
 10                           2, 1,
 11                           2, 2,
 12                           3, 2,
 13                           3, 3,
 14                           1, 3,
 15                           1, 1)),
 16      table_name    => 'LIDAR_DATA',
 17      tolerance     => .00000005,
 18      other_dim_qry => MDSYS.SDO_MBR(
 19                         SDO_VPOINT_TYPE(0, 0),
 20                         SDO_VPOINT_TYPE(1000, 1000000)),
 21      mask          => 'FILTER',
 22      dop           => 16,
 23      sdo_level     => 4,
 24      sdo_ntiles    => 100) "Query"
 25  FROM
 26    dual;

Query
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select  /*+ PARALLEL ( 16 )*/ * from "LIDAR_DATA" WHERE  (
  (x >= 1.2499999625 and x <  1.37499996875 and y >= 1.2499999625 and y <  2.7500000375) OR
  (x >= 2.7500000375 and x <  2.87500004375 and y >= 2.12500000625 and y <  2.87500004375) OR
  (x >= 1.37499996875 and x <  1.499999975 and y >= 1.2499999625 and y <  2.7500000375) OR
  (x >= 2.2500000125 and x <  2.500000025 and y >= 2.12500000625 and y <  2.7500000375) OR
  (x >= 2.500000025 and x <  2.7500000375 and y >= 2.12500000625 and y <  2.7500000375) OR
  (x >= 1.12499995625 and x <  1.87499999375 and y >= 1.12499995625 and y <  1.2499999625) OR
  (x >= 1.7499999875 and x <  2 and y >= 2.12500000625 and y <  2.7500000375) OR
  (x >= 1.12499995625 and x <  1.2499999625 and y >= 1.2499999625 and y <  2.87500004375) OR
  (x >= 2 and x <  2.2500000125 and y >= 2.12500000625 and y <  2.7500000375) OR
  (x >= 1.7499999875 and x <  1.87499999375 and y >= 1.2499999625 and y <  2.12500000625) OR
  (x >= 1.2499999625 and x <  2.7500000375 and y >= 2.7500000375 and y <  2.87500004375) OR
  (x >= 1.499999975 and x <  1.62499998125 and y >= 1.2499999625 and y <  2.7500000375) OR
  (x >= 1.62499998125 and x <  1.7499999875 and y >= 1.2499999625 and y <  2.7500000375))  AND ( "Z" BETWEEN 0 AND 1000 )  AND ( "VAL" BETWEEN 0 AND 1000000 )   UNION ALL select  * from "LIDAR_DATA" W
HERE  (
  (x >= .99999995 and x <  1.87499999375 and y >= .99999995 and y <  1.12499995625) OR
  (x >= .99999995 and x <  1.12499995625 and y >= 1.12499995625 and y <  2.87500004375) OR
  (x >= .99999995 and x <  2.87500004375 and y >= 2.87500004375 and y <  3.0000000500000001402157) OR
  (x >= 2 and x <  2.12500000625 and y >= .99999995 and y <  1.87499999375) OR
  (x >= 1.87499999375 and x <  2 and y >= .99999995 and y <  2.12500000625) OR
  (x >= 2.87500004375 and x <  3.0000000500000001402157 and y >= 1.87499999375 and y <  3.0000000500000001402157) OR
  (x >= 2 and x <  2.87500004375 and y >= 2 and y <  2.12500000625) OR
  (x >= 2 and x <  2.87500004375 and y >= 1.87499999375 and y <  2))  AND ("Z" BETWEEN 0 AND 1000 )  AND ("VAL" BETWEEN 0 AND 1000000 )


1 row selected.

SQL> 
SQL> declare
  2    stm varchar2(4096);
  3  begin
  4    SELECT
  5      sdo_pc_pkg.clip_pc_flat_string (
  6        geometry      => SDO_GEOMETRY(
  7                           2003,
  8                           8307,
  9                           NULL,
 10                           SDO_ELEM_INFO_ARRAY(1, 1003, 1),
 11                           SDO_ORDINATE_ARRAY(
 12                             1, 1,
 13                             2, 1,
 14                             2, 2,
 15                             3, 2,
 16                             3, 3,
 17                             1, 3,
 18                             1, 1)),
 19        table_name    => 'LIDAR_DATA',
 20        tolerance     => .00000005,
 21        other_dim_qry => MDSYS.SDO_MBR(
 22                           SDO_VPOINT_TYPE(0, 0),
 23                           SDO_VPOINT_TYPE(1000, 1000000)),
 24        mask          => 'FILTER',
 25        dop           => 16,
 26        sdo_level     => 4,
 27        sdo_ntiles    => 100) "Query"
 28    into
 29      stm
 30    FROM
 31      dual;
 32  
 33    execute immediate 'create table result as (' || stm || ')';
 34  end;
 35  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from result order by x, y;

         X          Y          Z        VAL
---------- ---------- ---------- ----------
         1          1          2          1
         1          2          3          2
         1          3          4          3
         2          1          3          2
         2          2          4          4
         2          3          5          6
         3          2          5          6
         3          3          6          9

8 rows selected.

SQL> 
SQL> drop table result;

Table dropped.

SQL> drop table lidar_data;

Table dropped.

SQL> SQL>

Parent topic: SDO_PC_PKG Package (Point Clouds)