1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Merge Geometry Lines

Discussion in 'SQL PL/SQL' started by dkoracle, Jun 28, 2012.

  1. dkoracle

    dkoracle Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi all,

    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,
    7997311.301837, 7201013.165354, 0)));



    Can you please help me.
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Please post yoy table DDl and insert script for test data.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It's in the original post.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    oops..

    But what I would like to know is the elements of User datatype SDO_GEOMETRY.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    SQL> DESC sdo_geometry
     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------
     SDO_GTYPE                                          NUMBER
     SDO_SRID                                           NUMBER
     SDO_POINT                                          MDSYS.SDO_POINT_TYPE
     SDO_ELEM_INFO                                      MDSYS.SDO_ELEM_INFO_ARRAY
     SDO_ORDINATES                                      MDSYS.SDO_ORDINATE_ARRAY
    METHOD
    ------
     MEMBER FUNCTION GET_GTYPE RETURNS NUMBER
    METHOD
    ------
     MEMBER FUNCTION GET_DIMS RETURNS NUMBER
    METHOD
    ------
     MEMBER FUNCTION GET_LRS_DIM RETURNS NUMBER
    METHOD
    ------
     MEMBER FUNCTION GET_WKB RETURNS BLOB
    METHOD
    ------
     MEMBER FUNCTION GET_WKT RETURNS CLOB
    METHOD
    ------
     MEMBER FUNCTION ST_COORDDIM RETURNS NUMBER
    METHOD
    ------
     MEMBER FUNCTION ST_ISVALID RETURNS NUMBER
    METHOD
    ------
     FINAL CONSTRUCTOR FUNCTION SDO_GEOMETRY RETURNS SELF AS RESULT
     Argument Name                  TYPE                    IN/OUT DEFAULT?
     ------------------------------ ----------------------- ------ --------
     WKT                            CLOB                    IN
     SRID                           NUMBER                  IN     DEFAULT
    METHOD
    ------
     FINAL CONSTRUCTOR FUNCTION SDO_GEOMETRY RETURNS SELF AS RESULT
     Argument Name                  TYPE                    IN/OUT DEFAULT?
     ------------------------------ ----------------------- ------ --------
     WKT                            VARCHAR2                IN
     SRID                           NUMBER                  IN     DEFAULT
    METHOD
    ------
     FINAL CONSTRUCTOR FUNCTION SDO_GEOMETRY RETURNS SELF AS RESULT
     Argument Name                  TYPE                    IN/OUT DEFAULT?
     ------------------------------ ----------------------- ------ --------
     WKB                            BLOB                    IN
     SRID                           NUMBER                  IN     DEFAULT