1

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.

2

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.
| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.