This discussion is archived
8 Replies Latest reply: Apr 4, 2012 4:33 AM by Simon Greener RSS

Line Constructor

don123 Newbie
Currently Being Moderated
Hi

I have point geometries with id and i want to construct line geometry from this. If there are only two points for a line, i can pass start X, start Y, end X, end Y to sdo_ordinate_array.

But if i have intermediate vertices for a given id, how to do this as count of intermediate vertices is uncertain.

id x y
---------------------------------------
1 x1 y1
1 x2 y2
1 x3 y3
1 x4 y4
1 x5 y5
2 x1 y1
2 x2 y2
2 x3 y3

Thanks for your help..
  • 1. Re: Line Constructor
    Luc Van Linden Pro
    Currently Being Moderated
    Hi

    I have tried keeping this into a select statement using analytical functions.

    What you do not seem to have is a sequence number of the points within the line in your data. Hence we will generate one using rownum over rowid.
    I am not 100% sure but this I believe will use the sequence in which the recs were inserted as the sequence nr.

    Might sure be other and maybe better approaches but this seemed to work.

    select id, SDO_AGGR_CONCAT_LINES( segment)
    from
    (
    select id,
    lsn,
    max(lsn) OVER (PARTITION BY ID ORDER BY id ASC) MaxLSN,
    segment
    from
    (
    select id,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY id,rn ASC) LSN,
    sdo_geometry(2002, null, null, SDO_ELEM_INFO_ARRAY (1,2,1), SDO_ORDINATE_ARRAY ( x, y, LEAD(x, 1, 0) OVER (PARTITION BY ID ORDER BY id, rn ASC), LEAD(y, 1, 0) OVER (ORDER BY id, rn ASC) )) segment
    from
    (
    SELECT
    ID,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY rowid ASC) RN,
    x, y,
    X || ',' || Y as coord,
    sdo_geometry(2001, null, SDO_POINT_TYPE(x, y, null), null, null)
    FROM
    linepoints
    )
    ))
    where LSN < maxlsn
    group by ID;

    I Hope this helps.

    Luc
  • 2. Re: Line Constructor
    Simon Greener Journeyer
    Currently Being Moderated
    Here is another way to do this.

    The With CTE is only there to generate some test data. You can get rid of the whole block and replace the last SELECT's from points c with your table name that contains the following data:
    1 x1 y1
     1 x2 y2
     1 x3 y3
     1 x4 y4
     1 x5 y5
     2 x1 y1
     2 x2 y2
     2 x3 y3
    With points As ( 
    SELECT trunc(level / 5) as geom_id,
           row_number() over (partition by trunc(level / 5) order by rownum) as point_id,
           round(dbms_random.value(1,10000),3) as X,
           round(dbms_random.value(1,10000),3) as Y
      FROM dual
    CONNECT BY LEVEL < 30
    )
    SELECT mdsys.sdo_geometry(2002,NULL,NULL,mdsys.sdo_elem_info_array(1,2,1),CAST(MULTISET(SELECT b.COLUMN_VALUE FROM points a,table(mdsys.sdo_ordinate_array(a.x,a.y)) b where a.geom_id = c.geom_id order by a.point_id) as mdsys.sdo_ordinate_array)) as geom
      FROM points c
     GROUP BY c.geom_id;
    -- Results
    --
    GEOM
    ----
    MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(5144.458,3199.919,2251.939,6849.938,6919.348,1134.661,565.804,4384.515,1628.731,6092.055))
    MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(122.862,3006.545,9581.443,9379.387,7659.869,6859.322,7936.498,151.507,926.478,4598.965))
    MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(6373.324,759.294,44.012,9814.083,8125.855,612.249,2699.456,8318.277,1715,7349.652))
    MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(8923.524,6996.764,6518.515,3501.614,6053.453,8277.119,7652.991,9403.059,8728.289,9505.907))
    MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(6828.892,7137.751,3420.856,8590.158,1346.581,6701.412,9105.74,951.904,951.429,1062.246))
    MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1467.562,8577.782,8580.429,8866.923,3676.022,6519.497,8639.309,5164.398))
    Isn't SQL grand!

    regards
    S

    Edited by: Simon Greener on Mar 31, 2012 1:42 PM
  • 3. Re: Line Constructor
    don123 Newbie
    Currently Being Moderated
    Thanks to both..

    Simon,

    I am bit confused with the table alias in your sql, can you rewrite sql according to my table structure.

    table name with point geometries=LINEPOINTS
    geometry column in table=GEOMETRY
    POINTID is serial number for point geometry, FEATURE_ID to be used for grouping point geometries into line geometry.


    FEATURE_ID POINTID
    ===============
    398639 11675
    398639 11676
    398639 11677
    398639 11678
    398639 11679
    398639 11680
    398639 11681
    398639 11682
    2215595 11683
    2215595 11684
    2215595 11685
    2215595 11686


    Thanks again
  • 4. Re: Line Constructor
    Simon Greener Journeyer
    Currently Being Moderated
    908275 (What's your name???),

    What alias are you taking about? The "points" in "With points as" is the virtual table name constructed by the Common Table Expression (CTE) - it is NOT an alias. The only alias is "c". Not that confusing.

    So, I am confused by your question.

    In the initial request you gave us a bunch of points "id,x,y" but no table name.

    Now you give me a table name - linepoints - with a geometry column - geometry - which I suppose is the destination for the created linestrings.

    But a problem springs to mind: Why are the IDs in the original posting 1 and 2 - which I assume is some sort of aggregate - different from the feature_id/pointids in this new post (11675 etc)? What is the link between the original id,x,y data (what is the table name?) and the data identified by feature_id/pointid.

    Now, if I assume that you id,x,y is actually supposed to be pointid,x,y in a table called rawpoints then the SQL could be written as something like this (not sure if this is correct as I have no real table names and data to run with):
    SELECT mdsys.sdo_geometry(2002,NULL,NULL,mdsys.sdo_elem_info_array(1,2,1),CAST(MULTISET(SELECT b.COLUMN_VALUE FROM rawpoints a,table(mdsys.sdo_ordinate_array(a.x,a.y)) b where a.pointid = c.pointid order by a.pointid) as mdsys.sdo_ordinate_array)) as geom
      FROM linepoints c
     GROUP BY c.feature_id;
    Note: For those of us who endeavour to help posters on this forum it does help to have properly defined data sources.

    regards
    Simon
  • 5. Re: Line Constructor
    don123 Newbie
    Currently Being Moderated
    Simon,

    sorry for the confusion, please ignore first posting...

    i have point geometry table, i need to create new line geometry table by using point geometries.

    point geometry table name: linepoints
    geometry column: geometry
    points serial (sequence) number: pointid
    column to group points as line: feature_id


    regards
    syed masood
  • 6. Re: Line Constructor
    Simon Greener Journeyer
    Currently Being Moderated
    Syed,

    OK, here is an example that I hope reflects your situation.

    Firstly, I will create a table called linepoints that I think contains data like yours:
    create table linepoints as
    SELECT case when trunc(level / 7) = 0 then 398639 else 2215595 end as feature_id,
           row_number() over (partition by 1 order by rownum) as point_id,
           sdo_geometry(2001,null,
                        sdo_point_type(round(dbms_random.value(1,10000),3),
                                       round(dbms_random.value(1,10000),3),
                                       NULL),NULL,NULL) as pointGeom
      FROM dual
    CONNECT BY LEVEL < 13;
    -- Result
    table LINEPOINTS created.
    This is what linepoints contains:
    select * from linepoints;
    -- Results
    FEATURE_ID POINT_ID POINTGEOM
    ---------- -------- ------------------------------------------------------------------------------------
    398639     1        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(512.505,9909.19,NULL),NULL,NULL)
    398639     2        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(701.488,8150.579,NULL),NULL,NULL)
    398639     3        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(753.581,2449.058,NULL),NULL,NULL)
    398639     4        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(7160.47,6238.475,NULL),NULL,NULL)
    398639     5        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(4550.681,1422.242,NULL),NULL,NULL)
    398639     6        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(1972.044,6790.043,NULL),NULL,NULL)
    2215595    7        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(1204.709,1973.255,NULL),NULL,NULL)
    2215595    8        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(7485.635,2439.01,NULL),NULL,NULL)
    2215595    9        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(5005.396,5022.274,NULL),NULL,NULL)
    2215595    10       MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(1180.856,6256.96,NULL),NULL,NULL)
    2215595    11       MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(6425.442,7360.927,NULL),NULL,NULL)
    2215595    12       MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(4200.348,5369.366,NULL),NULL,NULL)
    
     12 rows selected
    Secondly, given this table linepoints here is how I would create the linear (2002) sdo_geometry objects:
    SELECT c.feature_id,
           mdsys.sdo_geometry(2002,NULL,NULL,
                              mdsys.sdo_elem_info_array(1,2,1),
                              CAST(MULTISET(SELECT b.COLUMN_VALUE FROM linepoints a,table(mdsys.sdo_ordinate_array(a.pointGeom.sdo_point.x,a.pointGeom.sdo_point.y)) b where a.feature_id = c.feature_id order by a.point_id) as mdsys.sdo_ordinate_array))
           as geom
      FROM linepoints c
     GROUP BY c.feature_id;
    -- Results
    FEATURE_ID GEOM
    ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    2215595    MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1204.709,1973.255,7485.635,2439.01,5005.396,5022.274,1180.856,6256.96,6425.442,7360.927,4200.348,5369.366))
    398639     MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(512.505,9909.19,701.488,8150.579,753.581,2449.058,7160.47,6238.475,4550.681,1422.242,1972.044,6790.043))
    If this is accurate and gives you the result you want, please mark my answer as being correct.

    regard
    Simon
  • 7. Re: Line Constructor
    don123 Newbie
    Currently Being Moderated
    Hi Simon,

    Thanks a lot for the help..I am marking this as correct answer. I declared a cursor by using your SQL and generated line geometry.

    (1) In your SQL, are you casting SDO_POINT as SDO_ORDINATE_ARRAY ??

    (2) Can I use sdo_util.getvertices and convert as SDO_ORDINATE_ARRAY ??


    thanks
    syed
  • 8. Re: Line Constructor
    Simon Greener Journeyer
    Currently Being Moderated
    Syed,
    (1) In your SQL, are you casting SDO_POINT as SDO_ORDINATE_ARRAY ??
    No, I am casting the X and Y ordinates of the SDO_POINT type as an SDO_ORDINATE_ARRAY so that the CAST(COLLECT()) operates on SDO_ORDINATE_ARRAYs and "appends" them together.

    If you look at my earlier posting I don't use the sdo_geometry's sdo_point type rather I just use two X and Y columns from a CTW table.
    (2) Can I use sdo_util.getvertices and convert as SDO_ORDINATE_ARRAY ??
    Yes, of course you can. Here is the last SELECT in my answer redone...
    SELECT c.feature_id,
           mdsys.sdo_geometry(2002,NULL,NULL,
                              mdsys.sdo_elem_info_array(1,2,1),
                              CAST(MULTISET(SELECT b.COLUMN_VALUE 
                                              FROM linepoints a,
                                                   table(sdo_util.getVertices(a.pointGeom)) v,
                                                   table(mdsys.sdo_ordinate_array(v.x,v.y)) b 
                                             WHERE a.feature_id = c.feature_id 
                                             ORDER BY a.point_id) 
                                  as mdsys.sdo_ordinate_array))
           as geom
      FROM linepoints c
     GROUP BY c.feature_id;
    -- Results
    FEATURE_ID GEOM
    ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    2215595    MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1204.709,1973.255,7485.635,2439.01,5005.396,5022.274,1180.856,6256.96,6425.442,7360.927,4200.348,5369.366))
    398639     MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(512.505,9909.19,701.488,8150.579,753.581,2449.058,7160.47,6238.475,4550.681,1422.242,1972.044,6790.043))
    HTH

    regards
    S

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points