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
), orFILTER
, 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 operatorOR
(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
andsdo_ntiles
parameters. For most queries, the valuessdo_level=1
andsdo_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)