Home » Server Options » Spatial » merge geometry lines (Oracle10g)
merge geometry lines [message #559030] |
Thu, 28 June 2012 01:46 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/0f53fbf851759bc7bff4a94f0a4ecc7a?s=64&d=mm&r=g) |
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)));
|
|
|
Re: merge geometry lines [message #559080 is a reply to message #559030] |
Thu, 28 June 2012 09:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
_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.
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:24:45 CDT 2024
|