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? – Kirk Kuykendall Sep 7 '16 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! – Albert Godfrind Sep 8 '16 at 8:39
  • 1
    I'm not sure why ST_AddMeasure is missing from Greener's code. – Kirk Kuykendall Sep 8 '16 at 19:30
  • 1
    If you're using VBA, it seems like IMSegmentation2.SetMsAsDistance would simplify things. – Kirk Kuykendall Sep 9 '16 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. – Albert Godfrind Sep 12 '16 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.