Home » Server Options » Spatial » Merging line geomrtry in a particular order (Oracle 10g R2 - Windows 2000)
Merging line geomrtry in a particular order [message #524448] Fri, 23 September 2011 14:51 Go to next message
yuko
Messages: 65
Registered: August 2011
Member
Hi All,

Need your help for the below requirement.
I have a STREETS table which contains the line geometry for the streets and the line segment id.(The data is 3D with X,Y,Z coordinates where Z is zero)
The requirement is the street line segments needs to be merged based on some condition
and the merged geometry should be updated back to the STREETS
table and the remaining geometry records should be deleted which are already merged.

There is another table CONCAT_INFO, which contains the information related to merging of street line geometries.
The CONCAT_INFO table has 3 columns FTR_ID,START_FTR_ID,SEQ.
SEQ represents the order in which the line geometries needs to be merged.
Wherever FTR_ID = START_FTR_ID, represents the start segment of the geometry.
Currently the SEQ column is NULL. The requirement is to merge the line segments in a particular and for
that the SEQ column of CONCAT_INFO table has to be updated with appropriate
sequence number based on coordinate values.

--Sample Data
drop table streets;
CREATE TABLE streets 
(
ftr_id NUMBER(10), 
shape MDSYS.SDO_GEOMETRY,
CONSTRAINT pk_street_ftr_id PRIMARY KEY (ftr_id)
);

----- Ist set of line segments to be merged
INSERT INTO streets VALUES
(
10,
SDO_GEOMETRY
(
3002, -- SDO_GTYPE  3002 represents 3-dimensional line string
NULL, -- SDO_SRID
NULL, -- SDO_POINT_TYPE is null
SDO_ELEM_INFO_ARRAY (1,2,1 ),--  Line string connected by straight lines
SDO_ORDINATE_ARRAY
(
1,1,0, -- X1, Y1, Z1 
2,2,0, -- X2, Y2,Z2 
3,3,0 -- X3, Y3,Z3
)
)
);

INSERT INTO streets VALUES
(
11,
SDO_GEOMETRY
(
3002, -- SDO_GTYPE  3002 represents 3-dimensional line string
NULL, -- SDO_SRID
NULL, -- SDO_POINT_TYPE is null
SDO_ELEM_INFO_ARRAY (1,2,1 ),--  Line string connected by straight lines
SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute
(
3,3,0, -- X1, Y1, Z1 
4,4,0, -- X2, Y2,Z2 
5,5,0 -- X3, Y3,Z3 
)
)
);

INSERT INTO streets VALUES
(
12,
SDO_GEOMETRY
(
3002, -- SDO_GTYPE  3002 represents 3-dimensional line string
NULL, -- SDO_SRID
NULL, -- SDO_POINT_TYPE is null
SDO_ELEM_INFO_ARRAY (1,2,1 ),--  Line string connected by straight lines
SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute
(
5,5,0, -- X1, Y1, Z1 
6,6,0, -- X2, Y2,Z2 
7,7,0 -- X3, Y3,Z3 
)
)
);

----- IInd set of line segments to be merged
INSERT INTO streets VALUES
(13,
SDO_GEOMETRY
(
3002, -- SDO_GTYPE  3002 represents 3-dimensional line string
NULL, -- SDO_SRID
NULL, -- SDO_POINT_TYPE is null
SDO_ELEM_INFO_ARRAY (1,2,1 ),--  Line string connected by straight lines
SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute
(
8,8,0, -- X1, Y1, Z1 
9,9,0, -- X2, Y2,Z2 
10,10,0 -- X3, Y3,Z3 
)
)
);

INSERT INTO streets VALUES
(
14,
SDO_GEOMETRY
(
3002, -- SDO_GTYPE  3002 represents 3-dimensional line string
NULL, -- SDO_SRID
NULL, -- SDO_POINT_TYPE is null
SDO_ELEM_INFO_ARRAY (1,2,1 ),--  Line string connected by straight lines
SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute
(
12,12,0, -- X1, Y1, Z1 
13,13,0, -- X2, Y2,Z2 
14,14,0 -- X3, Y3,Z3 
)
)
);

INSERT INTO streets VALUES
(
15,
SDO_GEOMETRY
(
3002, -- SDO_GTYPE  3002 represents 3-dimensional line string
NULL, -- SDO_SRID
NULL, -- SDO_POINT_TYPE is null
SDO_ELEM_INFO_ARRAY (1,2,1 ),--  Line string connected by straight lines
SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute
(
10,10,0, -- X1, Y1, Z1 
11,11,0, -- X2, Y2,Z2 
12,12,0 -- X3, Y3,Z3 
)
)
);

INSERT INTO streets VALUES
(
16,
SDO_GEOMETRY
(
3002, -- SDO_GTYPE  3002 represents 3-dimensional line string
NULL, -- SDO_SRID
NULL, -- SDO_POINT_TYPE is null
SDO_ELEM_INFO_ARRAY (1,2,1 ),--  Line string connected by straight lines
SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute
(
14,14,0, -- X1, Y1, Z1 
15,15,0, -- X2, Y2,Z2 
16,16,0 -- X3, Y3,Z3 
)
)
);

CONCAT_INFO table
DROP TABLE concat_info;
create table concat_info
(
ftr_id		number(10),
start_ftr_id	number(10),
seq			number(3)
);

INSERT INTO concat_info VALUES(10,10,NULL);
INSERT INTO concat_info VALUES(11,10,NULL);
INSERT INTO concat_info VALUES(12,10,NULL);

INSERT INTO concat_info VALUES(13,13,NULL);
INSERT INTO concat_info VALUES(14,13,NULL);
INSERT INTO concat_info VALUES(15,13,NULL);
INSERT INTO concat_info VALUES(16,13,NULL);
COMMIT ;

I am using SDO_AGGR_LRS_CONCAT function to merge the geometries
The problem is the line segments are not captured in a proper order.
This function works fine as long as the geometries are captured in a proper order, but in reality the lines segments are not captured
in a proper order and because of this the shape of the merged line is distracted.
Ex1: -- When the data is captured in proper order the resulting geometry is fine
SELECT SDO_AGGR_LRS_CONCAT(MDSYS.SDOAGGRTYPE(shape, 0.005)) SHAPE
FROM streets a, concat_info b
where a.ftr_id = b.ftr_id
and b.start_ftr_id = 10;

SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
1, 1, 0, 2, 2, 0, 3, 3, 0, 4, 4, 0, 5, 5, 0, 6, 6, 0, 7, 7, 0));

