Lesson 5: Output spatial data to SVG

In this lesson, you create an SVG document to view a multipolygon expressed in WKT. You can export geometries to SVG format for viewing in Interactive SQL or in an SVG-compatible application.

Prerequisites

This lesson assumes you have completed all preceding lessons. See Lesson 1: Install additional units of measure and spatial reference systems.

This lesson assumes you have the roles and privileges listed in the Privileges section at the start of this tutorial: Tutorial: Experimenting with the spatial features.

Task

In Interactive SQL, execute the following statement to create a variable with an example geometry:

CREATE OR REPLACE VARIABLE @svg_geom 
ST_Polygon = (NEW ST_Polygon('Polygon ((1 1, 5 1, 5 5, 1 5, 1 1), (2 2, 2 3, 3 3, 3 2, 2 2))'));

In Interactive SQL, execute the following SELECT statement to call the ST_AsSVG method:
```
SELECT @svg_geom.ST_AsSVG() AS svg;
```
The result set has a single row that is an SVG image. You can view the image using the SVG Preview feature in Interactive SQL. To do this, double-click the result row, and select the SVG Preview tab. You should see a square geometry inside of another square geometry.

Note: By default, Interactive SQL truncates values in the Results pane to 256 characters. If Interactive SQL returns an error indicating that the full column value could not be read, increase the truncation value. To do this, click Tools » Options and click SAP Sybase IQ in the left pane. On the Results tab, change Truncation Length to a high value, such as 5000. Click OK to save your changes, execute query again, and then double-click the row again.
The previous step described how to preview an SVG image within Interactive SQL. However, it may be more useful to write the resulting SVG to a file so that it can be read by an external application. You could use the xp_write_file system procedure or the WRITE_CLIENT_FILE function [String] to write to a file relative to either the database server or the client computer. In this example, you will use the OUTPUT statement [Interactive SQL].
In Interactive SQL, execute the following SELECT statement to call the ST_AsSVG method and output the geometry to a file named myPolygon.svg:
```
SELECT @svg_geom.ST_AsSVG();
OUTPUT TO 'c:\\temp\\massdata\\myPolygon.svg' 
QUOTE '' 
ESCAPES OFF 
FORMAT TEXT
```
You must include the QUOTE '' and ESCAPES OFF clauses, otherwise line return characters and single quotes are inserted in the XML to preserve whitespace, causing the output to be an invalid SVG file.
Open the SVG in a web browser or application that supports viewing SVG images. Alternatively, you can open the SVG in a text editor to view the XML for the geometry.
The ST_AsSVG method generates an SVG image from a single geometry. In some cases, you want to generate an SVG image including all of the shapes in a group. The ST_AsSVGAggr method is an aggregate function that combines multiple geometries into a single SVG image. First, using Interactive SQL, create a variable to hold the SVG image and generate it using the ST_AsSVGAggr method.
```
CREATE OR REPLACE VARIABLE @svg XML;
SELECT ST_Geometry::ST_AsSVGAggr( geometry, 'attribute=fill="black"' )
INTO @svg
FROM Massdata;
```
The @svg variable now holds an SVG image representing all of the zip code regions in the Massdata table. The 'attribute=fill="black"' specifies the fill color that is used for the generated image. If not specified, the database server chooses a random fill color. Now that you have a variable containing the SVG image you are interested in, you can write it to a file for viewing by other applications. Execute the following statement in Interactive SQL to write the SVG image to a file relative to the database server.
```
CALL xp_write_file( 'c:\\temp\\Massdata.svg', @svg );
```
The WRITE_CLIENT_FILE function could also be used to write a file relative to the client application, but additional steps may be required to ensure appropriate privileges are enabled. If you open the SVG image in an application that supports SVG data, you should see an image like the following:

The image is not uniformly black; there are small gaps between the borders of adjacent zip code regions. These are actually white lines between the geometries and is characteristic of the way the SVG is rendered. There are not really any gaps in the data. Larger white lines are rivers and lakes.

The geometry has been viewed as an SVG.