Oracle Spatial for the thrifty developer- Part 3: The basics of dynamic segmentation…

In this post I will show you a function that will return a linear segment along a line between a start and end length. This function together with the PointAlong() function as discussed in the previous post could be used as the basic blocks for building dynamic segmentation or address geocoding routines. At the end of the day these two processes share the same basic principle: to create a geometry (point or line) for a feature (known as an “event”), based on descriptive information only such as start/end address or start/end linear reference plus the underlying linear geometry the feature is located against.

The code for the LineAlong function is shown below:


FUNCTION lineAlong (pi_geom IN mdsys.sdo_geometry
,pi_from IN NUMBER
,pi_to IN NUMBER
,pi_srid IN NUMBER DEFAULT NULL)
RETURN mdsys.sdo_geometry IS
--
last1 INTEGER;
st_set BOOLEAN := TRUE;
i INTEGER;
i_v INTEGER;
n_srid NUMBER;
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_length1 NUMBER;
n_addr_length2 NUMBER;
n_run_length NUMBER;
n_dist NUMBER;
n_grad NUMBER;
found_start_pnt BOOLEAN;
n_dist_to_point NUMBER;
l_ords1 mdsys.sdo_ordinate_array; -- Holds the xy of the from address point
l_ords2 mdsys.sdo_ordinate_array; -- Holds the xy of the to address point
l_ords_all mdsys.sdo_ordinate_array; -- Holds the xy of the address range
n_ords_count NUMBER;
inval_geom_type EXCEPTION;
--
BEGIN
-- Do some validation
IF pi_from > pi_to THEN
raise_application_error(-20000,'[From] value must be greater than the [To] value');
END IF;
n_total_length := get_line_length(pi_geom); -- Get total length of shape
IF pi_to > n_total_length THEN
raise_application_error(-20000,'[To] value cannot be greater than the total line length');
END IF;
--
-- Initialise vars
n_grad := NULL;
n_dist := NULL;
l_geom_type := pi_geom.sdo_gtype;
n_srid := pi_srid;
found_start_pnt := FALSE;
--
--Check geometry type. Must be a 2d line
IF l_geom_type = 2002 THEN
-- Get number of vertices
last1 := pi_geom.sdo_ordinates.LAST;
--dbms_output.put_line('Total vertices:' || to_char(last1 / 2));
-- Initialise counter
i := 1;
-- Calculate absolute distances of from address point from segment start
n_addr_length1 := pi_from;
n_addr_length2 := pi_to;
--
-- Start looping through vertices
WHILE TRUE LOOP
IF st_set THEN
i_v := 1;
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);
-- Greate segment geometry
l_segment := get_line_from_xy(x_current
,y_current
,x_next
,y_next
,n_srid);
-- Get segment length
n_seg_length := get_line_length(l_segment);
n_run_length := n_seg_length;
--Find xy for start point address
IF found_start_pnt = FALSE THEN
IF n_run_length >= n_addr_length1 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);
n_dist_to_point := n_addr_length1;
l_ords1 := get_point_at_distance_as_ords(x_current
,y_current
,n_dist_to_point
,n_grad
,n_srid);
-- Insert into ordinate array
l_ords_all := mdsys.sdo_ordinate_array(l_ords1(1),
l_ords1(2));
found_start_pnt := TRUE;
END IF;
END IF;
--Find xy for end point address
IF n_run_length >= n_addr_length2 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);
n_dist_to_point := n_addr_length2;
l_ords2 := get_point_at_distance_as_ords(x_current
,y_current
,n_dist_to_point
,n_grad);
--
/*dbms_output.put_line('End X: ' || to_char(l_ords2(1)) ||
' End Y: ' || to_char(l_ords2(2)));
*/
IF found_start_pnt = FALSE THEN
raise_application_error(-20000,'Invalid start length');
END IF;
-- Insert into ordinate array
n_ords_count := l_ords_all.COUNT;
l_ords_all.EXTEND(2);
l_ords_all(n_ords_count + 1) := l_ords2(1);
l_ords_all(n_ords_count + 2) := l_ords2(2);
EXIT;
END IF;
--dbms_output.put_line('segment counter:' || to_char(i_v));
st_set := FALSE;
i := i + 2;
i_v := i_v + 1; --Increment segment counter
ELSE
-- Remaining segments
IF i + 1 >= last1 THEN
-- We are on the last pair
IF n_grad IS NULL OR n_dist IS NULL THEN
raise_application_error(-20000,'Could not create line');
END IF;
EXIT;
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);
--
IF found_start_pnt = TRUE THEN
-- Insert into ordinate array
n_ords_count := l_ords_all.COUNT;
l_ords_all.EXTEND(2);
l_ords_all(n_ords_count + 1) := x_current;
l_ords_all(n_ords_count + 2) := y_current;
--l_ords_all(n_ords_count+3) := x_next;
--l_ords_all(n_ords_count+4) := y_next;
END IF;
-- Greate segment geometry
l_segment := get_line_from_xy(x_current
,y_current
,x_next
,y_next
,n_srid);
-- Get segment length
n_seg_length := get_line_length(l_segment);
n_run_length := n_run_length + n_seg_length;
--Find xy for start point address
IF found_start_pnt = FALSE THEN
IF n_run_length >= n_addr_length1 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);
n_dist_to_point := n_seg_length -
(n_run_length - n_addr_length1);
l_ords1 := get_point_at_distance_as_ords(x_current
,y_current
,n_dist_to_point
,n_grad);
-- Insert into ordinate array
l_ords_all := mdsys.sdo_ordinate_array(l_ords1(1),
l_ords1(2));
found_start_pnt := TRUE;
END IF;
END IF;
i := i + 2;
--dbms_output.put_line('segment counter:' || to_char(i_v));
END IF;
--
--Find xy for end point address
IF n_run_length >= n_addr_length2 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);
n_dist_to_point := n_seg_length - (n_run_length - n_addr_length2);
l_ords2 := get_point_at_distance_as_ords(x_current,
y_current,
n_dist_to_point,
n_grad);
IF found_start_pnt = FALSE THEN
raise_application_error(-20000,'Invalid start length');
END IF;
-- Insert into ordinate array
n_ords_count := l_ords_all.COUNT;
l_ords_all.EXTEND(2);
l_ords_all(n_ords_count + 1) := l_ords2(1);
l_ords_all(n_ords_count + 2) := l_ords2(2);
END IF;
END LOOP;
/* -- DEBUG INFO
FOR i IN 1 .. l_ords_all.COUNT LOOP
dbms_output.put_line(to_char(l_ords_all(i)));
END LOOP;
*/
RETURN mdsys.sdo_geometry(2002,
n_srid,
NULL,
mdsys.sdo_elem_info_array(1,2,1),l_ords_all);
END IF;
EXCEPTION
WHEN inval_geom_type THEN
raise_application_error(-20003, 'Function does not support this spatial type (' || to_char(l_geom_type) || ')');
END lineAlong;

You can download the whole package with all the functions here.

One thought on “Oracle Spatial for the thrifty developer- Part 3: The basics of dynamic segmentation…

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