Ex2: -- When the data is not captured in proper order then the resulting geometry is distracted
SELECT SDO_AGGR_LRS_CONCAT(MDSYS.SDOAGGRTYPE(shape, 0.005)) SHAPE
FROM streets a, concat_info b
where a.ftr_id = b.ftr_id
and b.start_ftr_id = 13;

SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(3306, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1, 10, 2, 1, 19, 2, 1,
28, 2, 1), SDO_ORDINATE_ARRAY(8, 8, 0, 9, 9, 0, 10, 10, 0, 12, 12, 0, 13, 13, 0,
 14, 14, 0, 10, 10, 0, 11, 11, 0, 12, 12, 0, 14, 14, 0, 15, 15, 0, 16, 16, 0))
The result is invalid geometry

In order to solve this problem I need to generate the SEQ number for the segments to be joined in a proper order
UPDATE concat_info set seq = 1 WHERE FTR_ID=10;
UPDATE concat_info set seq = 2 WHERE FTR_ID=11;
UPDATE concat_info set seq = 3 WHERE FTR_ID=12;
UPDATE concat_info set seq = 1 WHERE FTR_ID=13;
UPDATE concat_info set seq = 3 WHERE FTR_ID=14;
UPDATE concat_info set seq = 2 WHERE FTR_ID=15;
UPDATE concat_info set seq = 4 WHERE FTR_ID=16;

SQL> select * from concat_info;
    FTR_ID START_FTR_ID        SEQ
---------- ---------- ----------
        10         10          1
        11         10          2
        12         10          3
        13         13          1
        14         13          3
        15         13          2
        16         13          4

The above SEQ number represents the order in which the line segements needs to be merged.
FTR_ID = START_FTR_ID represents the start segment and based on the end coordinates of the each line segment the next segment's sequence number ha to be generated.
When I use the ORDER BY clause the resulting geometry is fine even if the data is not captured properly

-- With order by SEQ
Ex1:
SELECT
SDO_AGGR_LRS_CONCAT(MDSYS.SDOAGGRTYPE(shape, 0.005)) SHAPE
FROM (
SELECT /*+ NO_MERGE */ shape
FROM streets a, concat_info b
where a.ftr_id = b.ftr_id
and b.start_ftr_id = 10
ORDER BY seq);

SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
1, 1, 0, 2, 2, 0, 3, 3, 0, 4, 4, 0, 5, 5, 0, 6, 6, 0, 7, 7, 0))

SELECT
SDO_AGGR_LRS_CONCAT(MDSYS.SDOAGGRTYPE(shape, 0.005)) SHAPE
FROM (
SELECT /*+ NO_MERGE */ shape
FROM streets a, concat_info b
where a.ftr_id = b.ftr_id
and b.start_ftr_id = 13
ORDER BY seq);

SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
8, 8, 0, 9, 9, 0, 10, 10, 0, 11, 11, 0, 12, 12, 0, 13, 13, 0, 14, 14, 0, 15, 15, 0, 16, 16, 0))

The resulting geometry is fine 

