For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
現在Standard Edition OneのDBを使用していますが、Enterprise Editionへの移行を検討しています。
「11gR2: Standard Edition から Enterprise Edition への移行に関する注意点(KROWN:156164) (ドキュメントID 1758758.1)」という資料を見つけたのですが
Standard Edition Oneからの移行にも適用できるのでしょうか。
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 How to calculate cumulative length of a linestring, or the truck tonnage that accumulates across a set of roads - The Spatial Database Advisor (0 Bytes)
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.
@jum3 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;