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  |
 |
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  |
 |
Barbara Boehmer
Messages: 9101
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 |
|
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 |
|
Barbara Boehmer
Messages: 9101
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 #524622 is a reply to message #524598] |
Sun, 25 September 2011 12:55 |
|
Barbara Boehmer
Messages: 9101
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 |
|
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
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Dec 22 02:42:12 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
