Home » Server Options » Spatial » merge geometry lines (Oracle10g)
merge geometry lines [message #559030] Thu, 28 June 2012 01:46 Go to next message
naveendara
Messages: 11
Registered: June 2012
Location: Hyderabad
Junior Member
Hi

I have geometry data in TABLE TEST_GEOM,I to merg the spatial data like

If Cordinates(Xen,Yen ) of the line end points equal with
Cordinates(Xsn,Ysn) of the other line first points then i need to merge these two lines
and so on.......

Table description:-

CREATE TABLE TEST_GEOM(ID NUMBER(5),
GEOM SDO_GEOMETRY
);

exp:- I have 5 lines

INSERT INTO TEST_GEOM VALUES(1,SDO_GEOMETRY(3002,4326,NULL,SDO_ELEM_INFO_ARRAY(1, 2, 1),SDO_ORDINATE_ARRAY(7996368.769029, 7202906.335958, 0, 7997382.664042, 7200881.448819, 0)));
INSERT INTO TEST_GEOM VALUES(2,SDO_GEOMETRY(3002,4326,NULL,SDO_ELEM_INFO_ARRAY(1, 2, 1),SDO_ORDINATE_ARRAY(7998502.900262, 7198815.871391, 0, 7997311.301837, 7201013.165354, 0)));
INSERT INTO TEST_GEOM VALUES(3,SDO_GEOMETRY(3002,4326,NULL,SDO_ELEM_INFO_ARRAY(1, 2, 1),SDO_ORDINATE_ARRAY(7997382.664042, 7200881.448819, 0, 7997525.572178, 7200617.771654, 0)));
INSERT INTO TEST_GEOM VALUES(4,SDO_GEOMETRY(3002,4326,NULL,SDO_ELEM_INFO_ARRAY(1, 2, 1),SDO_ORDINATE_ARRAY(7998448.685039, 7198912.944882, 0, 7998502.900262, 7198815.871391, 0)));
INSERT INTO TEST_GEOM VALUES(5,SDO_GEOMETRY(3002,4326,NULL,SDO_ELEM_INFO_ARRAY(1, 2, 1),SDO_ORDINATE_ARRAY(7997525.57217, 7200617.771654, 0, 7996451.43832, 7202772.834646, 0)));

Required output:-

INSERT INTO TEST_GEOM VALUES(1,SDO_GEOMETRY(3002,4326,NULL,SDO_ELEM_INFO_ARRAY(1, 2, 1),SDO_ORDINATE_ARRAY(7996368.769029, 7202906.335958, 0,
7997382.664042, 7200881.448819, 0,
7997525.572178, 7200617.771654, 0,
7996451.43832, 7202772.834646, 0)));

INSERT INTO TEST_GEOM VALUES(2,SDO_GEOMETRY(3002,4326,NULL,SDO_ELEM_INFO_ARRAY(1, 2, 1),SDO_ORDINATE_ARRAY(7998448.685039, 7198912.944882, 0,
7998502.900262, 7198815.871391, 0,
Can you please help me. 7997311.301837, 7201013.165354, 0)));

Report message to a moderator

Re: merge geometry lines [message #559080 is a reply to message #559030] Thu, 28 June 2012 09:01 Go to previous message
_jum
Messages: 577
Registered: February 2008
Senior Member
The CRS 4326 is geodetic one:
Horizontal component of 3D system. Used by the GPS satellite navigation system and for NATO military geodetic surveying, lat/lon = noth/east.
So the values for lat und lon don't fit real coordinates and a spatial union will fail.
Here a first example that will work:

TRUNCATE TABLE test_geom;

INSERT INTO TEST_GEOM VALUES(1,SDO_GEOMETRY(3002,4326,NULL,SDO_ELEM_INFO_ARRAY(1, 2, 1),SDO_ORDINATE_ARRAY(50.0, 10.0, 0, 50.1, 10.0, 0)));
INSERT INTO TEST_GEOM VALUES(2,SDO_GEOMETRY(3002,4326,NULL,SDO_ELEM_INFO_ARRAY(1, 2, 1),SDO_ORDINATE_ARRAY(50.0, 10.0, 0, 50.0, 10.1, 0)));
INSERT INTO TEST_GEOM VALUES(3,SDO_GEOMETRY(3002,4326,NULL,SDO_ELEM_INFO_ARRAY(1, 2, 1),SDO_ORDINATE_ARRAY(50.2, 10.0, 0, 50.3, 10.1, 0)));
INSERT INTO TEST_GEOM VALUES(4,SDO_GEOMETRY(3002,4326,NULL,SDO_ELEM_INFO_ARRAY(1, 2, 1),SDO_ORDINATE_ARRAY(50.0,  9.9, 0, 50.1,  9.9, 0)));
INSERT INTO TEST_GEOM VALUES(5,SDO_GEOMETRY(3002,4326,NULL,SDO_ELEM_INFO_ARRAY(1, 2, 1),SDO_ORDINATE_ARRAY(50.2, 10.1, 0, 50.2, 10.0, 0)));

--find lines with distance 0
SELECT t1.id id1, 
       t2.id id2,
       SDO_GEOM.SDO_DISTANCE(t1.geom, t2.geom, 0.0001) dist 
  FROM test_geom t1, test_geom t2
 WHERE t1.id<t2.id
 ORDER BY 1,2;

       ID1        ID2       DIST
---------- ---------- ----------
         1          2          0
         1          3 10950,5749
         1          4 11119,5051
         1          5 10950,5748
         2          3 21901,1485
         2          4 11119,5051
         2          5 21894,3761
         3          4 15607,5388
         3          5          0
         4          5 15607,5388

10 rows selected.

--union the matching lines
SELECT t1.id id1, 
       t2.id id2,
       -- t1.geom,
       -- t2.geom,
       SDO_GEOM.SDO_UNION(t1.geom, t2.geom, 0.0001) neugeo 
  FROM test_geom t1, test_geom t2
 WHERE t1.id<t2.id
   AND SDO_GEOM.SDO_DISTANCE(t1.geom, t2.geom, 0.0001)=0;

       ID1        ID2    NEUGEOM
---------- ---------- -----------------------------------------------------------------
         1          2  (3002; 4326;  (1; 2; 1; (50  ; 10.1; 0;   50; 10; 0; 50.1;   10; 0)
         3          5  (3002; 4326;  (1; 2; 1; (50.2; 10.1; 0; 50.2; 10; 0; 50.3; 10.1; 0)

2 rows selected.


But note, that the spatial extension functions (SDO_GEOM.SDO_DISTANCE and SDO_GEOM.SDO_UNION) have to be licensed.

Report message to a moderator

Previous Topic: SDO Geometry Query
Next Topic: SDO_RELATE
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 23 03:31:53 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.