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.
UPDATE OWNER1.ROAD_ROUTE_EVW SET SHAPE= --CONSTRUCT A WKT LINE: 'LINESTRING M ( X Y M, X Y M )', SRID SDE.ST_GEOMFROMTEXT('LINESTRING M(' || 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_LENGTH(SHAPE) || ')', 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).