Hack 42. Present Data Graphically Using SVG

You can generate scalable vector graphics (SVG) images directly from SQL.

If you want to produce a pie chart directly from a database, you can have SQL produce the SVG elements that are required (SVG is an XML graphics format). You can create a sequence of SQL views that build upon each other to create a pie chart in SVG. The example shown is in MySQL, but the technique works with any flavor of SQL that supports VIEW.

The input data is in the d table, as shown in Table 6-1. The labels, the colors, and the relative values as percentages are in this one table. You can color each slice of the pie using one of SVG's named colors. You could instead use RGB values if you need subtler shades. For example, a mid-green would be the string rgb(0,128,0).

You can create and populate this table with these commands:

CREATE TABLE d (id CHAR(16) NOT NULL PRIMARY KEY, color CHAR(8), v INTEGER); INSERT INTO d(id, color, v) VALUES ('Conservative', 'blue', 40), ('Labour', 'red', 30), ('Lib-Dem', 'yellow', 20), ('Other', 'white', 10);

Figure 6-2 shows what the pie chart will look like when you render it with the Adobe SVG plug-in.

Figure 6-2. SVG pie chart

SVG will need the coordinates of the start and end of each slice of pie (segment). To calculate those you need the angle at which each segment starts and ends. To calculate the angles you will need to calculate a cumulative sum (running total) of the percentages. So, Conservative starts at 0 and ends at 40, Labour starts at 40 and ends at 70, Lib-Dem starts at 70 and ends at 90, and Other starts at 90 and ends at 100. These percentages will be translated into radians and then into x and y coordinates. The pie1 view shows the start and end positions for each party as a percentage:

mysql> CREATE VIEW pie1 AS -> SELECT id, -> COALESCE((SELECT SUM(v) FROM d WHERE id< x.id),0) AS s, -> (SELECT SUM(v) FROM d WHERE id<=x.id) AS e -> FROM d x; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM pie1; +--------------+------+------+ | id | s | e | +--------------+------+------+ | Conservative | 0 | 40 | | Labour | 40 | 70 | | Lib-Dem | 70 | 90 | | Other | 90 | 100 | +--------------+------+------+

Notice that the inner SELECT statements require a SUM to be calculated for every row before the current row. This gives you the start position (s) and a similar but inclusive SUM for the end position (e). You would normally want to avoid this kind of calculation because it is inefficient, but for a pie chart you are never going to want more than 10 or so rows, so the cost of the calculation is trivial.

The pie2 view converts the percentage values into radians. You'll need radians to work with the SIN and COS functions required for calculating the coordinates:

mysql> CREATE VIEW pie2 AS -> SELECT id, 2*3.1415*s/100 AS s, 2*3.1415*e/100 AS e -> FROM pie1; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM pie2; +--------------+------------+------------+ | id | s | e | +--------------+------------+------------+ | Conservative | 0.00000000 | 2.51320000 | | Labour | 2.51320000 | 4.39810000 | | Lib-Dem | 4.39810000 | 5.65470000 | | Other | 5.65470000 | 6.28300000 | +--------------+------------+------------+

You'll need x and y coordinates for the start and end of each segment, which you can calculate using COS and SIN:

mysql> CREATE VIEW pie3 AS -> SELECT id, 100*COS(s) AS x1, 100*SIN(s) AS y1, -> 100*COS(e) AS x2, 100*SIN(e) AS y2 -> FROM pie2; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM pie3; +--------------+--------+--------+--------+--------+ | id | x1 | y1 | x2 | y2 | +--------------+--------+--------+--------+--------+ | Conservative | 100.00 | 0.00 | -80.90 | 58.78 | | Labour | -80.90 | 58.78 | -30.91 | -95.10 | | Lib-Dem | -30.91 | -95.10 | 80.89 | -58.79 | | Other | 80.89 | -58.79 | 100.00 | -0.02 | +--------------+--------+--------+--------+--------+

You can now insert these values into the SVG tags required for each segment. You can use the REPLACE function to do this instead of using a sequence of string concatenations:

mysql> CREATE VIEW pie4 AS -> SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( -> '' -> ,'x1',x1),'y1',y1),'x2',x2),'y2',y2),'color',color) AS r -> FROM pie3 JOIN d ON pie3.id=d.id; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM pie4G *************************** 1. row *************************** r: *************************** 2. row *************************** r: *************************** 3. row *************************** r: *************************** 4. row *************************** r: 4 rows in set (0.00 sec)

In the SVG generated, the d attribute contains the instructions for drawing a sector. Taking the first one as an example, you have M0 0 L 100.00 0.00 A100,100 0 0, 1 -80.90,58.78 z, which is interpreted as follows:

M 0 0

Move to 0, 0.

L 100.00 0.00

Draw a line to 100.0, 0.00.

A100,100 0 0, 1 80.90,58.78

Draw an arc, radius 100, 100 (x and y), to the point 80.90, 58.78. The 0 0, 1 values dictate which way the arc should go.

z

Close the figure by drawing a line back to the starting point.

You can add the labels using text nodes in a similar style. The labels will be centered on a point that is 60 units along the bisecting radius for each segment:

