Reporting with XSL-FO, SVG and Apache FOP

Creating High Quality, Database-Driven Reports with Open Source Software

Table of Contents

The Task
Workflow and Chosen Architecture
Data Export
A. Appendix

Reporting is an important and integral part of GIS. While the most popular output of GIS software is often in the form of maps, reports are important to document and publish non-(carto-)graphic data, such as tables (often containing statistics, summaries or aggregations) and text. They also integrate graphics, photos, charts and maps into a single document. Reporting is often done using office or DTP tools, and is not the core domain or strenght of GIS. However, in GIS related processes these tools need to be very flexible and should be able to integrate maps and charts data, as well as the output of statistics packages. The layout, design and styles of the result documents should be flexible and easily adaptable and not limited to pre-defined templates provided by a software vendor. The software and used templates have to adopt to the requirements and imaginations of a domain-expert and not vice versa.

In the given case, the task was to create reports visualizing geodata and documenting tasks related to green spaces and parks. The City of Uster outsources the planting and maintenance of green areas (including lawns, trees, bushes and flowers) to gardeners and landscapers. The city is maintaining an inventory of all the green spaces and plants it is responsible for. The data is stored in a central geodatabase. Desktop GIS is used to collect and update geographical features. Additionally, non-graphical data verbally describes the features, maintenance tasks and the people and companies involved. The reports should display the project title of the green space (e.g. a park name), the object type, the responsible company and persons, a summary of all involved areas and their object types, a list of involved individual features (such as trees, bushes, flower pots, etc.) and a verbal description of the necessary maintenance task. Photos may be used to document the green space projects and large-scale maps should visualize the features involved.

The chosen workflow and architecture should be flexible and adoptable to other reporting situations and domains, such as surveying, infrastructure maintenance (esp. roads and waste-water management), agriculture and urban planning. The chosen reporting architecture should also integrate with the existing GIS and database architecture. Data should be stored in a centralized geodata warehouse but should be accessible and maintainable in a de-centralized way - from desktop GIS, web applications or standard office software.

The GIS department of Uster decided to standardize its infrastructure around open source components, where applicable and economical. OS software has proven to be very flexible. Most components can be automated using scripting technology. Users and Developers are not forced into single vendor, closed solutions. The use of OS software also helps to accumulate IT know-how inside the department. Software can be more easily adopted to the local requirements, usually in a shorter timeframe, compared to having to wait for software updates of a proprietary vendor. Finally, the chosen solution can be used without any licence restrictions on as many workstations as required. For the long run, the chosen workflow is also more cost-effective and sustainable.

The chosen reporting architecture can be accessed both from the command line, as well as from a user-friendly graphical web application. Data is stored in a centralized geodata warehouse. The PostgreSQL database system with the Postgis extensions is used for this purpose. Any compatible desktop GIS system can be used to edit the geodata (green space areas; tree, bush and flower pot locations). In our case we use QuantumGIS, an open source desktop GIS.

The non-graphical data is exported from PostgreSQL to an intermediate XML file containing all necessary non-graphical data for the publishing system. This data is exported by a small PHP script that manages the export from all the involved database tables and the storage to the given XML data structure. The intermediate XML file is used as a data-source for the application of the XSLT transformation discussed later. Alternatively, one could have generated the XSL-FO file directly, but the intermediate XML file has the advantage that the same data-source can be used for various different transformations.

