SpatialDB Advisor

Implementing a Parallel/ST_Parallel function for linestring data for Oracle Spatial

I have a MOVE/ST_TRANSLATE function in my GEOM PL/SQL package that moves all the points in an SDO_GEOMETRY object by a shift vector defined as a delta x and delta y.

But when I considered the idea of moving an SDO_GEOMETRY linestring object a set distance parallel to itself then the MOVE function becomes pointless as the direction of the movement of each vertex in the linestring is different depending on the direction of each segment (or vector) within the linestring.

The PARALLEL function is implemented in my GEOM package because of its dependence on other functions particularly the GetVector function. So, if you wish to see how it was implemented, download that package and have a look. Here is the documentation header and signature definition from the package.

  /** ----------------------------------------------------------------------------------------
  * function   : Parallel
  * precis     : Function that moves the supplied linestring left/right a fixed amount. Bends in the linestring, when moved, can remain vertex-connected or be converted to curves.
  * version    : 1.0
  * usage      : FUNCTION Parallel(p_geometry   in mdsys.sdo_geometry, 
  *                                 p_distance   in number,
  *                                 p_tolerance  in number,
  *                                 p_curved     in number := 0)
  *                 RETURN mdsys.sdo_geometry DETERMINISTIC;
  *               eg select Parallel(mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,1,10)),10,0.05) from dual;
  * param      : p_geometry  : Original linestring/multilinestring
  * paramtype  : p_geometry  : mdsys.sdo_geometry
  * param      : p_distance  : Distance to move parallel -vs = left; +ve = right
  * paramtype  : p_distance  : number
  * param      : p_tolerance : Standard Oracle diminfo tolerance.
  * paramType  : p_tolerance : Number
  * param      : p_curved    : Boolean flag indicating whether to stroke bends in line (1=stroke;0=leave alone)
  * paramType  : p_curved    : Integer (but really boolean)
  * return     : input geometry moved parallel by p_distance units
  * returnType : mdsys.sdo_geometry
  * history    : Simon Greener - Devember 2008 - Original coding.
  * copyright  : Free for public use
  **/
  FUNCTION Parallel(p_geometry   in mdsys.sdo_geometry, 
                    p_distance   in number,
                    p_tolerance  in number,
                    p_curved     in number := 0)
    RETURN mdsys.sdo_geometry DETERMINISTIC;

It is one of those interesting issues of software engineering that when we encapsulate functionality we end up with inter-dependence between elements. While PL/SQL’s packages allow for dependent objects to be associated within the same package, one still ends up with cross-package dependencies. I have often thought of not using packages – which would not be that difficult – but one would end up with needing a separate schema to hold the myriad of functions and object types that would be created c.f., PostGIS’s large function set. But one limitation stopping me form not using packages is the fact that Oracle standalone procedures and functions do not allow overloading: this is only possible within packages.

Let’s begin our testing by creating a table to hold the output of each test so we can generate a graphic with any GIS capable of connecting to Oracle (I use Manifold GIS Ultimate Edition ).

drop   table test_parallel;
create table test_parallel (
  oid    number,
  geom   mdsys.sdo_geometry );

Let’s start with something perhaps not so obvious: moving a single point “parallel” 10 meters:

select Geom.Parallel(mdsys.sdo_geometry(2001,null,null,sdo_elem_info_array(1,1,1),sdo_ordinate_array(1,1)),10,0.05) 
  from dual;

Error report:
SQL Error: ORA-20001: p_linestring is null or is not a linestring
ORA-06512: at "CODESYS.GEOM", line 3870
ORA-06512: at line 1

Yes, it is a silly thing to do and my function correctly detects it and throws an exception. Also, polygons are not handled by my function.

Prompt Neither should polygons
select Geom.Parallel(mdsys.sdo_geometry('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',NULL),10,0.05)  as geom
  from dual; 

Error report:
SQL Error: ORA-20001: p_linestring is null or is not a linestring
ORA-06512: at "CODESYS.GEOM", line 3870
ORA-06512: at line 1

I guess I could handle polygons by changing the way I handle the first and last point in the polygon. When I do this I will modify this article and upload the changed function.

Now, let’s move a simple linestring 10 meters to the right and left (-ve).

truncate table test_parallel;
insert into test_parallel 
select 1, mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,1,10)) from dual
union all
select 2, Geom.Parallel(mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,1,10)),10,0.05) from dual
union all
select 3, Geom.Parallel(mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,1,10)),-10,0.05) from dual
commit;

Now let’s look at a linestring composed of two segments with an acute angle bend between them. Because it makes no sense to apply a curve to the acute angle after moving parallel we set the p_curved parameter to 0 (setting to 1 will produce the same result).

Prompt Test Acute Angle
truncate table test_parallel;
insert into test_parallel 
select 1, mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,10,10,20,1)) from dual
union all
select 2, Geom.Parallel(mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,10,10,20,1)),5,0.05,0) from dual;
commit;

Now let’s look at a linestring composed of two segments with an obtuse angle bend between them. Because curving the angle of the resultant paralleled line makes sense, first we will create the parallel line without a curve, then with one.

