Oracle Spatial for the thrifty developer- Part 2: Calculating line lengths and more…

In this post we will talk about calulating line lengths as well as finding a point at a percentage along a line. This second function will prove quite useful as will be seen in next posts to implement simple address geocoding or building your own version of dynamic segmentation routines.
You can download the whole package here.

Calculate Line Lengths
This will return the length of a single-part polyline

FUNCTION get_line_length(pi_geom IN mdsys.sdo_geometry) RETURN NUMBER IS
last1 INTEGER;
st_set BOOLEAN := TRUE;
i INTEGER;
x_current NUMBER;
y_current NUMBER;
x_next NUMBER;
y_next NUMBER;
l_geom_type NUMBER;
n_total_length NUMBER;
ntol NUMBER;
inval_geom_type EXCEPTION;
BEGIN
--Initialize vars
l_geom_type := pi_geom.sdo_gtype;
n_total_length := 0;
i := 1;
last1 := pi_geom.sdo_ordinates.LAST;
ntol:= c_tol;
-- Validate input params
IF l_geom_type = 2002 THEN
-- Start looping through vertices
WHILE TRUE LOOP
IF st_set THEN
x_current := pi_geom.sdo_ordinates(i);
y_current := pi_geom.sdo_ordinates(i + 1);
x_next := pi_geom.sdo_ordinates(i + 2);
y_next := pi_geom.sdo_ordinates(i + 3);
n_total_length := n_total_length +
sdo_geom.sdo_distance(
get_pt_from_xy(x_current,
y_current),
get_pt_from_xy(x_next,
y_next),
to_number(ntol));
st_set := FALSE;
i := i + 2;
ELSE
IF i + 1 >= last1 THEN
-- We are on the last pair
RETURN n_total_length;
END IF;
--
x_current := pi_geom.sdo_ordinates(i);
y_current := pi_geom.sdo_ordinates(i + 1);
x_next := pi_geom.sdo_ordinates(i + 2);
y_next := pi_geom.sdo_ordinates(i + 3);
i := i + 2;
n_total_length := n_total_length +
sdo_geom.sdo_distance(
get_pt_from_xy(
x_current,
y_current),
get_pt_from_xy(
x_next,
y_next),
to_number(ntol));
END IF;
END LOOP;
RETURN n_total_length;
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN inval_geom_type THEN
raise_application_error(-20000,
'Funtion does not support this spatial type
(' || to_char(l_geom_type) || ')');
END get_line_length;

Get Point Along Line

This function will return a point at a percentage along a Polyline:

FUNCTION pointAlong(pi_line IN mdsys.sdo_geometry
,pi_pct IN NUMBER
,pi_srid IN NUMBER)
RETURN mdsys.sdo_geometry IS
---
last1 INTEGER;
st_set BOOLEAN := TRUE;
i INTEGER;
l_segment mdsys.sdo_geometry;
x_current NUMBER;
y_current NUMBER;
x_next NUMBER;
y_next NUMBER;
l_geom_type NUMBER;
n_total_length NUMBER;
n_seg_length NUMBER;
n_addr_length NUMBER;
n_run_length NUMBER;
n_dist NUMBER;
n_grad NUMBER;
l_pnt mdsys.sdo_geometry;
v_dist_to_point NUMBER;
inval_geom_type EXCEPTION;
inval_pct EXCEPTION;
--
BEGIN
-- Initialise vars
n_grad := NULL;
n_dist := NULL;
l_geom_type := pi_line.sdo_gtype;
--
IF pi_pct NOT BETWEEN 0 AND 100 THEN
RAISE inval_pct;
END IF;
--
--Check that we have a simple line geometry
--
IF l_geom_type = 2002 THEN
-- Get number of vertices
last1 := pi_line.sdo_ordinates.LAST;
-- Initialise counter
i := 1;
-- Get total length of line
n_total_length := get_line_length(pi_line);
-- Calculate required length
n_addr_length := n_total_length*(pi_pct/100);
WHILE TRUE LOOP
IF st_set THEN
x_current := pi_line.sdo_ordinates(i);
y_current := pi_line.sdo_ordinates(i + 1);
x_next := pi_line.sdo_ordinates(i + 2);
y_next := pi_line.sdo_ordinates(i + 3);
-- Greate segment geometry
l_segment := get_line_from_xy(x_current
,y_current
,x_next
,y_next
,pi_srid);
-- Get segment length
n_seg_length := get_line_length(l_segment);
n_run_length := n_seg_length;
IF n_run_length >= n_addr_length THEN
-- Found segment where point should be on. Calculate exact location and exit loop
n_grad:= get_grad_from_xy(x_current,
y_current,
x_next,
y_next);
n_dist:= get_length_from_xy(x_current,
y_current,
x_next,
y_next);
v_dist_to_point := n_addr_length;
EXIT;
END IF;
st_set := FALSE;
i := i + 2;
ELSE
IF i + 1 >= last1 THEN
-- We are on the last pair
IF n_grad IS NULL OR n_dist IS NULL THEN
-- Something went wrong...
raise_application_error(
-20000
,'Could not loop through geometry vertices. Invalid Geometry?');
END IF;
EXIT;
END IF;
x_current := pi_line.sdo_ordinates(i);
y_current := pi_line.sdo_ordinates(i + 1);
x_next := pi_line.sdo_ordinates(i + 2);
y_next := pi_line.sdo_ordinates(i + 3);
-- Greate segment geometry
l_segment := get_line_from_xy(x_current
,y_current
,x_next
,y_next
,pi_srid);
-- Get segment length
n_seg_length := get_line_length(l_segment);
n_run_length := n_run_length + n_seg_length;
IF n_run_length >= n_addr_length THEN
n_grad := get_grad_from_xy(x_current,
y_current,
x_next,
y_next);
n_dist := get_length_from_xy(x_current,
y_current,
x_next,
y_next);
v_dist_to_point := n_seg_length -
(n_run_length - n_addr_length);
EXIT;
END IF;
i := i + 2;
END IF;
END LOOP;
l_pnt := get_point_at_distance(x_current
,y_current
,v_dist_to_point
,n_grad);
RETURN l_pnt;
ELSE
RAISE inval_geom_type;
END IF;
EXCEPTION
WHEN inval_geom_type THEN
raise_application_error(-20000,'Funtion does not support this spatial type (
' || to_char(l_geom_type) || ')');
WHEN inval_pct THEN
raise_application_error(-20000,'Value must be between 0 and 100');
END pointAlong;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s