sp_GraphNelGene_ExampleExecution


USE tempdb;
GO
 
-----------------------------------------------------------------------------------
-- Complex Test Hierarchy Data
IF OBJECT_ID('tempdb..#Node') IS NOT NULL
  DROP TABLE #Node;
CREATE TABLE #Node (NodeName varchar(250), NodeLabel varchar(250) NULL);
 
IF OBJECT_ID('tempdb..#NodeRefersTo') IS NOT NULL
  DROP TABLE #NodeRefersTo;
CREATE TABLE #NodeRefersTo (NodeName varchar(250),NodeReferedTo varchar(250));
 
 
IF OBJECT_ID('tempdb..#Grid') IS NOT NULL
  DROP TABLE #Grid
 
DECLARE @x_upper INT = 6, @x_lower INT = 1, @y_upper INT = 6, @y_lower INT = 1;
          
WITH x_axis (x_coordinate) AS
(
  SELECT @x_lower AS x_coordinate
  UNION ALL
  SELECT x_coordinate + 1
  FROM x_axis
  WHERE x_coordinate < @x_upper
),
y_axis (y_coordinate) AS
(
  SELECT @y_lower AS y_coordinate
  UNION ALL
  SELECT y_coordinate + 1
  FROM y_axis
  WHERE y_coordinate < @y_upper
),
Grid AS
( 
  SELECT
    'x' + CAST(x_coordinate AS varchar(5)) + ' - y'  + CAST(y_coordinate AS varchar(5)) AS NodeLabel,
    x_coordinate AS x,
    y_coordinate AS y,
    ROW_NUMBER() OVER(ORDER BY y_coordinate, x_coordinate) AS NodeID
  FROM x_axis 
  CROSS JOIN y_axis
)
SELECT
  x, y, NodeLabel, 
  CASE SUBSTRING(CAST(NodeID AS varchar(6)),1,1)
  WHEN '1' THEN 'I'
  WHEN '2' THEN 'H'
  WHEN '3' THEN 'G'
  WHEN '4' THEN 'F'
  WHEN '5' THEN 'E'
  WHEN '6' THEN 'D'
  WHEN '7' THEN 'C'
  WHEN '8' THEN 'B'
  WHEN '9' THEN 'A'
  END + CAST(NodeID AS varchar(6)) AS NodeID   
INTO #Grid
FROM Grid
OPTION ( MAXRECURSION 32767 );
 
-- Insert #Node
INSERT INTO #Node (NodeName, NodeLabel)
SELECT NodeID, NodeLabel
FROM #Grid
GROUP BY NodeID, NodeLabel;
 
UPDATE #Node
SET NodeLabel = NodeName + '(' + NodeLabel + ')';
 
WITH Edges AS
(
  SELECT g.NodeID, e.NodeID AS NodeReferedTo
  FROM #Grid g
  INNER JOIN #Grid e
    ON e.x = g.x 
    AND e.y = g.y - 1
 
  UNION ALL
 
  SELECT g.NodeID, belowleft.NodeID AS NodeReferedTo
  FROM #Grid g
  INNER JOIN #Grid belowleft
    ON belowLeft.x = g.x-1
    AND belowLeft.y = g.y - 1
 
  UNION ALL
 
  SELECT g.NodeID, belowright.NodeID AS NodeReferedTo
  FROM #Grid g
  INNER JOIN #Grid belowright
    ON belowright.x = g.x + 1
    AND belowright.y = g.y - 1
)
 
INSERT INTO #NodeRefersTo (NodeName,NodeReferedTo)
SELECT * 
FROM Edges
ORDER BY NodeID, NodeReferedTo;
 
---------------------------------------------------------------------------
-- Generate full network diagram 
DECLARE @Node AS GraphNELNodeTableType; 
DECLARE @NodeReferedTo AS GraphNELNodeRefersToTableType;
DECLARE @AnchorNodes GraphNELAnchorNodeTableType
 
INSERT INTO @Node (NodeName, NodeLabel) SELECT NodeName, NodeLabel FROM #Node;
INSERT INTO @NodeReferedTo (NodeName, NodeReferedTo) SELECT NodeName, NodeReferedTo FROM #NodeRefersTo;
 
EXECUTE dbo.sp_GraphNELGene 
   @Node
  ,@NodeReferedTo
  ,@AnchorNodes
  ,@IncludeLabels = 0
  ,@PDFFile = 'c:\bioconductor\plot.pdf'
GO
 
---------------------------------------------------------------------------
-- Generate subset network diagram 
DECLARE @Node AS GraphNELNodeTableType; 
DECLARE @NodeReferedTo AS GraphNELNodeRefersToTableType;
DECLARE @AnchorNodes GraphNELAnchorNodeTableType
 
INSERT INTO @Node (NodeName, NodeLabel) SELECT NodeName, NodeLabel FROM #Node;
INSERT INTO @NodeReferedTo (NodeName, NodeReferedTo) SELECT NodeName, NodeReferedTo FROM #NodeRefersTo;
INSERT INTO @AnchorNodes SELECT 'I19' UNION ALL SELECT 'H24'; --UNION ALL SELECT 'I137' UNION ALL SELECT 'I161';
 
EXECUTE dbo.sp_GraphNELGene 
   @Node = @Node
  ,@NodeReferedTo = @NodeReferedTo
  ,@AnchorNodes = @AnchorNodes
  ,@IncludeLabels = 0
  ,@IncludeRefersTo = 1
  ,@IncludeReferredToBy = 1
  ,@PDFFile = 'c:\bioconductor\plot.pdf'
GO

%d bloggers like this: