6 Replies Latest reply on Mar 11, 2014 5:53 AM by don123

    one to many relation

    don123

      Hi, I have two tables one with point geometry and another table without any geometry.

       

      SQL> select idnumber, geometry from arp;

       

      IDNUMBER          GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
      --------------------------------------------------------------------------------

      5cOx                   SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-87.907389, 41.9793333, NULL), NULL, NULL)

       

       

      SQL> select idnumber, temp, month from temperature;

       

      IDNUM       TEMP MONTH

      ----- ---------- ----------

      5cOx          32      Jan

      5cOx          28      Feb

      5cOx          29      March

      5cOx          31     April

      5cOx          34     May

      5cOx          35    June

       

       

      SQL> select a.idnumber, a.geometry, b.temp, b.month from arp a, temperature b where a.idnumber=b.idnumber;

       

       

      IDNUMBER     GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)                    TEMP            MONTH

      5cOx              SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-87.907389, 41.9793333, NULL), NULL, NULL)                            32                       Jan

      5cOx              SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-87.907389, 41.9793333, NULL), NULL, NULL)                            28                       Feb

      5cOx              SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-87.907389, 41.9793333, NULL), NULL, NULL)                            29                      March

      5cOx              SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-87.907389, 41.9793333, NULL), NULL, NULL)                            31                       April

      5cOx              SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-87.907389, 41.9793333, NULL), NULL, NULL)                            34                       May

      5cOx              SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-87.907389, 41.9793333, NULL), NULL, NULL)                            35                       June

       

       

      Please note that geometry also selected six times.

      When i try to display this in Map view in sql developer, it has displaed six geometries at same location.

      How to handle this ? Is there any problem in design ?

       

      Thanks

        • 1. Re: one to many relation
          John O'Toole

          Of course the geometry will return 6 times from your join query as the each row in the temperature table has the same id.  There nothing special going on here with the spatial data.  If you had "name" in the arp table instead of geometry, and you ran:

          select a.idnumber, a.name, b.temp, b.month from arp a, temperature b where a.idnumber=b.idnumber;

          Then you'll obviously get the same name displayed 6 times.

           

          What do you expect your join query to return?

           

          John

          • 2. Re: one to many relation
            don123

            Thanks John

             

            The join query displays six geometries to show temperature of six months.

            I want to display single geometry and temeperature data for six months.

            I want to know how to handle spatial and temporal situation.

             

            regards

             


            • 3. Re: one to many relation
              Barbara Boehmer

              You can use PIVOT, as demonstrated below.

               

              SCOTT@orcl12c> select idnumber, geometry from arp

                2  /

               

              IDNUMBER

              --------

              GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

              -------------------------------------------------------------------------------------

              5cOx

              SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-87.907389, 41.9793333, NULL), NULL, NULL)

               

               

              1 row selected.

               

              SCOTT@orcl12c> select idnumber, temp, month from temperature

                2  /

               

              IDNUMBER       TEMP MONTH

              -------- ---------- -----

              5cOx             32 Jan

              5cOx             28 Feb

              5cOx             29 March

              5cOx             31 April

              5cOx             34 May

              5cOx             35 June

               

              6 rows selected.

               

              SCOTT@orcl12c> SELECT *

                2  FROM   temperature

                3  PIVOT  (SUM (temp)

                4           FOR month IN

                5             ('Jan', 'Feb', 'March', 'April', 'May', 'June'))

                6  /

               

              IDNUMBER      'Jan'      'Feb'    'March'    'April'      'May'     'June'

              -------- ---------- ---------- ---------- ---------- ---------- ----------

              5cOx             32         28         29         31         34         35

               

              1 row selected.

               

              SCOTT@orcl12c> SELECT b.*, a.geometry

                2  FROM   arp a,

                3          (SELECT *

                4           FROM   temperature

                5           PIVOT  (SUM (temp)

                6               FOR month IN

                7                 ('Jan', 'Feb', 'March', 'April', 'May', 'June'))) b

                8  WHERE  a.idnumber = b.idnumber

                9  /

               

              IDNUMBER      'Jan'      'Feb'    'March'    'April'      'May'     'June'

              -------- ---------- ---------- ---------- ---------- ---------- ----------

              GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

              -------------------------------------------------------------------------------------

              5cOx             32         28         29         31         34         35

              SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-87.907389, 41.9793333, NULL), NULL, NULL)

               

               

              1 row selected.

              • 4. Re: one to many relation
                don123

                HI, Thanks for your help, It works fine for me.

                 

                I think it is mandatory to provide aggregate functions like SUM and AVG in PIVOT.

                How this can be improved to get individual values ?

                 

                regards

                • 5. Re: one to many relation
                  Barbara Boehmer

                  You are correct about pivot requiring an aggregate function, such as sum or avg or max or min.  If you have more than one temperature per month and there is no other column that can be combined with the idnumber to make a unique key, then you can use something like row_number to create a value that you can use for this.  The rest depends on how you want it displayed.  I have demonstrated two different formats below.

                   

                   

                  SCOTT@orcl12c> select * from arp

                    2  /

                   

                  IDNUMBER

                  --------

                  GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

                  -------------------------------------------------------------------------------------

                  5cOx

                  SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-87.907389, 41.9793333, NULL), NULL, NULL)

                   

                   

                  1 row selected.

                   

                  SCOTT@orcl12c> select * from temperature

                    2  /

                   

                  IDNUMBER       TEMP MONTH

                  -------- ---------- -----

                  5cOx             32 Jan

                  5cOx             33 Jan

                  5cOx             28 Feb

                  5cOx             27 Feb

                  5cOx             29 March

                  5cOx             31 April

                  5cOx             34 May

                  5cOx             35 June

                  5cOx             36 June

                  5cOx             37 June

                   

                  10 rows selected.

                   

                  SCOTT@orcl12c> select idnumber, temp, month,

                    2          row_number () over (partition by idnumber, month order by temp) rn

                    3  from   temperature

                    4  order  by idnumber, month, rn

                    5  /

                   

                  IDNUMBER       TEMP MONTH         RN

                  -------- ---------- ----- ----------

                  5cOx             31 April          1

                  5cOx             27 Feb            1

                  5cOx             28 Feb            2

                  5cOx             32 Jan            1

                  5cOx             33 Jan            2

                  5cOx             35 June           1

                  5cOx             36 June           2

                  5cOx             37 June           3

                  5cOx             29 March          1

                  5cOx             34 May            1

                   

                  10 rows selected.

                   

                  SCOTT@orcl12c> SELECT *

                    2  FROM   (select idnumber, temp, month,

                    3              row_number () over (partition by idnumber, month order by temp) rn

                    4           from   temperature)

                    5  PIVOT  (SUM (temp)

                    6           FOR month IN

                    7             ('Jan', 'Feb', 'March', 'April', 'May', 'June'))

                    8  ORDER  BY idnumber, rn

                    9  /

                   

                  IDNUMBER         RN      'Jan'      'Feb'    'March'    'April'      'May'     'June'

                  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

                  5cOx              1         32         27         29         31         34         35

                  5cOx              2         33         28                                          36

                  5cOx              3                                                                37

                   

                  3 rows selected.

                   

                  SCOTT@orcl12c> SELECT b.*, a.geometry

                    2  FROM   arp a,

                    3          (SELECT *

                    4           FROM   (select idnumber, temp, month,

                    5                  row_number () over (partition by idnumber, month order by temp) rn

                    6               from   temperature)

                    7           PIVOT  (SUM (temp)

                    8               FOR month IN

                    9                 ('Jan', 'Feb', 'March', 'April', 'May', 'June'))) b

                  10  WHERE  a.idnumber = b.idnumber

                  11  ORDER  BY b.idnumber, rn

                  12  /

                   

                  IDNUMBER         RN      'Jan'      'Feb'    'March'    'April'      'May'     'June'

                  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

                  GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

                  -------------------------------------------------------------------------------------

                  5cOx              1         32         27         29         31         34         35

                  SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-87.907389, 41.9793333, NULL), NULL, NULL)

                   

                  5cOx              2         33         28                                          36

                  SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-87.907389, 41.9793333, NULL), NULL, NULL)

                   

                  5cOx              3                                                                37

                  SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-87.907389, 41.9793333, NULL), NULL, NULL)

                   

                   

                  3 rows selected.

                   

                  SCOTT@orcl12c> SELECT *

                    2  FROM   (select idnumber, temp, month,

                    3              row_number () over (partition by idnumber, month order by temp) rn

                    4           from   temperature)

                    5  PIVOT  (SUM (temp)

                    6           FOR (month, rn) IN

                    7             (('Jan',   1) AS jan1,

                    8          ('Jan',   2) AS jan2,

                    9          ('Jan',   3) AS jan3,

                  10          ('Feb',   1) AS feb1,

                  11          ('Feb',   2) AS feb2,

                  12          ('Feb',   3) AS feb3,

                  13          ('March', 1) AS march1,

                  14          ('March', 2) AS march2,

                  15          ('March', 3) AS march3,

                  16          ('April', 1) AS april1,

                  17          ('April', 2) AS april2,

                  18          ('April', 3) AS april3,

                  19          ('May',   1) AS may1,

                  20          ('May',   2) AS may2,

                  21          ('May',   3) AS may3,

                  22          ('June',  1) AS jun1,

                  23          ('June',  2) AS jun2,

                  24          ('June',  3) AS jun3))

                  25  ORDER  BY idnumber

                  26  /

                   

                  IDNUMBER       JAN1       JAN2       JAN3       FEB1       FEB2       FEB3     MARCH1

                  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

                      MARCH2     MARCH3     APRIL1     APRIL2     APRIL3       MAY1       MAY2

                  ---------- ---------- ---------- ---------- ---------- ---------- ----------

                        MAY3       JUN1       JUN2       JUN3

                  ---------- ---------- ---------- ----------

                  5cOx             32         33                    27         28                    29

                                                31                               34

                                     35         36         37

                   

                   

                  1 row selected.

                   

                  SCOTT@orcl12c> SELECT b.*, a.geometry

                    2  FROM   arp a,

                    3          (SELECT *

                    4           FROM   (select idnumber, temp, month,

                    5                  row_number () over (partition by idnumber, month order by temp) rn

                    6               from   temperature)

                    7           PIVOT  (SUM (temp)

                    8               FOR (month, rn) IN

                    9                 (('Jan',   1) AS jan1,

                  10              ('Jan',   2) AS jan2,

                  11              ('Jan',   3) AS jan3,

                  12              ('Feb',   1) AS feb1,

                  13              ('Feb',   2) AS feb2,

                  14              ('Feb',   3) AS feb3,

                  15              ('March', 1) AS march1,

                  16              ('March', 2) AS march2,

                  17              ('March', 3) AS march3,

                  18              ('April', 1) AS april1,

                  19              ('April', 2) AS april2,

                  20              ('April', 3) AS april3,

                  21              ('May',   1) AS may1,

                  22              ('May',   2) AS may2,

                  23              ('May',   3) AS may3,

                  24              ('June',  1) AS jun1,

                  25              ('June',  2) AS jun2,

                  26              ('June',  3) AS jun3))) b

                  27  WHERE  a.idnumber = b.idnumber

                  28  ORDER  BY b.idnumber

                  29  /

                   

                  IDNUMBER       JAN1       JAN2       JAN3       FEB1       FEB2       FEB3     MARCH1

                  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

                      MARCH2     MARCH3     APRIL1     APRIL2     APRIL3       MAY1       MAY2

                  ---------- ---------- ---------- ---------- ---------- ---------- ----------

                        MAY3       JUN1       JUN2       JUN3

                  ---------- ---------- ---------- ----------

                  GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

                  -------------------------------------------------------------------------------------

                  5cOx             32         33                    27         28                    29

                                                31                               34

                                     35         36         37

                  SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-87.907389, 41.9793333, NULL), NULL, NULL)

                   

                   

                  1 row selected.

                  • 6. Re: one to many relation
                    don123

                    Thanks a lot for detailed examples..