I'm using the below code to update the SEQ in CONCAT_INFO table.
It works fine as long as the line segments are captured in proper order, but if the line segments are not captured in proper order then it updates only 2 sequence numbers. i.e when the next matching coordinate is not available in the very next record then it updates only 2 sequence numbers and comes out of the loop. In that case the loop should iterate again for all the records where SEQ is NULL.
I'm struck up at this point, colud you please help me in fixing this code.
declare
v_crnt_seg_end_x		number;
v_crnt_seg_end_y		number;
v_nxt_seg_strt_x		number;
v_nxt_seg_strt_y		number;
v_seq					number ;
begin
update concat_info set seq = null;
-- When ftr_id = start_ftr_id set it to 1 to mark the starting segment
update concat_info set seq = 1 where ftr_id = start_ftr_id;
for cur_strt_seg in (select a.ftr_id, b.shape 
    from concat_info a, streets b
	where a.ftr_id = b.ftr_id 
	and a.seq = 1)
	loop
	-- Get the end coordinates of the starting line segment
	  v_crnt_seg_end_x := cur_strt_seg.shape.sdo_ordinates(cur_strt_seg.shape.sdo_ordinates.count-2);
	  v_crnt_seg_end_y := cur_strt_seg.shape.sdo_ordinates(cur_strt_seg.shape.sdo_ordinates.count-1);
	  v_seq := 1;
	  for cur_nxt_seg in (select a.ftr_id, b.shape 
	  from concat_info a, streets b 
	  where a.ftr_id = b.ftr_id
	  and a.start_ftr_id = cur_strt_seg.ftr_id
	  and a.ftr_id != cur_strt_seg.ftr_id)
	  loop
-- Get the start coordinates of the next line segment
	    v_nxt_seg_strt_x := cur_nxt_seg.shape.sdo_ordinates(1);
	    v_nxt_seg_strt_y := cur_nxt_seg.shape.sdo_ordinates(2);
-- If current segment's end coordinates are equal to next segment's start coordinates the update seq = seq+1
		if  (v_crnt_seg_end_x = v_nxt_seg_strt_x and v_crnt_seg_end_y = v_nxt_seg_strt_y) then
		v_seq := v_seq + 1;
		update concat_info set seq = v_seq 
                where ftr_id = cur_nxt_seg.ftr_id 
                and start_ftr_id = cur_strt_seg.ftr_id;
-- If this is true then get the next segment's end coordinates
      	        v_crnt_seg_end_x := cur_nxt_seg.shape.sdo_ordinates(cur_nxt_seg.shape.sdo_ordinates.count-2);
	        v_crnt_seg_end_y := cur_nxt_seg.shape.sdo_ordinates(cur_nxt_seg.shape.sdo_ordinates.count-1);
		end if ;
	  end loop ;
	end loop ;
commit ;
end;
/

PL/SQL procedure successfully completed.
SQL> select * from concat_info ;

    FTR_ID START_FTR_ID        SEQ
---------- ------------ ----------
        10           10          1
        11           10          2
        12           10          3
        13           13          1
        14           13
        15           13          2
        16           13

7 rows selected.
For START_FTR_ID = 13 only 2 records are updated.


Please let me know for any more details.

Thank you,
Yuko.
Re: Merging line geomrtry in a particular order [message #524456 is a reply to message #524448] Fri, 23 September 2011 19:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
One method would be to update the concat_info table using a hierarchical query, as demonstrated below.

-- test data:
SCOTT@orcl_11gR2> select * from streets
  2  /

    FTR_ID
----------
SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
        10
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
1, 1, 0, 2, 2, 0, 3, 3, 0))

        11
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
3, 3, 0, 4, 4, 0, 5, 5, 0))

        12
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
5, 5, 0, 6, 6, 0, 7, 7, 0))

        13
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
8, 8, 0, 9, 9, 0, 10, 10, 0))

        14
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
12, 12, 0, 13, 13, 0, 14, 14, 0))

        15
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
10, 10, 0, 11, 11, 0, 12, 12, 0))

        16
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
14, 14, 0, 15, 15, 0, 16, 16, 0))


7 rows selected.

SCOTT@orcl_11gR2> select * from concat_info
  2  /

    FTR_ID START_FTR_ID        SEQ
---------- ------------ ----------
        10           10
        11           10
        12           10
        13           13
        14           13
        15           13
        16           13

7 rows selected.


-- update:
SCOTT@orcl_11gR2> update concat_info ci
  2  set    ci.seq =
  3  	    (select x.seq
  4  	     from   (select ftr_id, start_ftr_id,
  5  			    row_number () over
  6  			      (partition by start_ftr_id
  7  			       order by rownum) seq
  8  		     from   (select ftr_id, start_ftr_id,
  9  				    start_ftr_id
 10  				    || ','
 11  				    || sum (decode (rn2, 2, column_value))
 12  				    || ','
 13  				    || sum (decode (rn2, 1, column_value)) ender,
 14  				    start_ftr_id
 15  				    || ','
 16  				    || sum (decode (rn1, 4, column_value))
 17  				    || ','
 18  				    || sum (decode (rn1, 5, column_value)) starter
 19  			     from   (select a.ftr_id, a.start_ftr_id, t.*,
 20  					    row_number () over
 21  					      (partition by a.ftr_id
 22  					       order by column_value) rn1,
 23  					    row_number () over
 24  					      (partition by a.ftr_id
 25  					       order by column_value desc) rn2
 26  				     from   concat_info a, streets b,
 27  					    table (b.shape.sdo_ordinates) t
 28  				     where  a.ftr_id = b.ftr_id)
 29  			     group  by ftr_id, start_ftr_id)
 30  		     start  with ftr_id = start_ftr_id
 31  		     connect by prior ender = starter) x
 32  	     where  ci.ftr_id = x.ftr_id
 33  	     and    ci.start_ftr_id = x.start_ftr_id)
 34  /