<?xml version="1.0" encoding="UTF-8"?>
  <objekt uster_nr="1.1" objekttyp="Parkanlage" svgwidth="18" svgheight="6.294" xmin="696227" ymin="244895" xmax="696513" ymax="244995" lage="Stadtpark" flaeche="11506" zustaendig="Michael Meyer" firma="Meyer Gartenbau" telefon="099-999 99 99" mobile="079-999 99 99" erstellung="-" umgestaltung="-">
   <flaeche einheit="Bodendeckende Gehölze" flaeche="35"/>
   <flaeche einheit="Bodendeckende Gehölze" flaeche="112"/>
   <flaeche einheit="Bodendeckende Gehölze" flaeche="411"/>
   <flaeche einheit="Fettwiese" flaeche="367"/>
   <flaeche einheit="Fettwiese" flaeche="1397"/>
   <flaeche einheit="Gebrauchsrasen" flaeche="550"/>
   <flaeche einheit="Gebrauchsrasen" flaeche="744"/>
   <flaeche einheit="Gebrauchsrasen" flaeche="1044"/>
   <flaeche einheit="Gebrauchsrasen" flaeche="1583"/>
   <flaeche einheit="Natursteinbeläge" flaeche="939"/>
   <flaeche einheit="Ruderalfläche" flaeche="284"/>
   <flaeche einheit="Ruderalfläche" flaeche="676"/>
   <flaeche einheit="Schwarz-/Zementbodenbeläge" flaeche="58"/>
   <flaeche einheit="Schwarz-/Zementbodenbeläge" flaeche="60"/>
   <flaeche einheit="Stehende Gewässer" flaeche="58"/>
   <flaeche einheit="Stehende Gewässer" flaeche="2302"/>
   <flaeche einheit="Wildhecke" flaeche="161"/>
   <flaeche einheit="Wildhecke" flaeche="355"/>
   <flaeche einheit="Wildhecke" flaeche="371"/>
   <art name="Laubbäume" anzahl="102">
    <unter_art name="unbekannt" anzahl="102"/>
   <beschreibung>Grosse Parkanlage mit verschieden gestalteten und genutzten Teilbereichen</beschreibung>
   <massnahme>Zugang von der Seestrasse offen halten</massnahme>
   <massnahme>Bodendecker und Sträucher beim Eingang Seestrasse abräumen und Böschung ansäen</massnahme>
   <massnahme>Niederstamm-Obstbäume fällen</massnahme>
   <massnahme>Fichte beim EW-Turm fällen</massnahme>
   <massnahme>Dornensträucher beim hinteren Spielplatz abräumen und ansäen</massnahme>
   <massnahme>Lonicera Bodendecker beim EW -Turm entfernen und Fläche ansäen</massnahme>
   <massnahme>Berichtszusammenstellung durch Planung für langfristige Entwicklung</massnahme>
   <massnahme>Spielplatz im hinteren Teil aufwerten</massnahme>
   <massnahme>Neuer Kinderspielplatz auf der Südseite vom Bach</massnahme>
   <massnahme>Ausholzen bei der Seestrasse für besseren Einblick </massnahme>
   <massnahme>Aufräumen intern besser regeln</massnahme>
   <massnahme>Blumenflor auswechseln, jäten und wässern nach Bedarf</massnahme>
   <massnahme>Rasen nach Bedarf regelmässig schneiden (6-10x),  Schnittgut entfernen, keine Düngung</massnahme>
   <massnahme>Wiesenstreifen zwischen Bach und Weg und im hinteren Teil vor der Hecke zur Spinnerei 2x schneiden</massnahme>
   <massnahme>Bäume kontrollieren, Rückschnitt nach Absprache, Lauben</massnahme>
 <!- ... more objects ...-->

There are three dynamically generated SVG files for the mapping part of the report. One SVG file delivers the map content, one displays the reference map indicating the current map extent in the context of the overall Uster region, and the last one displays the legend and the scalebar. All three files are generated serverside with small PHP scripts. For the main map, the script queries the overall bounding box of all involved features and adds a small margin around this bounding box (see SQL query in the listing below). To avoid obscure map scales, the map extent is adopted to a maximum map scale if the bounding box of the involved features is too small. The width and height of the SVG root element in the resulting map is defined in cm, according to the calculated map scale. The inner coordinate system of the viewBox is defined in real world coordinates. The symbol definitions of certain map features are stored within the defs section of the SVG files. Unfortunately, external references cannot be used, due to limitations in certain viewers. A background map of the "Übersichtsplan of the Kanton of Zürich" (map scale 1:5000) is pulled in with WMS queries. This background map serves as a reference layer. The full php source code of the map generation can be seen in the appendix.

$query = "SELECT ST_XMIN(ST_Extent(the_geom)) AS xmin, ST_YMIN(ST_Extent(the_geom)) AS ymin, ST_XMAX(ST_Extent(the_geom)) AS xmax, ";
$query .= "ST_YMAX(ST_Extent(the_geom)) AS ymax FROM natur.gehoelze_und_pflanzgefaesse WHERE uster_nr = '".$uster_nr."';";

A. Appendix

include '../resources/uster_www_connectinfo.php';
header("Content-type: image/svg+xml");
//a single green space project is defined by the uster_nr
$uster_nr = $_GET['uster_nr'];

//example URL for map generation

