8 Replies Latest reply: Apr 4, 2012 6:33 AM by Simon Greener RSS

    Line Constructor

    don123
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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