程式碼來源: Pro Oracle Spatial for Oracle Database 11g
我覺得這段程式, 會在計算路徑時會用到, 先表列在這參考
因為程式的需要, 加入了只輸入單點與輸入2點的情況
單點, 就只回傳單點; 2個單點, 就組成一條線.
所以程式變成:
create or replace FUNCTION add_to_line(
geom SDO_GEOMETRY,
point SDO_GEOMETRY,
point_number NUMBER DEFAULT 0
) RETURN SDO_GEOMETRY
IS
g SDO_GEOMETRY; -- Updated geometry
d NUMBER; -- Number of dimensions in line geometry
t NUMBER; -- Geometry Type
p NUMBER; -- Insertion point into ordinates array
i NUMBER;
BEGIN
-- Check If geom is NULL then means this is the first point return point
IF geom IS NULL THEN
RETURN point;
END IF;
-- If geom is a point then construct and return a line
IF geom.SDO_GTYPE = 2001 THEN
g := SDO_GEOMETRY(2002, 8307,NULL,
SDO_ELEM_INFO_ARRAY(1, 2,1),
SDO_ORDINATE_ARRAY(
geom.SDO_POINT.X, geom.SDO_POINT.Y, point.SDO_POINT.X, point.SDO_POINT.Y
));
RETURN g;
END IF;
-- Get the number of dimensions from the gtype
d := SUBSTR(geom.SDO_GTYPE, 1, 1);
-- Get index in ordinates array
-- If 0, then we want the lst point
IF point_number = 0 THEN
p := geom.SDO_ORDINATES.COUNT() + 1;
ELSE
p := (point_number - 1) * d + 1;
END IF;
-- Verify that the insertion point exists
IF point_number <>0 THEN
IF p > geom.SDO_ORDINATES.LAST()
OR p < geom.SDO_ORDINATES.FIRST() THEN
RAISE_APPLICATION_ERROR(-20000, 'Invalid insertion point');
END IF;
END IF;
-- Initialize output line with input line
g := geom;
-- Step 1: Extend the ordinates array
g.SDO_ORDINATES.EXTEND(d);
-- Step 2: Shift the ordinates "down"
FOR i IN REVERSE p..g.SDO_ORDINATES.COUNT() - d LOOP
g.SDO_ORDINATES(i+d) := g.SDO_ORDINATES(i);
END LOOP;
-- Step 3: Store the new point
g.SDO_ORDINATES(p) := point.SDO_POINT.X;
g.SDO_ORDINATES(p+1) := point.SDO_POINT.Y;
IF d = 3 THEN
g.SDO_ORDINATES(p+2) := point.SDO_POINT.Z;
END IF;
-- Return the new Line String
RETURN g;
END;
/
