 | Level: Intermediate Knut Stolze (stolze@de.ibm.com), DB2 z/OS Utilities Development, IBM Germany
15 Nov 2007 Visualizing spatial data is important when working with 2D or 3D objects, especially if this data is managed inside a database and you need dedicated tools to access the spatial information and prepare it for your users. This article
illustrates how to exploit scalable vector graphics (SVG) for such tasks. It
demonstrates how to convert your spatial data stored in IBM® DB2®
for Linux®, UNIX®, Windows®
or DB2 for z/OS® to SVG and how to build web pages with that technique.
Introduction
In the previous article, "Visualize spatial data in DB2" (see Resources), I
demonstrated how you could use the Environmental Systems Research Institute (ESRI) ArcExplorer™ to look at spatial data
stored in a DB2 LUW database with the help of the DB2 Spatial Extender. The
capabilities of the ESRI ArcExplorer are limited. For example, you cannot embed it
in your own applications or enhance it with further functionality that you may need in your own projects. Thus, it is necessary to look for other solutions. While it may be an option to use an open-source or commercial product, there are situations that can be addressed more quickly with some PHP code and Scalable Vector Graphics (SVG).
The goal of the current article is to provide such a light-weight -- but very
flexible and customizable -- approach. The article illustrates how spatial and non-spatial
data can be rendered in any modern web browser like Mozilla Firefox, Opera, or
Internet Explorer. The articles describes how to exploit PHP as a scripting language to generate the web pages
dynamically, depending on the SQL query provided by the user. The conversion of
geometries to their SVG representation is performed by the DB2 database server with the help of a new user-defined function. That new function operates on all the spatial data types. With this article, you have all the necessary pieces to build your own Web applications and include spatial data in whichever way you like.
Rendering spatial data as SVG
Figure 1 gives a preview of what can easily be done with SVG. This figure shows a
screenshot of a small Web application built on XHTML and PHP. A single web page
shows all relevant information in three frames. One frame is used to specify
multiple SQL queries, where each query defines a single layer and the spatial and
non-spatial data is retrieved from your DB2 database. Another frame shows the
two-dimensional, graphical representation of the geometries in all layers. Since spatial data is not very meaningful by itself, the third frame is used to show the values for non-spatial attributes of a currently selected geometry. This sample application provides a generic framework that can deal with any spatial data residing in your DB2 database.
Figure 1. Screenshot of a
sample application
The data used for Figure 1 is provided as sample data to the DB2 Spatial Extender.
I imported the three shapefiles sjCensusBlocks.shp, sjMainStreets.shp, and
sjZipCodes.shp (from the ~/sqllib/samples/extenders/spatia/data/ directory of a DB2 instance) into a spatially-enabled database. The command used for the spatial import is shown in Listing 1. You can also use the DB2 Control Center if you prefer it over the command line tools.
Listing 1. Importing sample
spatial data
$ db2se import_shape svg -client 1 \
-filename ~/sqllib/samples/extenders/spatial/data/sjCensusBlocks \
-srsname NAD83_SRS_1 -tablename sjCensusBlocks -spatialcolumn shape -commitscope 500
$ db2se import_shape svg -client 1 \
-filename ~/sqllib/samples/extenders/spatial/data/sjMainStreets \
-srsname NAD83_SRS_1 -tablename sjMainStreets -spatialcolumn shape -commitscope 500
$ db2se import_shape svg -client 1 \
-filename ~/sqllib/samples/extenders/spatial/data/sjZipCodes \
-srsname NAD83_SRS_1 -tablename sjZipCodes -spatialcolumn shape -commitscope 500
|
Subsequently, the data in table SJCENSUSBLOCKS is used as a reference. Find all
zip code areas that overlap with any of the census blocks, and find all streets that lie within the minimum bounding rectangle (MBR) of any of those zip code areas. Those conditions are expressed in the three SQL queries in Listing 2.
Listing 2. Queries against
sample spatial data
SELECT *
FROM sjcensusblocks
-- select only those zip code areas that overlap with any of the
-- existing census blocks
SELECT *
FROM sjzipcodes AS z
WHERE EXISTS ( SELECT 1
FROM sjcensusblocks AS c
WHERE db2gse.ST_Intersects(z.shape, c.shape) = 1 )
-- query main streets laying in MBR of relevant zip code areas
SELECT *
FROM sjmainstreets AS s
WHERE EXISTS ( SELECT 1
FROM sjzipcodes AS z
WHERE EXISTS ( SELECT 1
FROM sjcensusblocks AS c
WHERE db2gse.ST_Intersects(
z.shape, c.shape) = 1 ) AND
db2gse.ST_EnvIntersects(z.shape, s.shape) = 1 )
|
Notice that the order of the queries in Listing 2 is not the same as in the
screenshot in Figure 1. The reason is that zip codes should be shown on the
lowest layer, census blocks on top of zip code areas, and main streets as highest on top of zip code areas and census blocks. Therefore, the second query is specified for layer 1 in the input panel.
Now that you have an idea what can be achieved easily by combining SVG
and spatial data in DB2 databases, the subsequent sections explain, in more detail,
how I implemented the Web application.
User-defined function
to convert geometries to SVG
Converting geometry data to its SVG format is an important part for the rendering of
spatial data in web pages. SVG was defined by W3C and the specification
can be found via the links provided in the Resources
section. Fortunately, SVG is very similar to the well-known text (WKT)
representation. The DB2 Spatial Extender for DB2 LUW as well as the Spatial Support feature for DB2 z/OS already provide methods and functions to convert a geometry to WKT. We exploit that and can reduce our conversion task to transform WKT to SVG.
A user-defined function (UDF), implemented in C code takes care of the conversion.
(Multi-)linestrings and (multi-)polygons of the DB2 Spatial Extender are converted to
SVG path elements. Such a path element has the tag name path, some additional attributes to influence the color, fill mode,
line width, and other aspects of the rendered geometry, and the actual data defining
the location and shape of the geometry. The data types of the DB2 Spatial Extender
only define the location and shape, which implies that any other additional
attributes must be provided by the user when creating the SVG element from a
geometry. Therefore, our UDF ST_AsSVG takes not only a
geometry as input parameter, but also the color to be used for the interior of the
geometry (for example, the SVG attribute fill) and the
width in which the lines of the geometry shall be drawn (for example, the SVG
attribute stroke-width, measured in units of the
underlying coordinate system). The color is used for the area covered by a
(multi-)polygon or the line of a (multi-)linestring. Any other attributes are either
fixed, like the usage of fill-rule="evenodd" for
(multi-)polygons, or they have to be added by the application that receives the SVG
string and processes it further, such as, the client application.
The data that defines an SVG path element consists of a series of coordinate values
defining points, and additional control characters preceding a point. Coordinates
of a single point are separated by commas and spaces are used between points. Our
function ST_AsSVG uses the control characters M, L, and Z
only. SVG defines that M position to the location where a
point shall be placed without connection to any previous point. Starting from such a
point, lines are draws to following points, where any following point is specified
with relative coordinates to its preceding point. WKT only contains absolute
coordinates and not relative ones. Therefore, each point should be prefixed with the control character L. The third control character Z closes a path, which is needed for (multi-)polygons.
The described conversion is straight-forward and can be done in-place, i.e. the
memory area holding the WKT string is modified directly to transform it to a valid
SVG element string. Replacing spaces with commas and vice versa can be done in a
single scan over the string, and the control characters are injected at the same
time. Thus, converting the data is easy to accomplish. That leaves the addition of the SVG rendering-specific attributes to be done. Our function allocates some additional space to accommodate for those attributes.
Listing 3 shows the main parts of the external implementation of the UDF. There are
a few things I would like to draw your attention to:
|
Why C code?
We used a C function, even though it is possible to do the necessary string
operations also in SQL. The reason is that string operations are often more
cumbersome than simply iterating over the string with a pointer in C. Naturally, the same logic can be implemented in Java as well, but the usage of Java UDFs in DB2 z/OS requires a more complex system configuration.
|
|
- Since the actual size of the WKT and SVG strings are unknown, the DBA sends the data
between DB2 and the function via locators. That allows the user to dynamically allocate as much memory as is really needed but not more.
- Typically, the UDF is used in the select list of a SQL query. That implies that
the function is called multiple times during the execution of the SQL statement. A
simple, but effective performance improvement is to carry allocated memory from one call to the next. The number of memory operations can be reduced significantly that way.
- The listing shows only the handling for (multi-)linestrings and also omits the
error handling for illustration purposes. You can find the complete code in the Download section.
Listing 3. C code of UDF to convert WKT to SVG
void SQL_API_FN convertWKT2SVG(
SQLUDF_LOCATOR *wktLocator, SQLUDF_VARCHAR *color,
SQLUDF_DOUBLE *lineWidth, SQLUDF_LOCATOR *svgLocator,
SQLUDF_NULLIND *wktLocator_ind, SQLUDF_NULLIND *color_ind,
SQLUDF_NULLIND *lineWidth_ind, SQLUDF_NULLIND *svgLocator_ind,
SQLUDF_TRAIL_ARGS_ALL)
{
int rc = SQLUDF_API_RC_OK;
struct scratchMap {
sqlint32 length;
char *data;
} *scratch = (struct scratchMap *)SQLUDF_SCRAT->data;
sqlint32 length, wktLength, svgLength = 0;
static const size_t xmlAttrLength = 100; /* space for attributes */
static const size_t xmlTrailLength = 20; /* space for closing XML stuff */
switch (SQLUDF_CALLT) {
case SQLUDF_FIRST_CALL:
scratch->length = 0;
scratch->data = NULL;
/* fall through to NORMAL call */
case SQLUDF_NORMAL_CALL:
/* get memory for WKT/SVG data (reuse allocation from previous call) */
rc = readWktFromLocator(wktLocator, scratch,
SQLUDF_STATE, SQLUDF_MSGTX);
/* convert ST_(Multi)Polygon to SVG path */
if (strncmp(scratch->data + xmlAttrLength, "POLYGON", 7) == 0 ||
strncmp(scratch->data + xmlAttrLength, "MULTIPOLYGON", 12) == 0) {
/* prepend XML attributes */
size_t attrLength = sprintf(scratch->data,
"<path fill-rule=\"evenodd\" fill=\"%s\" stroke=\"black\" "
"stroke-width=\"%lf\" d=\"", color, *lineWidth);
scratch->data[attrLength] = ' ';
/* remove type identifier from WKT */
char *tmp = scratch->data + xmlAttrLength;
char *beginPointList = strstr(tmp, "(");
for ( ; tmp < beginPointList; tmp++) *tmp = ' ';
/* convert all rings */
while (*tmp != '\0') {
convertRingFromWktToSvgPath(tmp);
for ( ; *tmp != '\0'; tmp++); *tmp++ = 'Z';
/* find beginning of next ring */
for ( ; *tmp == ')' || *tmp == ',' || *tmp == ' '; tmp++) *tmp = ' ';
}
/* close XML/SVG element */
strcat(tmp, "\" />");
svgLength = strlen(scratch->data);
}
/* write result to new LOB locator and set NULL indicator */
rc = writeSvgToLocator(scratch, svgLocator, svgLocator_ind,
SQLUDF_STATE, SQLUDF_MSGTX);
break;
}
cleanup:
if (SQLUDF_CALLT == SQLUDF_FINAL_CALL || SQLUDF_CALLT == SQLUDF_FINAL_CRA ||
(SQLUDF_CALLT == SQLUDF_FIRST_CALL && rc != SQLUDF_API_RC_OK)) {
delete [] scratch->data;
scratch->data = NULL;
}
}
|
The code needs to be compiled and linked into a shared library, and that library
copied to the ~/sqllib/function/ directory of your DB2 LUW instance. The sample
script bldrtn can be used for that. On DB2 z/OS, you compile and linkedit the C code
into a load module and store it in a data set that is used by the respective WLM environment. Additionally, you have to bind the bind bindfile or DBRM to your DB2 system.
In DB2 LUW as well as DB2 z/OS it is necessary to register a function in your DB2
database or subsystem in order to use it later on. The CREATE
FUNCTION statements for DB2 LUW is shown in Listing 4. DB2 z/OS uses some
different options, especially a different EXTERNAL NAME
clause, which requires adapted SQL statements. You will find the complete code in
the Download section (see Downloads
Listing 4. Registering the UDF in DB2
CREATE FUNCTION db2gse.ST_AsSVG_int (
wkt CLOB(2G) AS LOCATOR,
color VARCHAR(16),
lineWidth DOUBLE )
RETURNS CLOB(2G) AS LOCATOR
LANGUAGE C EXTERNAL NAME 'svgudf!convertWKT2SVG'
PARAMETER STYLE SQL
DETERMINISTIC NO EXTERNAL ACTION
READS SQL DATA FENCED FINAL CALL
SCRATCHPAD 20@
CREATE FUNCTION db2gse.ST_AsSVG (
g db2gse.ST_Geometry,
color VARCHAR(16),
lineWidth DOUBLE )
RETURNS CLOB(2G)
LANGUAGE SQL READS SQL DATA
DETERMINISTIC NO EXTERNAL ACTION
RETURN db2gse.ST_AsSVG_int(db2gse.ST_AsText(g), color, lineWidth)@
|
There are two functions involved. First, ST_AsSVG_int is
an internal function that should not be called directly. This function provides the
link between the external C code and DB2. It assumes that the shared library
containing the compiled C code is named svgudf and the
function to be called by DB2 convertWKT2SVG. This
internal function takes the WKT representation of a geometry as input -- not the
geometry itself -- along with the color and line width for the respective SVG
attributes. Wrapped around the internal function is ST_AsSVG. This second function operates directly on geometries. Internally, it invokes ST_AsText to produce the WKT string, which is then fed to the internal function. Thus, the caller does not have to worry about the C code working with WKT only. It is completely transparent.
Once the functions are registered, you can use them in your SQL statements and have
a look at the SVG strings produced by it. Listing 5 shows an example. A
multi-polygon is created using the ST_Geometry constructor
function, and that value is passed directly to ST_AsSVG.
The output shows the string produced by ST_AsSVG. You can see that each ring in the multi-polygon is transformed to a single sub-path in SVG, started with the M control character and terminated with Z to close the ring. There is no distinction between inner and outer rings because the SVG attribute fill-rule="evenodd" tells the SVG renderer to determine the interior or exterior of areas again.
Listing 5. Testing ST_AsSVG
$ db2 -td@
db2 => SET CURRENT FUNCTION PATH = CURRENT FUNCTION PATH, db2gse@
db2 => VALUES ST_AsSVG(ST_Geometry('multipolygon (((10 10, 10 20, 20 20, 20 10, 10 10),
(13 13, 13 16, 16 16, 16 13, 13 13)), ((50 50, 50 60, 60 60, 50 50)))', 0),
'red', 0.001)@
1
-------------------------------------------------------
<path fill-rule="evenodd" fill="red" stroke="black" stroke-width="0.001000"
d="M10,10 L20,10 L20,20 L10,20 L10,10Z
M13,13 L13,16 L16,16 L16,13 L13,13Z
M50,50 L60,60 L50,60 L50,50Z " />
|
Accessing DB2 from PHP
The second major piece of the Web application is the HTML and PHP code itself. PHP
is used to communicate with DB2 and to build the content for the frame showing query
results dynamically. A series of good articles already exist that explain how to
access DB2 databases from PHP applications. The access can be done with the ibm_db2 extension (see Resources),
or the unixODBC module. The latter is not recommended
because unixODBC cannot handle large objects (LOBs),
which is mandatory these days for pretty much any application, especially spatial
applications. Thus, the functionality of the ibm_db2 extension is solely used for the communication with DB2. Please refer to the manuals to install this extension and how to use it.
Implementation of the Web application
The Web application consists of merely six files. The files main.html and data.html
are only used to build the frame set. We show the queries in the upper left frame,
the rendered SVG output in the lower frame, and the non-spatial attributes in the
upper right frame. Another, supporting file is provided with connect_data.inc. This
file is used as a configuration file for the connection to our DB2 database with the proper credentials, i.e. it define the database name, user name, and password. (Alternatively, it is possible to add input fields on the web page where these information can be supplied by the user.) The three other files contain the code for each of the visible frames.
Queries frame
The queries frame (coded in file queries.xhtml) defines several input fields where
you specify the SQL queries. One query retrieves the data for a single layer.
Additionally, you specify the color that should be used for rendering all geometries
of that layer. Once you specified all your SQL queries, you click on the Execute button, which submits the queries to a PHP script and the PHP script builds the XHTML document for the Results frame. The definition of the input fields is straight-forward HTML code, as you can see in Listing 6.
Listing 6. Implementation of
the queries frame
<form action="results.php" method="post">
<table>
<tr>
<td>1</td>
<td><textarea size="100" cols="50" rows="3" name="query1"></textarea></td>
<td><select name="color1">
<option selected="1" value="red">red</option>
<option value="blue">blue</option>
<option value="green">green</option>
</select></td>
</tr>
...
</table>
<input type="SUBMIT" value="Execute" />
</form>
|
Results frame
The PHP script results.php is the central part of our small application. It
receives the SQL queries (and the associated color information) via POST parameters,
executes those queries against DB2 and builds the XHTML web page containing SVG elements to be
shown in the Results frame. Listing 7 shows the functions implemented in that PHP
script. Each function is explained in more detail further below.
Listing 7. PHP script to
render geometries
<?php
function execute_queries()
{
/* determine bounds for viewBox attribute over all queries */
for ( $i = 1; $i <= 3; $i++ ) {
calculate_bounds($i, $_POST["query" . $i]);
}
/* calculate width of lines */
$lineWidth = MAX($bounds["maxx"] - $bounds["minx"],
$bounds["maxy"] - $bounds["miny"]) / 1000.0;
/* generate SVG strings */
for ( $i = 1; $i <= 3; $i++ ) {
render_geometries($i, $_POST["query" . $i], $_POST["color" . $i], $lineWidth);
}
}
function calculate_bounds($num, $query)
{
...
$boxQuery = "SELECT MIN(db2gse.ST_MinX(\"$spatialColumn[$num]\")), " .
" MIN(db2gse.ST_MinY(\"$spatialColumn[$num]\")), " .
" MAX(db2gse.ST_MaxX(\"$spatialColumn[$num]\")), " .
" MAX(db2gse.ST_MaxY(\"$spatialColumn[$num]\")) " .
"FROM ( $query ) AS t";
$stmt = db2_prepare($conn, $boxQuery, $options);
$result = db2_execute($stmt);
$row = db2_fetch_array($stmt);
...
}
function render_geometries($num, $query, $color, $width)
{
$options = array ( "cursor" => DB2_FORWARD_ONLY );
$dataQuery = "SELECT db2gse.ST_AsSVG(\"$spatialColumn[$num]\", " .
" '$color', $width) AS \"$spatialColumn[$num]\"" .
" $columnList[$num] " .
"FROM ( $query ) AS t ";
$stmt = db2_prepare($conn, $dataQuery, $options);
$result = db2_execute($stmt);
$numCols = db2_num_fields($stmt);
while ( $row = db2_fetch_both($stmt) ) {
...
}
}
require_once("connect_data.inc");
$conn = db2_connect($database, $user, $password);
execute_queries();
/* calculate 5% margin for all sides */
$xMargin = ($bounds["maxx"] - $bounds["minx"]) * 0.05;
$yMargin = ($bounds["maxy"] - $bounds["miny"]) * 0.05;
/* write complete XHTML output */
echo "<svg viewBox=\"" . ($bounds["minx"] - $xMargin). " " .
((-$bounds["maxy"]) - $yMargin) . " " .
($xRange + 2 * $xMargin) . " " . ($yRange + 2 * $yMargin) .
"\" xmlns=\"http://www.w3.org/2000/svg\" version=\"1.1\" >\n";
echo "<g transform=\"scale(1.0 -1.0)\">\n";
echo $geometriesSvgText;
echo "</g>\n";
echo "</svg>\n";
db2_close($conn);
?>
|
The first function, execute_queries, contains the main logic and has three separate steps:
- Calculate the extent in which all geometries returned by the user-provided queries
will be placed. That information is essential for SVG to set up the viewport area.
Otherwise, nothing would be shown on the screen because the SVG renderer does not
zoom-in on the area in question. The function
calculate_bounds is dedicated to that task.
- Based on the extent, you calculate the width in which lines shall be drawn. You use
1/1000 of the shorter axis of the extent, for example, either X or Y coordinates. Choosing a line width that is too small results in the lines not being visible at all, and a line width too large renders the output useless because the lines cover all geometries.
- With the extent, the line width, and the user-provided color, you can now finally
process all geometries and convert them to their SVG representation. Along the same
lines, you also collect the non-spatial attributes for each geometry and encode it in the XHTML document so that they do not have to be retrieved from DB2 whenever they are accessed. The whole conversion process is coded in function
render_geometries.
In order to compute the extent in calculate_bounds, the
user-provided queries are taken and modified as illustrated in Listing 8. The
tokens spatialColumn and query are set in italics to indicate that
those represent the spatial column (as identified by function find_spatial_column) and the user-provided query, respectively. The
result is the minimum and maximum X and Y coordinates, which define the boundaries of the extend for each query. Using those extrema, you find the minimum/maximum X/Y coordinates across all queries and, thus, have the overall extent.
Listing 8. Determining
extent for a single user-provided query.
SELECT MIN(db2gse.ST_MinX("spatialColumn")),
MIN(db2gse.ST_MinY("spatialColumn")),
MAX(db2gse.ST_MaxY("spatialColumn")),
MAX(db2gse.ST_MaxX("spatialColumn"))
FROM ( query ) AS t
|
The function find_spatial_column is not shown in Listing
7 because it is straight-forward. It takes a query as input, prepares the query and
then describes each column in the output result set. The first column of type "blob"
is used as spatial column. This is done because of transform groups and transform
functions. Whenever you try to query a spatial column, DB2 injects a transformation
from the internal, structured type representation to a representation that can be
understood by the application. You can either set a transform manually using the
SET CURRENT DEFAULT TRANSFORM GROUP statement, or you rely on the already established default transform group DB2_PROGRAM. This default transform group defines functions that convert a geometry to a BLOB. Hence, checking for the type "blob" will find any spatial column, assuming that no other BLOB columns occur in the result set.
Producing the SVG output for all geometries and processing non-spatial columns is
implemented in function render_geometries. Since we do
not require the user to specify the ST_AsSVG in the SQL
statements, it is necessary to inject a call to that function more or less
transparently. A technique similar to the one in function calculate_bounds is applied. Listing 9 shows how to transform a
user-provided query in order to add the conversion step. The elements
spatialColumn and query have the same meaning as before. The other
elements set in single quotations --color and lineWidth-- represent the
user-provided color value for each layer and the line width that you calculated before.
Listing 9. Injecting ST_AsSVG function call
SELECT db2gse.ST_AsSVG("spatialColumn," 'color,' lineWidth)
AS "spatialColumn", columnList
FROM ( query ) AS t
|
This application allows you to view the non-spatial attributes associated with each
geometry in a separate frame. To that end, all those non-spatial attributes are
processed in function render_geometries and encoded in the
XHTML code. The connection between a geometry and its non-spatial attributes is
established by "opening" the SVG element of a geometry and adding onmouseover and onmouseout attributes. Since all SVG elements are just strings, some basic string operations are sufficient, as Listing 10 demonstrates. The PHP variable $stats contains the names and values of all non-spatial attributes. Whenever you move the mouse over a geometry, the respective non-spatial attributes are passed on to a JavaScript function in the statsFrame and shown there.
Listing 10. Adding
onmouseover and onmouseout attributes to a geometry
$geometriesSvgText .= substr($svgText, 0, -2); /* remove trailing '/>' */
$geometriesSvgText .= " onmouseover=\"top.dataFrame.statsFrame.showStats($stats)\" " .
" onmouseout=\"top.dataFrame.statsFrame.clearStats()\" />\n";
|
A final word should be said on the WKT versus SVG issue. WKT and SVG define different
origins for the coordinate systems. WKT uses the lower left corner for (0, 0) and
all positive X and Y coordinates are to the right and upwards, respectively. This is
not the case for SVG where (0, 0) is the upper left corner. Thus, if the WKT
coordinates are shown unchanged in the SVG output, all geometries would be upside
down. To avoid this problem, an SVG transformation was applied to scale all
coordinates. Each coordinate value is multiplied with the given factor, and you can
specify one factor for X coordinates and another factor for Y coordinates. Thus, we choose the transformation transform="scale(1.0 -1.0)" and all geometries are flipped backwards again, giving the correct output on the screen. Note, however, that such a transformation requires a proper adjustment of the values for the SVG viewBox attribute.
Frame for non-spatial attributes
The final piece of the small Web application that I want to describe is the
frame that shows non-spatial attributes whenever you move the mouse over a particular
geometry. As mentioned before, all non-spatial attributes are already contained in
the XHTML output produced by results.php. More specifically, those attributes are
parameters for a call to a JavaScript function showStats
in the statsFrame. That frame is implemented in file stats.html and contains
basically only the JavaScript code. This code is executed in the client, such as the web browser.
It is unknown on which geometry a cursor will point at a given time. Furthermore,
users may give us multiple arbitrary (but valid) SQL queries, which implies that we
have to deal with different non-spatial attributes for each geometry. For those
reasons, we choose to implement a dynamically constructed table in that frame.
Whenever a geometry is selected, a JavaScript function is called and the names and
values for all non-spatial attributes passed to it. The function constructs the
respective DOM elements to build a table with two columns and as many rows as there are non-spatial attributes. One column holds the name and the other column of the same row shows the associated value. Once the table is constructed, it is added to the web page and, thus, becomes visible. When moving away from a geometry in the Results frame, another JavaScript function is invoked and the table is removed again.
We adopted that approach to avoid reloading the content for the frame. It avoids additional network round trips to fetch the data, and it also does not have any issues with rows being updated or deleted in the DB2 database, possibly making the shown geometry inconsistent with the later-fetched non-spatial attributes.
Summary
This article demonstrates that each modern browser can easily be used to
visualize spatial data stored in a DB2 database (either DB2 LUW or DB2 z/OS). It is
necessary to convert a geometry in the database to its SVG representation and adding
some rendering-specific attributes like color and stroke-width in the process. We
provided a user-defined function ST_AsSVG for that, which
you can install in your DB2 system and use for such tasks. The other part is to
implement an interface to accept SQL queries from the user, execute those queries and build an XHTML web page with embedded SVG elements. The SVG elements are computed by the UDF. Finally, some thoughts have to be given on how to deal with non-spatial attributes. We choose to display those attributes in a separate frame, but other approaches like tooltips are possible with some PHP and/or JavaScript coding.
Download | Description | Name | Size | Download method |
|---|
| ST_AsSVG User-Defined Function and Web Scripts | db2-svg.zip | 19KB | HTTP |
|---|
Resources
About the author | 
| | Knut started his work with DB2 when he joined IBM as a
visiting scientist at the Silicon Valley Lab, USA, in 1999, where he worked on
the DB2 Image Extender. He moved on to the DB2 Spatial Extender Development
team and was responsible for several enhancements to improve usability,
performance, and standard-conformance of the Extender. From 2002 through 2006
he was a PhD student and teaching assistant at the University of Jena,
Germany. At the same time, he continued to work for IBM on the
Information Integrator development. Today, Knut is a member of the
development team responsible for the Admin Enablement functionality on DB2 for
z/OS. He can be reached through the newsgroups comp.databases.ibm-db2 and
ibm.software.db2.udb.spatial or through email at stolze@de.ibm.com. |
Rate this page
| |