// db connection
$dbconn = pg_connect("host=$host dbname=$db user=$user password=$password");

//query extent of gruenflaechen (area data)
$query = "SELECT ST_XMIN(ST_Extent(the_geom)) AS xmin, ST_YMIN(ST_Extent(the_geom)) AS ymin, ST_XMAX(ST_Extent(the_geom)) AS xmax, ";
$query .= "ST_YMAX(ST_Extent(the_geom)) AS ymax FROM natur.gruenflaechen WHERE uster_nr = '".$uster_nr."';";
$result = pg_query($dbconn,$query) or die(pg_ErrorMessage());
$array = pg_fetch_array($result, NULL, PGSQL_ASSOC);

$xmin = $array['xmin'];
$xmax = $array['xmax'];
$ymin = $array['ymin'];
$ymax = $array['ymax'];

//query extent of gehoelze_und_pflanzgefaesse
$query = "SELECT ST_XMIN(ST_Extent(the_geom)) AS xmin, ST_YMIN(ST_Extent(the_geom)) AS ymin, ST_XMAX(ST_Extent(the_geom)) AS xmax, ";
$query .= "ST_YMAX(ST_Extent(the_geom)) AS ymax FROM natur.gehoelze_und_pflanzgefaesse WHERE uster_nr = '".$uster_nr."';";
$result = pg_query($dbconn,$query) or die(pg_ErrorMessage());
$array = pg_fetch_array($result, NULL, PGSQL_ASSOC);

