Oracle 11g点云数据类型的构建和检索脚本

传送门 ☞ 轮子的专栏 ☞ 转载请注明 ☞ http://blog.youkuaiyun.com/leverage_1229

1Point3D

1.1增加一个SDO_PC类型的列用于存储点云数据

CREATE TABLE pc_tab (pc SDO_PC);

1.2创建pc_blktab表作为点云数据的分块表

CREATE TABLE pc_blktab AS SELECT * FROM MDSYS.SDO_PC_BLK_TABLE;

1.3查看用于存储输入的点集的表的结构

DESC INPTAB;

1.4创建三维点数据的结果表

CREATE TABLE restab (ptn_id NUMBER, point_id NUMBER,
rid VARCHAR2(24), val_d1 NUMBER, val_d2 NUMBER, val_d3 NUMBER);

1.5使用一个输入的点集初始化、插入、填充一个点云

-- Initialize a PointCloud object and populate it using the points in INPTAB.
DECLARE
pc sdo_pc;
BEGIN
-- Initialize the point cloud object.
pc := SDO_PC_PKG.INIT(
'PC_TAB', -- Table that has the SDO_POINT_CLOUD column defined
'PC', -- Column name of the SDO_POINT_CLOUD object
'PC_BLKTAB', -- Table to store blocks of the point cloud
'blk_capacity=50', -- max # of points per block
SDO_GEOMETRY(2003, 8307, NULL,
-- Extent: 2 in 2003 in preceding line indicates that
-- ptn_dimensionality is 2. This means only the first 2 dimensions are
-- used in partitioning the input point set. The index on the block table
-- will also have a dimensionality of 2 in this case.
--
SDO_ELEM_INFO_ARRAY(1,1003,3),
SDO_ORDINATE_ARRAY(-180, -90, 180, 90)
),
0.5, -- Tolerance for point cloud
3, -- Total number of dimensions is 3; the third dimension is stored
-- but not used for partitioning
NULL -- This parameter is for enabling compression but always set to
-- NULL in Oracle 11gR1;
);
-- Insert the point cloud object into the "base" table.
INSERT INTO pctab (pc) VALUES (pc);
-- Create the blocks for the point cloud.
SDO_PC_PKG.CREATE_PC(
pc, -- Initialized PointCloud object
'INPTAB' -- Name of input table to ingest into the point cloud
'RESTAB' -- Name of output table that stores the points
-- (with addl. Columns ptn_id,pt_id) );
END;
/

1.6查看存储输入的点集的表的记录数（点的个数）

SELECT count(*) FROM INPTAB;

1.7验证点云中点的个数（联合分块表）

SELECT sum(num_points) FROM pc_blktab;

1.8查询一个点云对象

CREATE TABLE qryres AS SELECT * FROM MDSYS.SDO_PC_BLK_TABLE;
-- Query
DECLARE
inp sdo_pc;
BEGIN
SELECT pc INTO inp FROM pc_tab WHERE rownum=1;
INSERT INTO qryres
SELECT * FROM
TABLE(SDO_PC_PKG.CLIP_PC
(
inp, -- Input point cloud object
SDO_GEOMETRY(2003, 8307, NULL,
SDO_ELEM_INFO_ARRAY(1, 1003, 3),
SDO_ORDINATE_ARRAY(-74.1, -73.9, 39.99999,40.00001)
), -- QUERY
NULL, NULL, NULL, NULL));
END;
/

1.9获取各分块的点作为多点集合SDO_GEOMETRY

SELECT blk_id, SDO_PC_PKG.TO_GEOMETRY(
r.points, -- LOB containing the points
r.num_points, -- # of points in the LOB
3, -- Total dimensionality of the points in the LOB
8307 -- SRID
) FROM qryres r;

1.10选择各分块的点的ID作为一个以ptn_id, point_id为键值对的数组

SELECT SDO_PC_PKG.GET_PT_IDS(
r.points, -- LOB containing the points
r.num_points, -- # of points in the LOB
3 -- Total dimensionality of the points in the LOB
) FROM resqry r WHERE num_points >0;