7 rows updated.


-- results:
SCOTT@orcl_11gR2> select * from concat_info
  2  /

    FTR_ID START_FTR_ID        SEQ
---------- ------------ ----------
        10           10          1
        11           10          2
        12           10          3
        13           13          1
        14           13          3
        15           13          2
        16           13          4

7 rows selected.

SCOTT@orcl_11gR2> SELECT SDO_AGGR_LRS_CONCAT(MDSYS.SDOAGGRTYPE(shape, 0.005)) SHAPE
  2  FROM   (SELECT /*+ NO_MERGE */ shape
  3  	     FROM   streets a, concat_info b
  4  	     where  a.ftr_id = b.ftr_id
  5  	     and    b.start_ftr_id = 10
  6  	     ORDER  BY seq)
  7  /

SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
1, 1, 0, 2, 2, , 3, 3, , 4, 4, , 5, 5, 0, 6, 6, , 7, 7, 0))


1 row selected.

SCOTT@orcl_11gR2> SELECT SDO_AGGR_LRS_CONCAT(MDSYS.SDOAGGRTYPE(shape, 0.005)) SHAPE
  2  FROM   (SELECT /*+ NO_MERGE */ shape
  3  	     FROM   streets a, concat_info b
  4  	     where  a.ftr_id = b.ftr_id
  5  	     and    b.start_ftr_id = 13
  6  	     ORDER  BY seq)
  7  /

SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
8, 8, 0, 9, 9, , 10, 10, , 11, 11, , 12, 12, , 13, 13, , 14, 14, 0, 15, 15, , 16
, 16, 0))


1 row selected.

SCOTT@orcl_11gR2>

