Add a Point in a Line String

程式碼來源: 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;

/