2Point7D

2.1SDO_PC预处理

DROP TABLE SDO_PC_STRAIGHT7D;
CREATE TABLE SDO_PC_STRAIGHT7D (RID VARCHAR2(24),VAL_D1 NUMBER,VAL_D2 NUMBER,VAL_D3
NUMBER,VAL_D4 NUMBER,VAL_D5 NUMBER,VAL_D6 NUMBER,VAL_D7 NUMBER,VAL_D8 NUMBER);
DROP SEQUENCE RID_SEQ;
CREATE SEQUENCE RID_SEQ INCREMENT BY 1 START WITH 1 ORDER;
INSERT INTO SDO_PC_STRAIGHT7D (RID,VAL_D1,VAL_D2,VAL_D3,VAL_D4,VAL_D5,VAL_D6,VAL_D7)
SELECT RID_SEQ.nextval,X,Y,Z,I,R,G,B FROM STRAIGHT7D;
DROP INDEX SDO_PC_idx;
CREATE INDEX SDO_PC_idx ON SDO_PC_STRAIGHT7D (VAL_D1,VAL_D2);

2.2SDO_PC创建

DROP TABLE PC_BASE;
CREATE TABLE PC_BASE (PNTCLOUD SDO_PC);
DROP TABLE PC_BLOCK;
CREATE TABLE PC_BLOCK AS SELECT * FROM MDSYS.SDO_PC_BLK_TABLE;

2.3SDO_PC填充

DECLARE
DIM NUMBER := 7;
TOL NUMBER := 0.001;
PTN_PARAMS VARCHAR2(80) := 'BLK_CAPACITY=<max_points_per_record>';
EXTENT SDO_GEOMETRY;
PC SDO_PC;
BEGIN
EXTENT :=
SDO_GEOMETRY(2003,28992,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(1,2,3,4)
);
SELECT MIN(VAL_D1),MIN(VAL_D2),MAX(VAL_D1),MAX(VAL_D2) INTO
EXTENT.SDO_ORDINATES(1),EXTENT.SDO_ORDINATES(2),EXTENT.SDO_ORDINATES(3),EXTENT.SDO_ORD
INATES(4) FROM SDO_PC_STRAIGHT7D;
PC :=
SDO_PC_PKG.INIT('PC_BASE','PNTCLOUD','PC_BLOCK',PTN_PARAMS,EXTENT,TOL,DIM,NULL,NULL,NU
LL);
INSERT INTO PC_BASE VALUES (PC);
COMMIT;
SDO_PC_PKG.CREATE_PC(PC,'SDO_PC_STRAIGHT7D',NULL);
END;
/

2.4SDO_PC查询

DROP TABLE RESULT_QUERY_SDO_PC;
CREATE TABLE RESULT_QUERY_SDO_PC AS SELECT * FROM TABLE(SDO_PC_PKG.CLIP_PC((SELECT
PNTCLOUD FROM PC_BASE WHERE
ROWNUM=1),SDO_GEOMETRY(2003,28992,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,4),MDSYS.SDO_O
RDINATE_ARRAY(<x>-<distance>,<y>, <x>,<y>-<distance>, <x>+<distance>,<y>)), NULL, NULL,
NULL));
SELECT COUNT(*) FROM RESULT_QUERY_SDO_PC;
DROP TABLE RESULT_QUERY;
CREATE TABLE RESULT_QUERY AS SELECT
SDO_PC_PKG.TO_GEOMETRY(A.POINTS,A.NUM_POINTS,7,28992,NULL) GEOM FROM
RESULT_QUERY_SDO_PC A WHERE A.NUM_POINTS>0;
SELECT COUNT(*) FROM RESULT_QUERY;
SELECT * FROM RESULT_QUERY WHERE ROWNUM < 2;
SELECT COUNT(*) FROM PC_BLOCK;
SELECT SUM(NUM_POINT) FROM PC_BLOCK;
SELECT * FROM PC_BASE;
ANALYZE TABLE PC_BLOCK COMPUTE STATISTICS;
ANALYZE TABLE PC_BASE COMPUTE STATISTICS;