Prompt Test Obtuse angle - Not curved
truncate table test_parallel;
insert into test_parallel 
select 1, mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,10,10,20,1)) from dual
union all
select 2, Geom.Parallel(mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,10,10,20,1)),-5,0.05,0) from dual;
commit;

Prompt Test Obtuse angle - Curved
truncate table test_parallel;
insert into test_parallel 
select 1, mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,10,10,20,1)) from dual
union all
select 2, Geom.Parallel(mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,10,10,20,1)),-5,0.05,1) from dual;
commit;

Not Curved	Curved

I don’t support linestrings with circular curves (as yet).

Prompt Compound Linestrings not supported
select 1, Geom.Parallel(MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,4,3,1,2,1,3,2,2,7,2,1),MDSYS.SDO_ORDINATE_ARRAY(-2.5,4.5,6.5,13.5,9.9,15,13.3,13.7,23.3,4.7)),5,0.05,1) 
  from dual;

Error report:
SQL Error: ORA-20001: Compound linestrings are not supported.
ORA-06512: at "CODESYS.GEOM", line 3873
ORA-06512: at line 1

But 3D linestrings are OK.

Prompt Check 3D linestring processing
select Geom.Parallel(mdsys.sdo_geometry(3002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,1,1,10,1)),10,0.05) as geom
  from dual;

Check 3D linestring processing
GEOM
------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(11,1,1,11,10,1))

1 rows selected

What happens if we hit two line segments which have no deflection angle (ie one goes straight on after the other)?

Prompt Check lines that are parallel or continue from last line without deflection
truncate table test_parallel;
insert into test_parallel
select 1, mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,1,10,1,100)) from dual
union all
select 2, Geom.Parallel(mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,1,10,1,100)),10,0.05) from dual;
commit;

Yes, the function handles this situation correctly. And it also handles it correctly for 3D linestrings:

Prompt Check 3D lines that are parallel or continue from last line without deflection
select Geom.Parallel(mdsys.sdo_geometry(3002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,9,1,10,9,1,100,9)),10,0.05) as geom
  from dual;

Check 3D lines that are parallel or continue from last line without deflection
GEOM
---------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(11,1,9,11,10,9,11,100,9))

1 rows selected

Now check a multilinestring with an acute and obtuse angle with p_curved = 1 (ie true).

truncate table test_parallel;
Prompt Check multilinestring with one linestring with an acute angle and the other an obtuse angle
insert into test_parallel 
select 1, mdsys.sdo_geometry(2006,null,null,sdo_elem_info_array(1,2,1,5,2,1),sdo_ordinate_array(1,1,10,10,20,1,50,50,100,0,150,50)) from dual;
union all
select 2, Geom.Parallel(mdsys.sdo_geometry(2006,null,null,sdo_elem_info_array(1,2,1,5,2,1),sdo_ordinate_array(1,1,10,10,20,1,50,50,100,0,150,50)),10,0.05,1) from dual;
commit;

Now, quickly check 3D and 4D linestrings with measures. Note that the NULL values in the SDO_Ordinate array are set to 0 by the SDO_UTIL.APPEND function (I will change this at some stage).

Prompt Check 2D line with LRS measures
select Geom.parallel(SDO_GEOMETRY(3302, NULL, NULL,
                     SDO_ELEM_INFO_ARRAY(1,2,1),
                     SDO_ORDINATE_ARRAY(5,10,0, 20,5,NULL, 35,10,NULL, 55,10,100)),1,0.05,1) 
         as geom
  from dual;

Check 2D line with LRS measures
GEOM
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3302,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,4,3,1,2,1,4,2,2,10,2,1),MDSYS.SDO_ORDINATE_ARRAY(4.7,9.1,0,19.7,4.1,0,20,4,0,20.3,4.1,0,35.2,9,0,55,9,100))

1 rows selected

Prompt Check 3D line with LRS measures
select Geom.parallel(SDO_GEOMETRY(4402, NULL, NULL,
                     SDO_ELEM_INFO_ARRAY(1,2,1),
                     SDO_ORDINATE_ARRAY(5,10,500,0, 20,5,501,NULL, 35,10,502,NULL, 55,10,503,100)),1,0.05,1)
         as geom
  from dual;

Check 3D line with LRS measures
GEOM
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(4402,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,4,3,1,2,1,5,2,2,13,2,1),MDSYS.SDO_ORDINATE_ARRAY(4.7,9.1,500,0,19.7,4.1,501,0,20,4,501,0,20.3,4.1,501,0,35.2,9,502,0,55,9,503,100))

1 rows selected

Finally, let’s finish with a linestring with many acute/obtuse bends creating a parallel line on the left and on the right all with p_curved = 1.

Prompt Finish with left/right parallel of linestring with acute/obtuse bends with p_curve = 1
truncate table test_parallel;
insert into test_parallel (oid,geom)
select 1, mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,0,45,45,90,0,135,45,180,0,180,-45,45,-45,0,0)) as geom 
  from dual
union all
select rin + 1, Parallel(b.geom,case when rin = 2 then -1 else 1 end * 10,0.005,1) as geom
  from (select level as rin, mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,0,45,45,90,0,135,45,180,0,180,-45,45,-45,0,0)) as geom 
         from dual
         where level between 1 and 2
         connect by level < 3) b;
commit;

I hope this function is useful to someone.