Route Example

I'm trying to write a query to update sde.st_geometry M values to the cumulative length of a line.

The M values in my route feature class are purely based on the geometric length of the line. The lines/routes have not been calibrated or anchored using referents.

Here's what I've tried:

I've successfully made a query that almost does this. It replaces the shape column with a new shape. The new shape is a copy of the existing shape, except the start point M value has been set to 0, and the end point M value has been set to the geometric length of the line.


     || sde.st_x(sde.st_pointn(SHAPE,1))
     || ' '
     || sde.st_y(sde.st_pointn(SHAPE,1))
     || ' 0, ' 
     || sde.st_x(sde.st_pointn(SHAPE,2))
     || ' ' 
     || sde.st_y(sde.st_pointn(SHAPE,2))
     || ' '
     || ')', SDE.ST_SRID(SHAPE))

The catch is that this only works for lines with two vertices (start point and end point). But, of course, my lines(roads) have more than two vertices (the number of vertices varies). So the SQL above is useless.


What I think needs to happen is that I would somehow need to loop through each vertex, using the st_pointn index position, and set the M to the cumulative length of the line. I don't believe there is a way to do this with sde.st_geometry functions (the sde.ST_NumPoints function might be of use though).

  • 1
    Did you try Simon Greener's implementation of ST_AddMeasure? Sep 7, 2016 at 18:55
  • 1
    To clarify, this is about ESRI's implementation of ST_GEOMETRY, not Oracle's implementation. While Oracle's native implementation of ST_GEOMETRY is fully compatible and interoperable with SDO_GEOMETRY, ESRI's implementation is not. But yes, converting the ST_GEOMETRY object to WKB and passing that to SDO_GEOMETRY will turn it into a valid SDO_GEOMETRY object. Then you can use Oracle's SDO_LRS functions to do what you are looking for. Keeping in mind that in order to do so, you must be LICENSED for Oracle Spatial! Sep 8, 2016 at 8:39
  • 1
    I'm not sure why ST_AddMeasure is missing from Greener's code. Sep 8, 2016 at 19:30
  • 1
    If you're using VBA, it seems like IMSegmentation2.SetMsAsDistance would simplify things. Sep 9, 2016 at 14:04
  • 1
    Re: licensing. Oracle Spatial is a paid option on top of Oracle Enterprise Edition. What you get with EE (and actually with all editions) is Oracle Locator that provides you with full support of 2D vector data. Anything else (3D, raster, LRS, networks, geocoding, point clouds ... ) comes with Oracle Spatial. Note that even though you may have the SDO_LRS package in your database, you are not allowed to use it without proper licensing. Sep 12, 2016 at 8:47


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.