GET_POINT 函式

通常在建立網路模型的時候常會用到的函數(其實在一般的向量圖資中也常會用到). 我想Oracle可能覺這個功能太簡單了, 所以並沒有放入一般的函式.
原程式碼出處: Pro Oracle Spatial, ISBN 1-59059-383-9
又從Albert Godfrind和Daniel Geringer的補充與修正. 程式碼如下

create or replace function get_point (
geom mdsys.sdo_geometry, point_number number default 1
) return mdsys.sdo_geometry DETERMINISTIC
is
d number; -- Number of dimensions in geometry
i number; -- Index into ordinates array
px number; -- X of extracted point
py number; -- Y of extracted point
pz number; -- Z of extracted point
begin
-- Return if input geometry is null
if geom is null then
return null;
end if;

-- Get the number of dimensions from the gtype
if length (geom.sdo_gtype) = 4 then
d := substr (geom.sdo_gtype, 1, 1);
else
raise_application_error (-20000, 'Unable to determine dimensionality from gtype');
end if;

-- Check that this is a simple line string geometry
if substr(geom.sdo_gtype, -1, 1) <> '2' then
raise_application_error (-20000, 'Not a simple line string');
end if;

-- Get index into ordinates array. If negative, count backwards from the end of the array
if point_number > 0 then
i := (point_number-1) * d + 1;
else
i := point_number*d + geom.sdo_ordinates.count() + 1;
end if;

-- Verify that the point exists
if i > geom.sdo_ordinates.last()
or i < geom.sdo_ordinates.first() then
raise_application_error (-20000, 'Invalid point number');
end if;

-- Extract the X and Y coordinates of the desired point
px := geom.sdo_ordinates(i);
py := geom.sdo_ordinates(i+1);
if d >= 3 then
pz := geom.sdo_ordinates(i+2);
else
pz := null;
end if;

-- Construct and return the point
return
mdsys.sdo_geometry (
d*1000+1,
geom.sdo_srid,
mdsys.sdo_point_type (px, py, pz),
null, null);
end;
/
------------------------------------------------------------------------------
create or replace function get_first_point (
geom mdsys.sdo_geometry
) return mdsys.sdo_geometry
is
begin
return get_point(geom, 1);
end;
/
------------------------------------------------------------------------------
create or replace function get_last_point (
geom mdsys.sdo_geometry
) return mdsys.sdo_geometry
is
begin
return get_point(geom, -1);
end;
/