Re: Merging line geomrtry in a particular order [message #524510 is a reply to message #524456] Sat, 24 September 2011 06:21 Go to previous messageGo to next message
yuko
Messages: 65
Registered: August 2011
Member
Hi Barbara Boehmer,
Thank you for the response.
I tested the UPDATE statement for different scenarios, what I observed is the UPDATE statement works fine as long as there are
only 3 coordinates in each line segment and the query fails if the number of coordinates are more than 3.

Below are 2 more test cases.
Test Case 1 -- Where each line segment has 3 coordinates

delete from streets where ftr_id in (24,23,22,25);

-- IInd Segment
INSERT INTO streets VALUES
(24,
SDO_GEOMETRY
(
3002, -- SDO_GTYPE  3002 represents 3-dimensional line string
NULL, -- SDO_SRID
NULL, -- SDO_POINT_TYPE is null
SDO_ELEM_INFO_ARRAY (1,2,1 ),--  Line string connected by straight lines
SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute
(
29.235,29.4351,0, -- X1, Y1, Z1 
30.234,30.768,0, -- X2, Y2,Z2 
31.4321,31.4632,0 -- X3, Y3,Z3 
)
)
);

-- IIIrd Segment

INSERT INTO streets VALUES
(
23,
SDO_GEOMETRY
(
3002, -- SDO_GTYPE  3002 represents 3-dimensional line string
NULL, -- SDO_SRID
NULL, -- SDO_POINT_TYPE is null
SDO_ELEM_INFO_ARRAY (1,2,1 ),--  Line string connected by straight lines
SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute
(
31.4321,31.4632,0, -- X1, Y1, Z1 
32.6564,32.4567,0, -- X2, Y2,Z2 
33.8790,33.4512,0 -- X3, Y3,Z3 
)
)
);


-- Ist Segment

INSERT INTO streets VALUES
(
22,
SDO_GEOMETRY
(
3002, -- SDO_GTYPE  3002 represents 3-dimensional line string
NULL, -- SDO_SRID
NULL, -- SDO_POINT_TYPE is null
SDO_ELEM_INFO_ARRAY (1,2,1 ),--  Line string connected by straight lines
SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute
(
27.4567,27.3421,0, -- X1, Y1, Z1 
28.92,28.63,0, -- X2, Y2,Z2 
29.235,29.4351,0 -- X3, Y3,Z3 
)
)
);

-- IVth Segment

INSERT INTO streets VALUES
(25,
SDO_GEOMETRY
(
3002, -- SDO_GTYPE  3002 represents 3-dimensional line string
NULL, -- SDO_SRID
NULL, -- SDO_POINT_TYPE is null
SDO_ELEM_INFO_ARRAY (1,2,1 ),--  Line string connected by straight lines
SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute
(
33.8790,33.4512,0, -- X1, Y1, Z1 
34.2346,34.8690,0, -- X2, Y2,Z2 
35.3218,35.7389,0 -- X3, Y3,Z3 
)
)
);

delete from concat_info where start_ftr_id = 22;
INSERT INTO concat_info VALUES(24,22,NULL);
INSERT INTO concat_info VALUES(23,22,NULL);
INSERT INTO concat_info VALUES(22,22,NULL);
INSERT INTO concat_info VALUES(25,22,NULL);
COMMIT ;


Test Case 2 -- Where each line segment has more than 3 coordinates
delete from streets where ftr_id in (18,19,20,21);

-- IInd Segment
INSERT INTO streets VALUES
(18,
SDO_GEOMETRY
(
3002, -- SDO_GTYPE  3002 represents 3-dimensional line string
NULL, -- SDO_SRID
NULL, -- SDO_POINT_TYPE is null
SDO_ELEM_INFO_ARRAY (1,2,1 ),--  Line string connected by straight lines
SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute
(
19.2347,19.54321,0, -- X1, Y1, Z1 
20.234,20.768,0, -- X2, Y2,Z2 
21.4321,21.4632,0, -- X3, Y3,Z3 
22.45637,22.48430,0 -- X4, Y4,Z4 
)
)
);

-- Ist Segment

INSERT INTO streets VALUES
(
19,
SDO_GEOMETRY
(
3002, -- SDO_GTYPE  3002 represents 3-dimensional line string
NULL, -- SDO_SRID
NULL, -- SDO_POINT_TYPE is null
SDO_ELEM_INFO_ARRAY (1,2,1 ),--  Line string connected by straight lines
SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute
(
11.25,11.65,0, -- X1, Y1, Z1 
13.23,14.29,0, -- X2, Y2,Z2 
15.68,15.97,0, -- X3, Y3,Z3 
16.28,17.34,0, -- X4, Y4,Z4 
18.723,17.4678,0, -- X5, Y5,Z5 
19.2347,19.54321,0 -- X5, Y5,Z5 
)
)
);


-- IVth Segment
INSERT INTO streets VALUES
(
21,
SDO_GEOMETRY
(
3002, -- SDO_GTYPE  3002 represents 3-dimensional line string
NULL, -- SDO_SRID
NULL, -- SDO_POINT_TYPE is null
SDO_ELEM_INFO_ARRAY (1,2,1 ),--  Line string connected by straight lines
SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute
(
27.4567,27.3421,0, -- X1, Y1, Z1 
28.92,28.63,0, -- X2, Y2,Z2 
29.235,29.4351,0, -- X3, Y3,Z3 
30.327,30.678123,0, -- X4, Y4,Z4 
31.65480,31.6548,0, -- X5, Y5,Z5 
32.7659,32.9030,0 -- X6, Y6,Z6 
)
)
);

-- IIIrd Segment
INSERT INTO streets VALUES
(20,
SDO_GEOMETRY
(
3002, -- SDO_GTYPE  3002 represents 3-dimensional line string
NULL, -- SDO_SRID
NULL, -- SDO_POINT_TYPE is null
SDO_ELEM_INFO_ARRAY (1,2,1 ),--  Line string connected by straight lines
SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute
(
22.45637,22.48430,0, -- X1, Y1, Z1 
23.6547,23.67890,0, -- X2, Y2,Z2 
24.67976,24.676975,0, -- X3, Y3,Z3 
25.12689,25.8747,0, -- X4, Y4,Z4 
26.3412,26.589,0, -- X5, Y5,Z5 
27.4567,27.3421,0 -- X6, Y6,Z6 
)
)
);

delete from concat_info where start_ftr_id = 19;
INSERT INTO concat_info VALUES(18,19,NULL);
INSERT INTO concat_info VALUES(19,19,NULL);
INSERT INTO concat_info VALUES(21,19,NULL);
INSERT INTO concat_info VALUES(20,19,NULL);
COMMIT ;


Below is the ouput of the update statement
SQL> select * from concat_info;

    FTR_ID START_FTR_ID        SEQ
---------- ------------ ----------
        10           10          1
        11           10          2
        12           10          3
        13           13          1
        14           13          3
        15           13          2
        16           13          4
        24           22          2
        23           22          3
        22           22          1
        25           22          4
        18           19
        19           19          1
        21           19
        20           19

15 rows selected.


In the actual data there will be N number of coodinates in a
line segment and the number of coordinates for each line segment vary from each other.

Could you please help me to solve this problem.

Thank you,
Yuko
Re: Merging line geomrtry in a particular order [message #524567 is a reply to message #524510] Sat, 24 September 2011 16:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The following method uses a recursive procedure with sdo_touch and a spatial index.

-- test data:
SCOTT@orcl_11gR2> select * from streets
  2  /

    FTR_ID
----------
SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
        10
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
1, 1, 0, 2, 2, 0, 3, 3, 0))

        11
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
3, 3, 0, 4, 4, 0, 5, 5, 0))

        12
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
5, 5, 0, 6, 6, 0, 7, 7, 0))

        13
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
8, 8, 0, 9, 9, 0, 10, 10, 0))

        14
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
12, 12, 0, 13, 13, 0, 14, 14, 0))

        15
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
10, 10, 0, 11, 11, 0, 12, 12, 0))

        16
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
14, 14, 0, 15, 15, 0, 16, 16, 0))

        24
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
29.235, 29.4351, 0, 30.234, 30.768, 0, 31.4321, 31.4632, 0))

        23
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
31.4321, 31.4632, 0, 32.6564, 32.4567, 0, 33.879, 33.4512, 0))

        22
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
27.4567, 27.3421, 0, 28.92, 28.63, 0, 29.235, 29.4351, 0))

        25
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
33.879, 33.4512, 0, 34.2346, 34.869, 0, 35.3218, 35.7389, 0))

        18
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
19.2347, 19.54321, 0, 20.234, 20.768, 0, 21.4321, 21.4632, 0, 22.45637, 22.4843,
 0))

        19
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
11.25, 11.65, 0, 13.23, 14.29, 0, 15.68, 15.97, 0, 16.28, 17.34, 0, 18.723, 17.4
678, 0, 19.2347, 19.54321, 0))

        21
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
27.4567, 27.3421, 0, 28.92, 28.63, 0, 29.235, 29.4351, 0, 30.327, 30.678123, 0,
31.6548, 31.6548, 0, 32.7659, 32.903, 0))

        20
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
22.45637, 22.4843, 0, 23.6547, 23.6789, 0, 24.67976, 24.676975, 0, 25.12689, 25.
8747, 0, 26.3412, 26.589, 0, 27.4567, 27.3421, 0))


