Using SDO_PC_PKG

Create a global temporary table for multi users
create global temporary table queryres as select * from MDSYS.sdo_pc_blk_table;

Q1: Give any point(x,y), how to get the Z-axis?
SDO_Geometry Point constructor:
sdo_geometry(2001,26910, sdo_point_type(532895.65,4276546.48,null),NULL,NULL)
Base on the point to create +/- tolerance MBR:
sdo_geometry(2003,26910, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(532895.15,4276545.98,532896.15,4276546.98))

DECLARE
  inp sdo_pc;
BEGIN
  select pc into inp from pc_tab;
  insert into queryres
  select * from
    table(sdo_pc_pkg.clip_pc
     (inp,

      sdo_geometry(2003,26910, NULL,

      SDO_ELEM_INFO_ARRAY(1,1003,3),

      SDO_ORDINATE_ARRAY(532895.15,4276545.98,532896.15,4276546.98)), NULL,NULL,NULL,NULL)

    );

END;
/
select sdo_pc_pkg.to_geometry(r.points, r.num_points,3,8307) from queryres r;

Q2: Clip an area

DECLARE

  inp sdo_pc;

BEGIN

  select pc into inp from pc_tab where rownum=1;

  insert into queryres

  select * from

    table(sdo_pc_pkg.clip_pc

      (inp,

      sdo_geometry(2003,26910, NULL,

      SDO_ELEM_INFO_ARRAY(1,1003,3),

      SDO_ORDINATE_ARRAY(532000,4273000,532100,4273100)),NULL,NULL,NULL,NULL)

    );

END;

/

select sdo_pc_pkg.to_geometry(r.points, r.num_points,3,8307) from queryres r;

Q3: For a line

Use SDO_GEOM.SDO_BUFFER to create a line buffer, then run sdo_pc_pkg.clip_pc, and , sdo_pc_pkg.to_geometry

Update metadata in Oracle Dictionary & Creating Index

After upload Point Clouds into Oracle database. The next step is to update metadata. Let's check the Oracle dictionary.
select * from user_sdo_geom_metadata;

It looks great. Oracle automatic update the metadata after loading Point Clouds.

Check sdo_index:
select * from user_sdo_index_info;

That's really great. Oracle have done the update with the metadata and index.

Following Appendix E Steps

1. Create table PC_TAB and add a SDO_PC column to store point cloud data
create table PC_TAB (pc SDO_PC);

2. Create PC_BLKTAB as the Block Table for the Point Cloud Data
create table PC_BLKTAB as select * from MDSYS.SDO_PC_BLK_TABLE;

3. Create INPTAB table storing Input Set of Points
CREATE SEQUENCE RID_SEQ INCREMENT BY 1 START WITH 1;

CREATE TABLE INPTAB(  RID VARCHAR2(24),  VAL_D1 NUMBER,  VAL_D2 NUMBER,  VAL_D3 NUMBER);
INSERT INTO inptab(rid, val_d1, val_d2, val_d3) SELECT RID_SEQ.NEXTVAL, X, Y, Z FROM points;
COMMIT;

4.Result Table for 3D Point Data

CREATE TABLE RESTAB (  PTN_ID NUMBER,  POINT_ID NUMBER,  RID VARCHAR2(24),  VAL_D1 NUMBER,  VAL_D2 NUMBER,  VAL_D3 NUMBER);

5. Initializing, Inserting, and Populating a Point Cloud with an Input Set of Points


DECLARE
  pc sdo_pc;
BEGIN
-- Initialize the point cloud object.
  pc := sdo_pc_pkg.init(
    'PC_TAB', -- Table that has SDO_PC column defined
    'PC', -- Column name of SDO_PC object
    'PC_BLKTAB', -- Table to store blocks of point cloud
    'blk_capacity=5000', -- Using default setting
    SDO_GEOMETRY(2003, 26910, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(531159.776, 4272683.453, 534629.345, 4280409.657)), 0.0005, 3, NULL
  );
  -- Insert the point cloud object into the "base" table
  INSERT INTO pc_tab (pc) VALUES (pc);
 
  -- Create the blocks for the point cloud
  sdo_pc_pkg.create_pc(
    pc, --Initilized PointCloud object
    'INPTAB', -- Name of input table to ingest into the point cloud
    'RESTAB' -- Name of output table that stores the points
  );
 END;
/

PS. user who nneds
CONNECT, CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE INDEXTYPE, CREATE SEQUENCE

Prepare External Table for Loading Point Clouds

After down from data set from OpenTopography. I am interest in points.txt only when gz file was decompressed. Oracle Spatial User Developer Guide did not document well on how to load point cloud data set into Oracle database. Instead, I found the steps from "Pro Oracle Spatial for Oracle Database 11g" Appendix E.

I am going to create an external table link to points.txt as POINTS for loading points into Oracle SDO_PC data type. Before creating an external table, I need to create a directory, PC_DIR, and put point.tx on that directory.

Login as system to create PC_DIR.
create or replace directory "PC_DIR" as '/u01/opt/PC_dataset';

And grant READ & WRITE rights to user at least
grant read, write on directory PC_DIR to  fsms;

Now I can create an external table.

  CREATE TABLE "FSMS"."POINTS"
   ( "X" NUMBER,
"Y" NUMBER,
"Z" NUMBER,
"CLASSIFICATION" NUMBER,
"GPSTIME" NUMBER,
"SCAN_ANGLE" NUMBER,
"INTENSITY" NUMBER,
"NUMBER_OF_RETURNS" NUMBER,
"RETURN_NUMBER" NUMBER,
"POINT_SOURCE_ID" NUMBER,
"EDGE_OF_FLIGHT_LINE_FLAG" NUMBER,
"DIRECTION_OF_SCAN_FLAG" NUMBER,
"USER_DATA" NUMBER,
"RED_CHANNEL" NUMBER,
"GREEN_CHANNEL" NUMBER,
"BLUE_CHANNEL" NUMBER
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "PC_DIR"
    
      LOCATION
       ( "PC_DIR":'points.txt'
       )
    )
   REJECT LIMIT UNLIMITED;

Following the steps from Appendix E

Get the Point Cloud

In Taiwan, I really hard to get Point Cloud data set free. For test, I found I can get some data set from Open Topography (http://www.opentopography.org/).
Here is the metadata got from Open Topography. (it is around Napa where I had been visited a great restaurant, GrayStone).

The greatest thing for me is it clearly describes coordination:

Horizontal Coordinates: UTM Zone 10 N NAD83 Meters
Vertical Coordinates: Geoid89
...

Data Selection Coordinates:
Xmin: 531159.776
Xmax: 534629.345
Ymin: 4272683.453
Ymax: 4280409.657
Classifications: All

Base on Horizontal and Vertical Coordinates, I can query from Oracle Coordinates references table and decides which SRID I should choose.

So we got to choose SRID = 26910