I have a
roads table. The road lines are stored in a user-defined type column called
Normally, spatial entities just have
y dimensions. In my case, my roads have a
measure(m) dimension; which means that I have
m dimensions. (
M values are used for linear referencing purposes.)
I have a problem where my GIS software does not automatically reset the
m values of the lines when the
y values are edited. This means that after lines are edited, the
m values become out-of-date/incorrect.
I've successfully figured out how to generate new
m values. Admittedly, the query is long and convoluted, but that's not the problem at hand. I'm working on simplifying it here.
select SDE.ST_GEOMFROMTEXT('LINESTRING M ( ' || listagg(X||' '||Y||' '||m,',') within group (order by VERTEX_INDEX) || ')', 300049) as WKT from (select ROAD_ID, VERTEX_INDEX, X, Y, ROUND(sum(m) over (partition by ROAD_ID order by VERTEX_INDEX),3) as m from (select ROAD_ID, VERTEX_INDEX, X, Y, case when VERTEX_INDEX = 1 then 0 else SQRT(power(abs((lag(X,1) over (partition by ROAD_ID order by VERTEX_INDEX))-X),2)+power(abs((lag(Y,1) over (partition by ROAD_ID order by VERTEX_INDEX))-Y),2)) end as m from ( select ROAD_ID, t.VERTEX_INDEX, SDE.ST_X(SDE.ST_POINTN(SHAPE, t.VERTEX_INDEX)) as X, SDE.ST_Y(SDE.ST_POINTN(SHAPE, t.VERTEX_INDEX)) as Y from USER1.ROADS cross join ( select level as VERTEX_INDEX from DUAL connect by level <= ( select max(SDE.ST_NUMPOINTS(SHAPE)) from USER1.ROADS ) ) t where t.VERTEX_INDEX <= SDE.ST_NUMPOINTS(SHAPE) and SDE.ST_GEOMETRYTYPE(SHAPE) <> 'ST_MULTILINESTRING' ) ) ) group by ROAD_ID Result: LINESTRING M ( 670495.21962780 4861662.29203628 0.00000000, 670644.70911652 4862034.07146977 400.70800781, 670579.99977367 4862214.15467259 592.06494141)
SHAPE column is edited (user updates the
y), I want to take that new shape, reset the
m values with my query, then update the
SHAPE column with the reset
I think the solution would be a fairly simple trigger. Unfortunately, I'm quite new at triggers, and am failing dramatically at making this one. How can this be done with a trigger?
I've drummed up a basic trigger to start from:
create or replace trigger "RESET_M" before update of SHAPE on USER1.ROADS for each row declare begin :new.SHAPE := :new.SHAPE; end;
Of course, this isn't helpful. It just sets the new
SHAPE to the same new
SHAPE, which is useless.
I don't know how I can do what I want by using my query. I want to get the edited/updated
y, calculate new
m values, and of course, commit it to the
SHAPE column. I imagine this would be quite easy if I were using functions. But the underlying reason why my giant query exists is that there isn't a function to do what I need to do.
And yes, Oracle Spatial does have a function called SDO_LRS.REDEFINE_GEOM_SEGMENT that does what I want. But I unfortunately, I don't have Oracle Spatial.