15 rows selected.

SCOTT@orcl_11gR2> select * from concat_info
  2  /

    FTR_ID START_FTR_ID        SEQ
---------- ------------ ----------
        10           10
        11           10
        12           10
        13           13
        14           13
        15           13
        16           13
        24           22
        23           22
        22           22
        25           22
        18           19
        19           19
        21           19
        20           19

15 rows selected.


-- index and update:
SCOTT@orcl_11gR2> insert into user_sdo_geom_metadata values
  2    ('streets',
  3  	'shape',
  4  	 sdo_dim_array
  5  	   (sdo_dim_element ('X', 0, 50, 0.5),
  6  	    sdo_dim_element ('Y', 0, 50, 0.5),
  7  	    sdo_dim_element ('Z', 0, 50, 0.5)),
  8  	    null)
  9  /

1 row created.

SCOTT@orcl_11gR2> create index streets_idx
  2  on streets (shape)
  3  indextype is mdsys.spatial_index
  4  /

Index created.

SCOTT@orcl_11gR2> create or replace procedure update_concat_info
  2    (p_seq	 in number	       default 0,
  3  	p_ftr_id in number	       default 0,
  4  	p_shape  in mdsys.sdo_geometry default null)
  5  as
  6  begin
  7    if p_seq = 0 then
  8  	 update concat_info set seq = null;
  9  	 update concat_info set seq = 1 where ftr_id = start_ftr_id;
 10  	 for cur_strt_seg in
 11  	   (select a.ftr_id, b.shape
 12  	    from   concat_info a, streets b
 13  	    where  a.ftr_id = b.ftr_id
 14  	    and    a.seq = 1)
 15  	 loop
 16  	   update_concat_info
 17  	     (2,
 18  	      cur_strt_seg.ftr_id,
 19  	      cur_strt_seg.shape);
 20  	 end loop ;
 21    else
 22  	 for cur_nxt_seg in
 23  	   (select a.ftr_id, b.shape
 24  	    from   concat_info a, streets b
 25  	    where  a.ftr_id = b.ftr_id
 26  	    and    a.start_ftr_id = p_ftr_id
 27  	    and    a.ftr_id != p_ftr_id
 28  	    and    a.seq is null
 29  	    and    sdo_touch (b.shape, p_shape) = 'TRUE')
 30  	 loop
 31  	   update concat_info
 32  	   set	  seq = p_seq
 33  	   where  ftr_id = cur_nxt_seg.ftr_id
 34  	   and	  start_ftr_id = p_ftr_id;
 35  	   update_concat_info
 36  	     (p_seq + 1,
 37  	      p_ftr_id,
 38  	      cur_nxt_seg.shape);
 39  	 end loop;
 40    end if;
 41  end update_concat_info;
 42  /

Procedure created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> exec update_concat_info

PL/SQL procedure successfully completed.


-- results:
SCOTT@orcl_11gR2> select * from concat_info
  2  /

    FTR_ID START_FTR_ID        SEQ
---------- ------------ ----------
        10           10          1
        11           10          2
        12           10          3
        13           13          1
        14           13          3
        15           13          2
        16           13          4
        24           22          2
        23           22          3
        22           22          1
        25           22          4
        18           19          2
        19           19          1
        21           19          4
        20           19          3

15 rows selected.

