Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 442 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Code Review: Update polyline vertices with cumulative length of line

I have a multi-part polyline in a row in an Oracle 18c table. The polyline's geometry information is stored in a SHAPE column; the datatype is a user-defined spatial type called SDE.ST_Geometry.
I've written a PL/SQL function call m_as_length()
that replaces a coordinate in the polyline's vertices with the cumulative length of the line. The coordinate is called an "M" coordinate (aka a "Measure-value"). M coordinates are similar to X and Y coordinates, but are used for specialized linear referencing purposes.
Input: MULTILINESTRING M (( 0.0 5.0 -100000.0, 10.0 10.0 -100000.0, 30.0 0.0 -100000.0),( 50.0 10.0 -100000.0, 60.0 10.0 -100000.0)) --select sde.st_astext(shape) from polylines Output: MULTILINESTRING M ((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54)) --select m_as_length(shape) from polylines
Question:
I'm a novice when it comes to PL/SQL. Can the m_as_length()
function be improved?
with function pythagoras(x1 in number, y1 in number, x2 in number, y2 in number) return number is begin return round( sqrt(power(x2 - x1, 2) + power(y2 - y1, 2)) ,2); end; function m_as_length(shape in sde.st_geometry) return varchar2 is result varchar2(32767); vertex varchar2(32767); vertex_set varchar2(32767); i number; j number; num_parts number; num_points number; oldX number; oldY number; newX number; newY number; line_len number; begin num_parts := sde.st_geometry_operators.st_numgeometries_f(shape); oldX := sde.st_geometry_operators.st_x_f(sde.st_geometry_operators.st_startpoint_f(sde.st_geometry_operators.st_geometryN_f(shape,1))); oldY := sde.st_geometry_operators.st_y_f(sde.st_geometry_operators.st_startpoint_f(sde.st_geometry_operators.st_geometryN_f(shape,1))); line_len := 0; for i in 1..num_parts loop num_points := sde.st_geometry_operators.st_numpoints_f(sde.st_geometry_operators.ST_GeometryN_f(shape,i)); vertex_set := null; for j in 1..num_points loop newX := sde.st_geometry_operators.st_x_f(sde.st_geometry_operators.st_pointn_f(sde.st_geometry_operators.st_geometryn_f(shape,i),j)); newY := sde.st_geometry_operators.st_y_f(sde.st_geometry_operators.st_pointn_f(sde.st_geometry_operators.st_geometryn_f(shape,i),j)); if j <> 1 then line_len := line_len + pythagoras(oldX, oldY, newX, newY); end if; oldX := newX; oldY := newY; vertex := newX || ' ' || newY || ' ' || line_len; vertex_set := vertex_set || vertex || ', '; end loop; vertex_set := '(' || rtrim((vertex_set),', ') || '),'; result := result || vertex_set; end loop; result := 'MULTILINESTRING M (' || rtrim((result),',') || ')'; return result; end; select m_as_length(shape) from polylines
Related:
- Use SDE.ST_GEOMETRY functions in a custom function
- Explains why the function calls are so verbose:
sde.st_geometry_operators.st_numgeometries_f
.
- Explains why the function calls are so verbose:
- ST_GEOMETRY functions (links):
- I have a JavaScript expression that serves the same purpose (different use case). It's easier to read than the PL/SQL function.
Answers
-
It seems to me you'd be better off encapsulating such methods in a descendant object type, but anyway...
Code seems ok, but a couple of points :
Replace power(..), with a squaring multiplication. Power is great, has its place, but it's relatively slow.
You seem to be overly assigning everything to variables. Whilst that's good for instrumentation if you're outputting everything, it slows code down as values need to be loaded in registers, range and type checked and it makes code more verbose.
I think your code can be rewritten as this :
with function pythagoras(x1 in number, y1 in number, x2 in number, y2 in number) return number is begin return round(sqrt( (x2 - x1) * (x2 - x1) + (y2 - y1) * (y2 - y1)), 2); -- Power is a slow function end; function m_as_length(shape in sde.st_geometry) return varchar2 is result varchar2(32767); vertex_set varchar2(32767); oldX number; oldY number; newX number; newY number; line_len number := 0; begin for vPartIndex in 1..sde.st_geometry_operators.st_numgeometries_f(shape) loop vertex_set := null; for vPointIndex in 1..sde.st_geometry_operators.st_numpoints_f(sde.st_geometry_operators.ST_GeometryN_f(shape,vPartIndex)) loop newX := sde.st_geometry_operators.st_x_f(sde.st_geometry_operators.st_pointn_f(sde.st_geometry_operators.st_geometryn_f(shape,vPartIndex),vPointIndex)); newY := sde.st_geometry_operators.st_y_f(sde.st_geometry_operators.st_pointn_f(sde.st_geometry_operators.st_geometryn_f(shape,vPartIndex),vPointIndex)); if vPointIndex <> 1 then line_len := line_len + pythagoras(oldX, oldY, newX, newY); end if; oldX := newX; oldY := newY; vertex_set := vertex_set || newX || ' ' || newY || ' ' || line_len || ', '; end loop; result := result || '(' || rtrim((vertex_set),', ') || '),'; end loop; return 'MULTILINESTRING M (' || rtrim((result),',') || ')'; end; select m_as_length(shape) from polylines
-
Have a look at the fine article"How to calculate cumulative length of a linestring" by
The Spatial Database Advisor
-
@Paulzip Thanks! That helps. And of course, your revised function produces the correct result:
MULTILINESTRING M ((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))
Side question:
Regarding your comment "It seems to me you'd be better off encapsulating such methods in a descendant object type...". Is there any chance you could elaborate on that?
Related:
- Link: More succinct SDE.ST_GEOMETRY function calls in PL/SQL
- Link: ST_Geometry data type and its subclasses
Thanks again.
-
If you use (free) LRS-functions you can directly achieve the result:
SELECT SDO_LRS.CONVERT_TO_LRS_GEOM( SDO_CS.make_2d( SDO_UTIL.FROM_WKTGEOMETRY('MULTILINESTRING (( 0.0 5.0 -100000.0, 10.0 10.0 -100000.0, 30.0 0.0 -100000.0),( 50.0 10.0 -100000.0, 60.0 10.0 -100000.0))') )) lrsline FROM dual;
{ "spatialdimension" : 2, "geometrycollection" : {"geometries" : [{"line" : {"datapoints" : [[0,5,0],[10,10,11.180339887499],[30,0,33.5410196624969]]}}, {"line" : {"datapoints" : [[50,10,33.5410196624969],[60,10,43.5410196624969]]}}]} }
Btw. if you code a function DETERMINISTIC clause could improve perfomance.
-
I don't have that spatial library installed, but can assume ST_GEOMETRY appears to be an object type. As such, you could inherit from it and add a method "m_as_length" to that object type.
-
@_jum Nice! That LRS-function solution is very simple. I like it.
Out of curiosity, how did you output the geometry to JSON? Just curious what technique you used...
-
Tested it with https://livesql.oracle.com
-
Side note:
In my original function (and in @Paulzip 's code review), we defined the variables as:
result varchar2(32767); vertex_set varchar2(32767);
Alternatively, I suppose we could have defined those variables a CLOBs:
result clob; vertex_set clob;