Update coordinates after edit

oracleoracle-12cplsqltriggerupdate

I have a roads table. The road lines are stored in a user-defined type column called SHAPE.

Normally, spatial entities just have x and y dimensions. In my case, my roads have a measure(m) dimension; which means that I have x, y, and 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 x and 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)

Question:

Once the SHAPE column is edited (user updates the x and y), I want to take that new shape, reset the m values with my query, then update the SHAPE column with the reset m values.

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?

Update:

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 x and 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.

Best Answer

Looking at your query we can see that there are lots of details that cannot be discussed here. In order to get a trigger to work, here a small test setup that may help you to do the next steps ...

Not quite sure if the "LINESTRING M" values are stored in the same table as the original x/y values, that's why the example is written with 2 tables in mind: (1) "shape_table", containing objects of type shape, and (2) "m_values" with columns for road_id and "linestring m" (name shortened to m). DDL as below.

create or replace type shape_t as object (
  road_id number
, vertex_index number
, x number
, prev_x number
, y number
, prev_y number
, dist_to_prev number 
, member function getdistance return number
);
/

create table shape_table ( shp shape_t ) ;

create table m_values ( road_id number,  m varchar2(4000) );

After inserting some test data:

SQL> select * from shape_table;
SHP(ROAD_ID, VERTEX_INDEX, X, PREV_X, Y, PREV_Y, DIST_TO_PREV)
-----------------------------------------------------------------------------
SHAPE_T(100, 1, 0, 0, 5, 0,   0)
SHAPE_T(100, 2, 10, 0, 10, 5, 11.18)
SHAPE_T(100, 3, 30, 10, 0, 10, 22.36)
SHAPE_T(100, 4, 50, 30, 10, 0, 22.36) 
SHAPE_T(100, 5, 60, 50, 10, 10, 10) 


SQL> select * from m_values;
ROAD_ID  M                                                                        
100      LINESTRING M ( 0 5 0, 10 10 11.18, 30 0 33.54, 50 10 55.9, 60 10 65.9 )

Now, if we have procedure that re-calculates the "LINESTRING M" and uses it for updating (the SELECT used here looks different from the one in your question, due to various reasons*) 

create or replace procedure refresh_m_value
is 
  m_roadid number := 0 ;
  m_linestring varchar2(4000) := '' ;
begin
-- generate the new M LINESTRING
  select 
    road_id
  , 'LINESTRING M ( ' 
    || listagg( x || ' ' || y || ' ' || dist_ , ', ' )
       within group ( order by vi )
    || ' )' linestring
  into m_roadid, m_linestring
  from (
    select
      s.shp.road_id road_id
    , s.shp.vertex_index vi
    , round( s.shp.x, 2 ) x
    , round( s.shp.y, 2 ) y
    , sum ( s.shp.dist_to_prev ) 
        over ( partition by s.shp.road_id order by s.shp.vertex_index ) dist_
    from shape_table s
  ) roads_ 
  group by road_id ;
-- write the new M LINESTRING
  update m_values 
  set m = m_linestring
  where road_id = m_roadid ; 

end refresh_m_value;
/

Use a trigger for calling the procedure after the original values have been UPDATEd. Add a log table for debugging/testing etc.

-- table for log messages generated by the trigger (see INSERTs in trigger code)

create table trigger_log ( date_ date, message_ varchar2(128) );

create or replace trigger reset_m
  after update on shape_table 
declare
  error_code NUMBER;
  error_message VARCHAR2(128);
begin
  refresh_m_value;
  insert into trigger_log (date_, message_)
    values (sysdate, '[trigger: reset_m] refresh_m_value executed ') ;
exception
  when others then
    error_code := SQLCODE;
    error_message := SUBSTR(SQLERRM, 1 , 128);
    insert into trigger_log (date_, message_) 
      values (sysdate, '[exception] ' || error_code || ' - ' || error_message);
end;
/

Test the trigger, check the tables (and log).

update shape_table s
set s.shp.x = 0, s.shp.y = 5
where s.shp.road_id = 100
  and s.shp.vertex_index = 1 ;

select * from shape_table;

select * from trigger_log;

select * from m_values;
  • More details about the (imaginary) TYPE shape_t, and populating the "shapes_table" can be found here. However, using the dbfiddle, the TYPE was not created successfully, due to ORA-01031: insufficient privileges.
Related Question