SCOTT@orcl_11gR2> SELECT SDO_AGGR_LRS_CONCAT(MDSYS.SDOAGGRTYPE(shape, 0.005)) SHAPE
  2  FROM   (SELECT /*+ NO_MERGE */ shape
  3  	     FROM   streets a, concat_info b
  4  	     where  a.ftr_id = b.ftr_id
  5  	     and    b.start_ftr_id = 10
  6  	     ORDER  BY seq)
  7  /

SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
1, 1, 0, 2, 2, , 3, 3, , 4, 4, , 5, 5, 0, 6, 6, , 7, 7, 0))


1 row selected.

SCOTT@orcl_11gR2> SELECT SDO_AGGR_LRS_CONCAT(MDSYS.SDOAGGRTYPE(shape, 0.005)) SHAPE
  2  FROM   (SELECT /*+ NO_MERGE */ shape
  3  	     FROM   streets a, concat_info b
  4  	     where  a.ftr_id = b.ftr_id
  5  	     and    b.start_ftr_id = 13
  6  	     ORDER  BY seq)
  7  /

SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
8, 8, 0, 9, 9, , 10, 10, , 11, 11, , 12, 12, , 13, 13, , 14, 14, 0, 15, 15, , 16
, 16, 0))


1 row selected.

SCOTT@orcl_11gR2> SELECT SDO_AGGR_LRS_CONCAT(MDSYS.SDOAGGRTYPE(shape, 0.005)) SHAPE
  2  FROM   (SELECT /*+ NO_MERGE */ shape
  3  	     FROM   streets a, concat_info b
  4  	     where  a.ftr_id = b.ftr_id
  5  	     and    b.start_ftr_id = 19
  6  	     ORDER  BY seq)
  7  /

SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
11.25, 11.65, 0, 13.23, 14.29, , 15.68, 15.97, , 16.28, 17.34, , 18.723, 17.4678
, , 19.2347, 19.54321, , 20.234, 20.768, , 21.4321, 21.4632, , 22.45637, 22.4843
, , 23.6547, 23.6789, , 24.67976, 24.676975, , 25.12689, 25.8747, , 26.3412, 26.
589, , 27.4567, 27.3421, 0, 28.92, 28.63, , 29.235, 29.4351, , 30.327, 30.678123
, , 31.6548, 31.6548, , 32.7659, 32.903, 0))


1 row selected.

SCOTT@orcl_11gR2> SELECT SDO_AGGR_LRS_CONCAT(MDSYS.SDOAGGRTYPE(shape, 0.005)) SHAPE
  2  FROM   (SELECT /*+ NO_MERGE */ shape
  3  	     FROM   streets a, concat_info b
  4  	     where  a.ftr_id = b.ftr_id
  5  	     and    b.start_ftr_id = 22
  6  	     ORDER  BY seq)
  7  /

SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
27.4567, 27.3421, 0, 28.92, 28.63, , 29.235, 29.4351, , 30.234, 30.768, , 31.432
1, 31.4632, , 32.6564, 32.4567, , 33.879, 33.4512, 0, 34.2346, 34.869, , 35.3218
, 35.7389, 0))


1 row selected.

SCOTT@orcl_11gR2>

Re: Merging line geomrtry in a particular order [message #524598 is a reply to message #524567] Sun, 25 September 2011 07:55 Go to previous messageGo to next message
yuko
Messages: 65
Registered: August 2011
Member
Hi Barbara Boehmer,

When I execute the procedure the following error is generated
ORA-29902: error in executing ODCIIndexStart() routine
ORA-13243: specified operator is not supported for 3- or higher-dimensional R-tree
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 416


I'm using Oracle 10gR2.
I searched for this error message. SDO_TOUCH is not supported for 3D geometry in 10gR2.

With 10gR2, is there any other solution for this problem.


Thank you,
Yuko
Re: Merging line geomrtry in a particular order [message #524622 is a reply to message #524598] Sun, 25 September 2011 12:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I only have 11g to test with. So, please try using

sdo_relate (b.shape, p_shape, 'mask=touch') = 'TRUE')

as in the code below, instead of sdo_touch, and see if that works or raises an error. Both work the same on my system.

insert into user_sdo_geom_metadata values
  ('streets',
   'shape',
    sdo_dim_array
      (sdo_dim_element ('X', 0, 50, 0.5),
       sdo_dim_element ('Y', 0, 50, 0.5),
       sdo_dim_element ('Z', 0, 50, 0.5)),
       null)
/
create index streets_idx
on streets (shape)
indextype is mdsys.spatial_index
/
create or replace procedure update_concat_info
  (p_seq    in number             default 0,
   p_ftr_id in number             default 0,
   p_shape  in mdsys.sdo_geometry default null)
as
begin
  if p_seq = 0 then
    update concat_info set seq = null;
    update concat_info set seq = 1 where ftr_id = start_ftr_id;
    for cur_strt_seg in 
      (select a.ftr_id, b.shape 
       from   concat_info a, streets b
       where  a.ftr_id = b.ftr_id 
       and    a.seq = 1)
    loop
      update_concat_info
        (2,
         cur_strt_seg.ftr_id,
         cur_strt_seg.shape);
    end loop ;
  else
    for cur_nxt_seg in 
      (select a.ftr_id, b.shape 
       from   concat_info a, streets b 
       where  a.ftr_id = b.ftr_id
       and    a.start_ftr_id = p_ftr_id
       and    a.ftr_id != p_ftr_id
       and    a.seq is null
       -- and    sdo_touch (b.shape, p_shape) = 'TRUE')
       and    sdo_relate (b.shape, p_shape, 'mask=touch') = 'TRUE')
    loop
      update concat_info 
      set    seq = p_seq 
      where  ftr_id = cur_nxt_seg.ftr_id 
      and    start_ftr_id = p_ftr_id;
      update_concat_info
        (p_seq + 1,
         p_ftr_id,
         cur_nxt_seg.shape);
    end loop;
  end if;
