4 Replies Latest reply: Nov 28, 2008 8:59 AM by 666352 RSS

    Producing the Mandelbrot set in ascii art?

    523861
      just read one of today's daily WTF's (http://thedailywtf.com/Articles/Stupid-Coding-Tricks-The-TSQL-Madlebrot.aspx)
      (someone wrote a TSQL statement to "graph" the mandlebrot set)

      and wanted to reproduce the TSQL code in oracle syntax.

      I'm having troubles here - the idea is the following "with" clause produces all the cells (i) with their associated coordinates

      but it doesn't appear to be working correctly. I havn't got 11g otherwise I'd just use the pivot command.

      instead of manually attempting to pivot by referencing every field, I figured I just needed to concatenate every X value
      but although it produces a pretty pattern I can't get it to produce the mandelbrot fractal

      anyone with more experience want to give this a go? surely someone with a better understanding of the model clause could whip it up.

      with t as (select ix, iy, substr(' .,,,-----++++%%%%@@@@### ',mod(max(i),26),1) I
      from (
      select ix, iy, x*x-y*y+(x-0.031) CX, y*x*2+(y-0.031) CY, x, y, rownum-1 i
        from (select -2.2+ rownum*0.031 x, rownum ix
                from dual
            connect by rownum <= 100) xgen,
            (select -1.5+rownum*0.031 y, rownum iy
               from dual
            connect by rownum <=100) ygen
            where x-0.031*x-0.031 + y-0.031*y-0.031 < 16
             connect by rownum <= 100)
             group by ix, iy)
      select replace(ltrim(max(sys_connect_by_path(i,'|')) keep (dense_rank last partition by ix),'|'),'|')
         from (select ix, i, row_number() over (partition by ix order by iy) as rn
                from t)
                connect by ix = prior ix and rn = prior rn + 1
                start with rn = 1
                group by ix
                
        • 1. Re: Producing the Mandelbrot set in ascii art?
          Sentinel
          Well the only way I could think to do this was using the model clause to create the Z dimension. Perhaps someone else could come up with a more elegant solution, but this one apears to give the right results:
          with ord as (select rownum - 1 xyz from dual connect by rownum <= 100)
          , xgen as (
            select -2.2 + xyz*0.031 cx, xyz ix
              from ord)
          , ygen as (
            select -1.5 + xyz*0.031 cy, xyz iy
              from ord)
          , z as (
            select ix, iy, i
              from xgen, ygen
             model partition by (ix, iy)
                   dimension by (0 i)
                   measures (cx, cy
                           , cx x
                           , cy y
                   )
                   ignore nav
                   rules iterate (100) 
                           until (X[iteration_number] * X[iteration_number] + 
                                  Y[iteration_number] * Y[iteration_number] > 16) (
                     cx[iteration_number] = cx[cv()]+cx[cv()-1]
                   , cy[iteration_number] = cy[cv()]+cy[cv()-1]
                   , x[iteration_number] = cx[cv()]+x[cv()-1]*x[cv()-1]-y[cv()-1]*y[cv()-1]
                   , y[iteration_number] = cy[cv()]+y[cv()-1]*x[cv()-1]*2
                   )
          )
          select replace(ltrim(max(sys_connect_by_path(i,'|')) keep (dense_rank last partition by ix),'|'),'|')
             from (select ix, iy, substr(' .,,,-----++++%%%%@@@@### ',least(max(i),26),1) i from z group by ix, iy)
                    connect by iy = prior iy and ix = prior ix + 1
                    start with ix = 1
                    group by iy;
                    
          • 3. Re: Producing the Mandelbrot set in ascii art?
            Sentinel
            Thanks Rob,

            It looked like an interesting problem, and it provided me with a good test case to start leaning about the model clause. Can't say as though I understand the mathematics of the mandelbrot set yet, but at least I have a better perspective on it than I did yesterday ;)
            • 4. Re: Producing the Mandelbrot set in ascii art?
              666352
              Hi,

              Another solution, I substitue the last connect by by model clause.( for performance model clause is better than connect by ) :)
              Solution
              WITH ORD AS ( SELECT ROWNUM -1 XYZ FROM DUAL CONNECT BY ROWNUM<=100)
              , XGEN AS (
                SELECT -2.2 + XYZ*0.031 CX, XYZ IX
                  FROM ORD)
              , YGEN AS (
                SELECT -1.5 + XYZ*0.031 CY, XYZ IY
                  FROM ORD)
              , Z AS (
                SELECT IX, IY,I
                  FROM XGEN, YGEN
                 MODEL PARTITION BY (IX, IY)
                       DIMENSION BY (0 I)
                       MEASURES (CX, CY
                               , CX X
                               , CY Y )
                       IGNORE NAV
                       RULES ITERATE (100)
                               UNTIL (X[ITERATION_NUMBER] * X[ITERATION_NUMBER] +
                                     Y[ITERATION_NUMBER] * Y[ITERATION_NUMBER] > 16) (
                         CX[ITERATION_NUMBER] = CX[CV()]+CX[CV()-1]
                       , CY[ITERATION_NUMBER] = CY[CV()]+CY[CV()-1]
                       , X[ITERATION_NUMBER] = CX[CV()]+X[CV()-1]*X[CV()-1]-Y[CV()-1]*Y[CV()-1]
                       , Y[ITERATION_NUMBER] = CY[CV()]+Y[CV()-1]*X[CV()-1]*2
                       )
              )
              SELECT    string
              FROM
              (SELECT   IY,IX,
                        SUBSTR (' .,,,-----++++%%%%@@@@### ', LEAST (MAX(I), 26), 1) I
                   FROM Z
               GROUP BY IY ,IX)
                   MODEL
                    RETURN UPDATED ROWS
                   PARTITION BY ( IY )
                    DIMENSION BY ( ROW_NUMBER() OVER (PARTITION BY IY ORDER BY  IX ASC) AS POSITION )
                    MEASURES     ( CAST( I AS VARCHAR2(100)) AS string )IGNORE NAV
                    RULES
                     UPSERT
                     ITERATE( 99)
                       UNTIL ( PRESENTV(string[ITERATION_NUMBER+2],1,0) = 0)
                    ( string[0] =string[0] ||  string[ ITERATION_NUMBER+1] )
                 ORDER BY IY
              Demo
              SQL> WITH ORD AS ( SELECT ROWNUM -1 XYZ FROM DUAL CONNECT BY ROWNUM<=100)
                2  , XGEN AS (
                3    SELECT -2.2 + XYZ*0.031 CX, XYZ IX
                4      FROM ORD)
                5  , YGEN AS (
                6    SELECT -1.5 + XYZ*0.031 CY, XYZ IY
                7      FROM ORD)
                8  , Z AS (
                9    SELECT IX, IY,I
               10      FROM XGEN, YGEN
               11     MODEL PARTITION BY (IX, IY)
               12           DIMENSION BY (0 I)
               13           MEASURES (CX, CY
               14                   , CX X
               15                   , CY Y )
               16           IGNORE NAV
               17           RULES ITERATE (100)
               18                   UNTIL (X[ITERATION_NUMBER] * X[ITERATION_NUMBER] +
               19                         Y[ITERATION_NUMBER] * Y[ITERATION_NUMBER] > 16) (
               20             CX[ITERATION_NUMBER] = CX[CV()]+CX[CV()-1]
               21           , CY[ITERATION_NUMBER] = CY[CV()]+CY[CV()-1]
               22           , X[ITERATION_NUMBER] = CX[CV()]+X[CV()-1]*X[CV()-1]-Y[CV()-1]*Y[CV()-1]
               23           , Y[ITERATION_NUMBER] = CY[CV()]+Y[CV()-1]*X[CV()-1]*2
               24           )
               25  )
               26  SELECT    string
               27  FROM
               28  (SELECT   IY,IX,
               29            SUBSTR (' .,,,-----++++%%%%@@@@### ', LEAST (MAX(I), 26), 1) I
               30       FROM Z
               31   GROUP BY IY ,IX)
               32       MODEL
               33        RETURN UPDATED ROWS
               34       PARTITION BY ( IY )
               35        DIMENSION BY ( ROW_NUMBER() OVER (PARTITION BY IY ORDER BY  IX ASC) AS POSITION )
               36        MEASURES     ( CAST( I AS VARCHAR2(100)) AS string )IGNORE NAV
               37        RULES
               38         UPSERT
               39         ITERATE( 99)
               40           UNTIL ( PRESENTV(string[ITERATION_NUMBER+2],1,0) = 0)
               41        ( string[0] =string[0] ||  string[ ITERATION_NUMBER+1] )
               42     ORDER BY IY;
              
              STRING
              ----------------------------------------------------------------------------------------------------
                           ....................................................................................
                          .......................................................................................
                         ........................................................................................
                        .........................................................................................
                      ....................................................,,,,,,,,,..............................
                     ................................................,,,,,,,,,,,,,,,,,,..........................
                    ..............................................,,,,,,,,,,,,,,,,,,,,,,,,.......................
                   ............................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,.....................
                   ..........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,...................
                  .........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,.................
                 ........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,................
                .......................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,..............
               .......................................,,,,,,,,,,,,,,,,,,,,,,,,--,,,,,,,,,,,,,,,,,,,,.............
              ......................................,,,,,,,,,,,,,,,,,,,,,,,,,,-+--,,,,,,,,,,,,,,,,,,,............
              ....................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----,,,,,,,,,,,,,,,,,,,...........
              ...................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,--- -----,,,,,,,,,,,,,,,,,..........
              .................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---++--++,,,,,,,,,,,,,,,,,,.........
              ................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----%++---,,,,,,,,,,,,,,,,,.........
              ..............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%----,,,,,,,,,,,,,,,,,,........
              .............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----- %%+----,,,,,,,,,,,,,,,,,,.......
              ...........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---%-+%   ----,,,,,,,,,,,,,,,,,,,......
              ..........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+ + #  %+%---,,,,,,,,,,,,,,,,,,......
              ........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----#      # +---,,,,,,,,,,,,,,,,,,.....
              .......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------%       %-----,,,,,,,,,,,,,,,,,.....
              .....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---------+         ------,,,,,,,,,,,,,,,,,....
              ....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----------+@       +-----------,,,,,,,,,,,,....
              ..................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----@-------++       ++-----------,,,,,,,,,,,,...
              .................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--+@% ---+ +@%%@     %%+@+@%------+-,,,,,,,,,,,...
              ................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----  # ++%               % @-----++--,,,,,,,,,,,..
              ..............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----+    %                  %%++ %+%@-,,,,,,,,,,,..
              .............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+                         %    ++-,,,,,,,,,,,,.
              ............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,------+                             @---,,,,,,,,,,,,.
              ..........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------++%                             ---,,,,,,,,,,,,.
              .........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+ +                             %+---,,,,,,,,,,,,,
              ........,,,,,,,,,,,,,,,,,,,,,--------------------@                                +----,,,,,,,,,,,,
              .......,,,,,,,,,,,,,,,,,,,,,,- +-----------------+                                 ----,,,,,,,,,,,,
              .......,,,,,,,,,,,,,,,,,,,,,--++------+---------+%                                 +++--,,,,,,,,,,,
              ......,,,,,,,,,,,,,,,,,,,,,,--%+-----++---------                                     #+-,,,,,,,,,,,
              .....,,,,,,,,,,,,,,,,,,,,,,----#%++--+@ -+-----+%                                     --,,,,,,,,,,,
              .....,,,,,,,,,,,,,,,,,,,,,,-----+## ++@ + +----%                                    +--,,,,,,,,,,,,
              ....,,,,,,,,,,,,,,,,,,,,,,------+@  @     @@++++#                                   +--,,,,,,,,,,,,
              ....,,,,,,,,,,,,,,,,,,,,,-------%           #++%                                      -,,,,,,,,,,,,
              ...,,,,,,,,,,,,,,,,,,,,,------++%#           %%@                                     %-,,,,,,,,,,,,
              ...,,,,,,,,,,,,,,,,,,,--------+               %                                     +--,,,,,,,,,,,,
              ...,,,,,,,,,,,,,,,,,,-----+--++@              #                                      --,,,,,,,,,,,,
              ..,,,,,,,,,,,,,,,,,-------%+++%                                                    @--,,,,,,,,,,,,,
              ..,,,,,,,,,,,-------------+ @ @                                                    ---,,,,,,,,,,,,,
              ..,,,,,,,,,---@--------@-+%                                                       +---,,,,,,,,,,,,,
              ..,,,,,------- +-++++-+%%%                                                       +----,,,,,,,,,,,,,
              ..,,,,,,------%--------++%                                                       +----,,,,,,,,,,,,,
              ..,,,,,,,,,,--+----------++#                                                       ---,,,,,,,,,,,,,
              ..,,,,,,,,,,,,------------+@@@%                                                    +--,,,,,,,,,,,,,
              ..,,,,,,,,,,,,,,,,,------- +++%                                                    %--,,,,,,,,,,,,,
              ...,,,,,,,,,,,,,,,,,,---------+@              @                                      --,,,,,,,,,,,,
              ...,,,,,,,,,,,,,,,,,,,,------- #              %@                                    +--,,,,,,,,,,,,
              ...,,,,,,,,,,,,,,,,,,,,,-------++@           %+                                      %-,,,,,,,,,,,,
              ....,,,,,,,,,,,,,,,,,,,,,-------            %++%                                     %-,,,,,,,,,,,,
              ....,,,,,,,,,,,,,,,,,,,,,,------+#  %#   #@ ++++                                    +--,,,,,,,,,,,,
              .....,,,,,,,,,,,,,,,,,,,,,,-----+ %%++% +@+----+                                    +--,,,,,,,,,,,,
              .....,,,,,,,,,,,,,,,,,,,,,,,---%+++--+#+--------%                                    #--,,,,,,,,,,,
              ......,,,,,,,,,,,,,,,,,,,,,,--++-----%%---------                                    @#--,,,,,,,,,,,
              .......,,,,,,,,,,,,,,,,,,,,,---------------------+@                                +-++,,,,,,,,,,,,
              ........,,,,,,,,,,,,,,,,,,,,,--------------------+                                 ----,,,,,,,,,,,,
              .........,,,,,,,,,,,,,,,,,,,,----,,,-------------                                #+----,,,,,,,,,,,,
              ..........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------+ +                              +---,,,,,,,,,,,,,
              ...........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+%#                           #---,,,,,,,,,,,,.
              ............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,------+#                        @   @---,,,,,,,,,,,,.
              .............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+#                        +    @--,,,,,,,,,,,,.
              ..............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+%   %+@                 %+-+ +++%-,,,,,,,,,,,..
              ................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----% %@++              # %  -----++-,,,,,,,,,,,,..
              .................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-- ++ ---+ + +%@     %++++++------%-,,,,,,,,,,,...
              ...................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---- -------++       +------------,,,,,,,,,,,,...
              ....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----------+%       +--------,,,,,,,,,,,,,,,....
              ......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+         -----,,,,,,,,,,,,,,,,,,....
              .......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------+        ----,,,,,,,,,,,,,,,,,,.....
              .........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----+%      %#---,,,,,,,,,,,,,,,,,,,.....
              ..........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+%+%@  %+%%--,,,,,,,,,,,,,,,,,,......
              ............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+-+%  %----,,,,,,,,,,,,,,,,,,.......
              .............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%@+---,,,,,,,,,,,,,,,,,,,.......
              ...............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%----,,,,,,,,,,,,,,,,,,........
              ................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----%+ +--,,,,,,,,,,,,,,,,,.........
              ..................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---++----,,,,,,,,,,,,,,,,,..........
              ...................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,---@-----,,,,,,,,,,,,,,,,,..........
              .....................................,,,,,,,,,,,,,,,,,,,,,,,,,,,-----,,,,,,,,,,,,,,,,,,,...........
               .....................................,,,,,,,,,,,,,,,,,,,,,,,,,,--%,,,,,,,,,,,,,,,,,,,,............
               .......................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,..............
                ........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,...............
                 ........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,................
                  .........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,.................
                   ..........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,...................
                    ............................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,.....................
                     .............................................,,,,,,,,,,,,,,,,,,,,,,,,.......................
                      ................................................,,,,,,,,,,,,,,,,,..........................
                       .....................................................,,,,.................................
                        .........................................................................................
                         ........................................................................................
                          ......................................................................................
                           ....................................................................................
                             .................................................................................
                              ..............................................................................
              
              100 ligne(s) sélectionnée(s).
              
              Ecoulé : 00 :00 :51.15
              SQL>