//compare with the data extent of the point features
if (isset($array['xmin'])) {
	if ($array['xmin'] < $xmin) {
		$xmin = $array['xmin'];
	if ($array['xmax'] > $xmax) {
		$xmax = $array['xmax'];
	if ($array['ymin'] < $ymin) {
		$ymin = $array['ymin'];
	if ($array['ymax'] > $ymax) {
		$ymax = $array['ymax'];

$width = round($xmax - $xmin);
$height = round($ymax - $ymin);
$xminmin = round($xmin - $width * 0.05);
$xmaxmax = round($xmax + $width * 0.05);
$yminmin = round($ymin - $height * 0.05);
$ymaxmax = round($ymax + $height * 0.05);
$mapWidth = round($width * 1.1);
$mapHeight = round($height * 1.1);

//define fixed with of the paper map in cm
$paperwidth = 18; //in cm
$paperheight = 20; //in cm
//minimum map width in meters - corresponds to the maximum map scale
$minWidth = 100; //in meters
$minHeight = 100; //in meters

if ($mapWidth < $minWidth) {
	$centerX = $xminmin + $mapWidth * 0.5;
	$mapWidth = $minWidth;
	$xminmin = $centerX - $mapWidth * 0.5;
	$xmaxmax = $centerX + $mapWidth * 0.5;

if ($mapHeight < $minHeight) {
	$centerY = $yminmin + $mapHeight * 0.5;
	$mapHeight = $minHeight;
	$yminmin = $centerY - $mapHeight * 0.5;
	$ymaxmax = $centerY + $mapHeight * 0.5;

$ratioPaper = $paperwidth / $paperheight;
$ratioMap = $mapWidth / $mapHeight;

if ($ratioMap > $ratioPaper) {
	//case map wider than max ratio on paper
	//need to adjust width
	$svgwidth = $paperwidth;
	$svgheight = $paperwidth * ($mapHeight / $mapWidth);
else {
	//case map wider than max ratio on paper
	//need to adjust width
	$svgheight = $paperheight;
	$svgwidth = $paperheight * $ratioMap;	

//calculate symbol scale
if ($mapWidth > $mapHeight) {
	$symbolScale = $mapWidth * 0.001;
else {
	$symbolScale = $mapHeight * 0.001;

print '<?xml version="1.0" encoding="UTF-8"?>'."\n";
print '<!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 1.1//EN" "">'."\n";
print '<svg width="'.round($svgwidth,3).'cm" height="'.round($svgheight,3).'cm" viewBox="'.$xminmin.' '.($ymaxmax*-1).' '.$mapWidth.' '.$mapHeight.'" xmlns="" xmlns:xlink="" xmlns:attrib="" version="1.1" >'."\n";
//symbole für Bäume u.a.
print '<defs>'."\n";
print "\t".'<symbol id="Laubbaum" overflow="visible"><circle transform="scale('.$symbolScale.')" cx="0" cy="0" r="5" fill="chartreuse" stroke="black" stroke-width="0.5" /></symbol>'."\n";
print "\t".'<symbol id="Nadelbaum" overflow="visible"><circle transform="scale('.$symbolScale.')" r="5" fill="darkgreen" stroke="black" stroke-width="0.5" /></symbol>'."\n";
print "\t".'<symbol id="Nieststamm" overflow="visible"><circle transform="scale('.$symbolScale.')" r="4" fill="chocolate" stroke="black" stroke-width="0.5" /></symbol>'."\n";
print "\t".'<symbol id="Obstbaum" overflow="visible"><circle transform="scale('.$symbolScale.')" r="5" fill="firebrick" stroke="black" stroke-width="0.5" /></symbol>'."\n";
print "\t".'<symbol id="Pflanzgefäss" overflow="visible"><rect transform="scale('.$symbolScale.')" x="-4" y="-4" width="8" height="8" fill="red" stroke="black" stroke-width="0.5" /></symbol>'."\n";
print "\t".'<symbol id="Solitärstrauch" overflow="visible"><g transform="scale('.$symbolScale.')"><use xlink:href="#SolitärsträucherInnen" stroke="white" stroke-width="6"/><g stroke="limegreen" stroke-width="2"><g id="SolitärsträucherInnen"><line x1="-7" x2="7" y1="0" y2="0"/><line x1="0" x2="0" y1="7" y2="-7"/><line x1="-5.5" x2="5.5" y1="-5.5" y2="5.5"/><line x1="5.5" x2="-5.5" y1="-5.5" y2="5.5"/></g></g></g></symbol>'."\n";
print '</defs>'."\n";
$url = "http://localhost/cgi/mapserv?map=/home/www/mapserverdata/;SERVICE=WMS&amp;VERSION=1.1.1&amp;REQUEST=GetMap&amp;LAYERS=uep8lmmgray&amp;SRS=EPSG:21781&amp;BBOX=".$xminmin.",".$yminmin.",".$xmaxmax.",".$ymaxmax."&amp;FORMAT=image/png&amp;WIDTH=".round($svgwidth*80)."&amp;HEIGHT=".round($svgheight*80)."&amp;STYLES=";
print '<image opacity="0.5" xlink:href="'.$url.'" x="'.$xminmin.'" y="'.($ymaxmax*-1).'" width="'.$mapWidth.'" height="'.$mapHeight.'" />'."\n";
//extract aeras
print "\t".'<g stroke="none" fill-opacity="0.75">'."\n";

$query = "SELECT gr.gid, gr.einheit, ST_Area(gr.the_geom) AS groesse, gr.bemerkung, gr.uster_nr,";
$query .= " gr.uster_bez, gr.lage, gr.unterhalt, gr.anlage, AsSVG(the_geom,0,2) AS svg, da.colorname AS farbe";
$query .= " FROM natur.gruenflaechen gr, natur.gruenflaechen_darstellung da WHERE uster_nr = '".$uster_nr."' AND gr.einheit = ORDER BY gr.einheit ASC;";

$result = pg_query($dbconn,$query) or die(pg_ErrorMessage());
#print "<!-- $query -->\n";

while ($rec = pg_fetch_array($result, null, PGSQL_ASSOC)) {
	print "\t\t".'<path id="gruenflaeche_'.$rec['gid'].'" fill="'.$rec['farbe'].'" attrib:flaeche="'.round($rec['groesse']).'" attrib:einheit="'.$rec['einheit'].'" attrib:unterhalt="'.$rec['unterhalt'].'" d="'.$rec['svg'].'" />'."\n";

print "\t".'</g>'."\n";

// Free resultset

//extract trees
print "\t".'<g id="baeume_und_straeucher">'."\n";

$query = "SELECT gid, AsSVG(the_geom,1,2) AS svg, einheit FROM natur.gehoelze_und_pflanzgefaesse WHERE uster_nr = '".$uster_nr."';";

$result = pg_query($dbconn,$query) or die(pg_ErrorMessage());

while ($rec = pg_fetch_array($result, null, PGSQL_ASSOC)) {
	print "\t\t".'<use id="gehoelz_'.$rec['gid'].'" '.$rec['svg'].' xlink:href="#'.$rec['einheit'].'" />'."\n";

print "\t".'</g>'."\n";

// Free resultset

// Closing connection

print '</svg>'."\n";