end update_concat_info;
/
show errors
exec update_concat_info

Re: Merging line geomrtry in a particular order [message #524623 is a reply to message #524598] Sun, 25 September 2011 13:03 Go to previous messageGo to next message
yuko
Messages: 65
Registered: August 2011
Member
Hi Barbara Boehmer,

I slightly modified the UPDATE_CONCAT_INFO procedure as I was facing some prblem with SDO_TOUCH function with 3D geometry.
Now instead of using SDO_TOUCH, i'm directly comparing the coordinate values.

Modified Script

 create or replace procedure update_concat_info
      (p_seq	 in number	       default 0,
    	p_ftr_id in number	       default 0,
    	p_shape  in mdsys.sdo_geometry default null)
    as
	 v_crnt_seg_end_x		number;
	 v_crnt_seg_end_y		number;
	 v_nxt_seg_strt_x		number;
	 v_nxt_seg_strt_y		number;
    begin
      if p_seq = 0 then
    	 update concat_info set seq = null;
    	 update concat_info set seq = 1 where ftr_id = start_ftr_id;
   	 for cur_strt_seg in
   	   (select a.ftr_id, b.shape
   	    from   concat_info a, streets b
   	    where  a.ftr_id = b.ftr_id
   	    and    a.seq = 1)
   	 loop
   	   update_concat_info
   	     (2,
   	      cur_strt_seg.ftr_id,
   	      cur_strt_seg.shape);
   	 end loop ;
     else
   	 for cur_nxt_seg in
   	   (select a.ftr_id, b.shape
   	    from   concat_info a, streets b
   	    where  a.ftr_id = b.ftr_id
   	    and    a.start_ftr_id = p_ftr_id
   	    and    a.ftr_id != p_ftr_id
   	    and    a.seq is null)
   	 loop
	   v_crnt_seg_end_x := p_shape.sdo_ordinates(p_shape.sdo_ordinates.count-2);
	   v_crnt_seg_end_y := p_shape.sdo_ordinates(p_shape.sdo_ordinates.count-1);
	   v_nxt_seg_strt_x := cur_nxt_seg.shape.sdo_ordinates(1);
	   v_nxt_seg_strt_y := cur_nxt_seg.shape.sdo_ordinates(2);
	   if (v_crnt_seg_end_x = v_nxt_seg_strt_x and v_crnt_seg_end_y = v_nxt_seg_strt_y) then  
   	    update concat_info 
   	    set	  seq = p_seq
   	    where  ftr_id = cur_nxt_seg.ftr_id
   	    and	  start_ftr_id = p_ftr_id;
   	    update_concat_info
  	      (p_seq + 1,
   	       p_ftr_id,
   	       cur_nxt_seg.shape);
	   end if;	  
   	 end loop;
     end if;
   end update_concat_info;
 /


Result
SQL> select * from concat_info;
    FTR_ID START_FTR_ID        SEQ
---------- ------------ ----------
        10           10          1
        11           10          2
        12           10          3
        13           13          1
        14           13          3
        15           13          2
        16           13          4
        24           22          2
        23           22          3
        22           22          1
        25           22          4
        18           19          2
        19           19          1
        21           19          4
        20           19          3

15 rows selected.

The result is as expected.
I'm testing few more cases, please let me know if see any problem with this modified code.

Thank you, for providing the help.

Regards,
Yuko
Re: Merging line geomrtry in a particular order [message #524624 is a reply to message #524623] Sun, 25 September 2011 13:07 Go to previous messageGo to next message
yuko
Messages: 65
Registered: August 2011
Member
Hi,

I tried SDO_RELATE as well but still facing the same problem.


Regards,
Yuko

Re: Merging line geomrtry in a particular order [message #524625 is a reply to message #524624] Sun, 25 September 2011 13:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Your revised procedure looks good to me.
Re: Merging line geomrtry in a particular order [message #524627 is a reply to message #524625] Sun, 25 September 2011 13:20 Go to previous message
yuko
Messages: 65
Registered: August 2011
Member
Once again thank you.
Previous Topic: SDO_RELATE Performance
Next Topic: Error in passing geometry column to SQL query
Goto Forum:
  


Current Time: Thu Mar 28 05:42:15 CDT 2024