mysql> CREATE VIEW pie5 AS -> SELECT REPLACE(REPLACE(REPLACE( -> 'tt' -> ,'xx', (x1+x2)/SQRT((x1+x2)*(x1+x2)+(y1+y2)*(y1+y2))*60) -> ,'yy', (y1+y2)/SQRT((x1+x2)*(x1+x2)+(y1+y2)*(y1+y2))*60) -> ,'tt',id) AS r -> FROM pie3; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM pie5; +-------------------------------------------------------------------+ | r | +-------------------------------------------------------------------+ | Conservative | | Labour | | Lib-Dem | | Other | +-------------------------------------------------------------------+

Finally, you can UNION these queries together. You also need to add the opening and closing SVG tags. Because the order of the components is important, you can use an additional column so that the output can be ordered. You cannot specify an ORDER BY on a view, but you can set up the view so that it can be ordered:

mysql> CREATE VIEW pie6 AS -> SELECT 1 AS s, -> ' -> UNION -> SELECT 2, -> 'viewBox="-120 -120 240 240" text-anchor="middle">' -> UNION -> SELECT 3, r FROM pie4 -> UNION -> SELECT 4, r FROM pie5 -> UNION -> SELECT 5, ''; Query OK, 0 rows affected (0.01 sec) mysql> quit Bye $ mysql -B -N -e "SELECT r FROM pie6 ORDER BY s;" > pie.svg $ cat pie.svg Conservative Labour Lib-Dem Other

For Oracle, you need to reference the dual pseudotable in the pie6 view. This becomes:

CREATE VIEW pie6 AS SELECT 1 AS s, '' FROM dual UNION SELECT 3, r FROM pie4 UNION SELECT 4, r FROM pie5 UNION SELECT 5, '' FROM dual

6.2.1. Vendor-Specific XML Features

Although the code shown works across all of the platforms, you can take advantage of vendor-specific XML features. One advantage is that the XML features will take care of character encodings.

6.2.1.1. SQL Server

SQL Server includes the FOR XML EXPLICIT option, which allows you to use the column headings to specify how the data element shows up in the XML generated. Here is an example of how you can use this option to generate the text elements required:

SELECT 1 AS Tag ,NULL AS Parent ,(x1+x2)/2 AS [text!1!x] ,(y1+y2)/2 AS [text!1!y] ,id AS [text!1!!element] FROM pie3 FOR XML EXPLICIT

The output from the query has a single column and no unnecessary whitespace is introduced:

Conservative Labour ...

The scheme is flexible and you can make the system generate practically any XML format required. However, the next stage (pie6 in the earlier example) is significantly more complex, and the FOR XML EXPLICIT approach becomes unmanageable. Unfortunately, you cannot create a view from a FOR XML query and you cannot combine them in a union (although a union or a view may be "inside" the FOR XML statement).

6.2.1.2. Oracle

You can use the XMLElement and XMLAttributes functions to generate XML output. You can generate the four text elements shown in the preceding example by using the following:

SQL> SELECT XMLElement("text", 2 XMLAttributes(ROUND((x1+x2)/2) AS "x", 3 ROUND((y1+y2)/2) AS "y"), 4 id) 5 FROM pie3; XMLELEMENT("TEXT",XMLATTRIBUTES(ROUND((X1+X2)/2)AS"X",ROUND((Y1+Y2)/2)AS"Y"),ID) -------------------------------------------------------------------------------- Conservative Labour Lib-Dem Other

Unfortunately, this approach can't easily solve the whole SVG problem. You may nest a number of XMLElement functions inside an XMLElement, but you cannot return the result of a query with more than one row inside an XMLElement.

6.2.1.3. MySQL

You can get your output as XML from the command-line client. This example does not produce the right tags, but you can use other tools such as XSLT [Hack #41] to transform it. Just be sure that the correct details are in the output. For example, you could create a pie7 view to report the details required for the path and text elements:

CREATE VIEW pie7 AS SELECT 'text' AS tag, id AS content, (x1+x2)/2 AS att1, (y1+y2)/2 AS att2, 2 AS s FROM pie3 UNION SELECT 'path' AS tag, '' AS content, REPLACE(REPLACE(REPLACE(REPLACE( 'M0 0 l x1 y1 A100,100 0 0, 1 x2,y2 z' ,'x1',x1),'y1',y1),'x2',x2),'y2',y2) AS att1, color AS att2, 1 AS s FROM pie3 JOIN d ON pie3.id=d.id;

You also can have the client generate the XML:

$ mysql -u scott -ptiger dbname --xml -e "SELECT * FROM pie7 ORDER BY s" path M0 0 l 100 0 A100,100 0 0, 1 -80.897342382161,58.78452173407 z blue 1 ... text Conservative 9.5513288089193 29.392260867035 ...

You still have some work to do to turn that into SVG. The following stylesheet (save it as pie.xsl) will produce SVG:

You can run that using xsltproc as follows:

$ mysql -u scott -ptiger dbname --xml -e 'SELECT * FROM pie7 ORDER BY s' > | xsltproc pie.xsl -