Files used

Right-click, save ...

• mysql_svg_perl.shtml
• mysql_svg_perl.perl (set DB-connections after download)
• mysql_svg_perl.js
• mysql_countrynames.sql
• mysql_countrygeom.sql

Technical Details

Inspired by André Winter's SVG-MySQL-PHP example I've decided to take his code and try to do something similar using Perl instead of PHP and well, the resulting example is quite straight forward: define an html input-form with a select group holding countries, parse the user selection with a small javascript that opens a new window and sends the resulting query to a Perl script, query your MySQL database using Perl's DBI Module and generate SVG code according to the database results.

If you have a closer look at the example you'll find some extra features like: automatic re-sizing & re-centering of the SVG viewport according to selected countries, or onmouseover/out effects that result in highlighting of the specific country. At the same time the country's name & flag is displayed in the left corner. The Perl script itself may also be called via query_string if you want to establish links to "selections" that won't change (e.g.: <a href="script.pl?query=at_ch">my neighbours</a>)

The tiny Javascript mysql_svg_perl.js that parses the user selection and opens a new window should be "self explaining" and can be download following the link above. You may also have a look at the DB table description and download SQL-files from there. Lets's start with the complete Perl code first and walk through the description afterwards. Perl code is colored in red, comments in teal, JavaScript in green, SQL related statments in mauve while regular SVG is in blue.

001  #!/usr/bin/perl -w
002  #
003  # get geometry from mysql db and create svg for selected countries
004  # klaus.foerster@uibk.ac.at, v0.1b
005  ########################################################################
006  
007  use DBI;
008  use CGI;
009  use strict;
010  
011  my $q = new CGI;
012  
013  my (@domains, $geom);
014  my $width  = "480";
015  my $height = "360";
016  
017  # check for query_string
018  if (!$q->param('query')) {
019  	print $q->header();
020  	print "Please select at least one country :(\n";
021  	exit (0);
022  }
023  
024  # parse query
025  @_ = split(/_/, $q->param('query'));
026  
027  for my $dom (@_) {
028  	$dom =~ m/^[a-z]{2}$/ or next;
029  	push @domains, "countrygeom.tld='$dom'";
030  }
031  if (!@domains) {
032  	print $q->header();
033  	print "Sorry, incorrect query string :(\n";
034  	exit (0);
035  }
036  
037  # generate sql query, connect to database and execute query
038  my $sql  = qq{ select countrygeom.tld, countrygeom.geom, countrynames.cnameen };
039  	$sql .= qq{ from countrygeom, countrynames };
040  	$sql .= qq{ where countrygeom.tld = countrynames.tld and (};
041  	$sql .= join " or ", @domains;
042  	$sql .= ")";
043  
044  my $dbh = DBI->connect("dbi:mysql:database=XXX;host=XXX","XXX","XXX");
045  if ( !defined $dbh ) {
046  	local $SIG{__DIE__} = sub {
047  		print STDERR "[" .scalar localtime (time) . "] [error]
                           [client $ENV{REMOTE_HOST}] ";
048  		print STDERR $DBI::errstr . " ";
049  		print $q->header();
050  		print $DBI::errstr;
051  		print "<p>Sorry. Database could not be reached. 
                           Please try again later<br />\n";
052  	};
053  	die;
054  }
055  my $sth = $dbh->prepare($sql);
056  $sth->execute || execute_failed($DBI::errstr);
057  
058  # get results and create svg-code
059  while (my @row = $sth->fetchrow) {
060  	$geom .= qq*<path id="$row[0]"*;
061  	$geom .= qq* onmouseover="showName('$row[0]','$row[2]')"
                           onmouseout="clearName('$row[0]')"*;
062  	$geom .= qq* class="fil3 landpolygone" d="$row[1]" />\n*;
063  }
064  
065  # anything found?
066  $geom or execute_failed("no geometry found for your query");
067  
068  # write svg-code
069  print $q->header("image/svg+xml");
070  print qq*<?xml version="1.0" encoding="iso-8859-1"?>
071  <!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 20010904//EN"
072  	"https://www.w3.org/TR/2001/REC-SVG-20010904/DTD/svg10.dtd">
073  <svg id="mainmap" xml:space="preserve" style="shape-rendering:geometricPrecision;
074  	text-rendering:geometricPrecision; image-rendering:optimizeQuality"
075  	width="$width" height="$height" viewBox="-6148 0 11425 8275" onload='init(evt)'>
076  <script>
077  <![CDATA[
078  	var myX;
079  	var myY;
080  	var myScale;
081  
082  function init(evt){
083  	var bbox = document.getElementById('countrygroup').getBBox();
084  	var viewboxvals=bbox.x+' '+bbox.y+' '+bbox.width+' '+bbox.height;
085  	document.getElementById('mainmap').setAttributeNS(null,'viewBox',viewboxvals);
086  	if ((bbox.width/bbox.height) >= ($width/$height)) {
087  		myScale = $width/bbox.width; // viewbox narrower than viewport
088  		myX = bbox.x;
089  		myY = bbox.y+(bbox.height/2)+((bbox.width\*$height )/(2\*$width));
090  	}
091  	else { // viewbox straiter as viewport
092  		myScale = $height/bbox.height;
093  		myX = bbox.x+(bbox.width/2)-((bbox.height\*$width)/(2\*$height));
094  		myY = bbox.y+bbox.height;
095  	}
096  }
097  
098  function showName(tld,country) {
099  	var t = document.getElementById('tldtext');
100  	var p = document.getElementById(tld);
101  	var c = document.getElementById('tldtext');
102  	var i = document.getElementById('flag');
103  
104  	t.setAttributeNS(null,'x',myX+50/myScale);
105  	t.setAttributeNS(null,'y',myY-10/myScale);
106  	t.setAttributeNS(null,"font-size",(30/myScale)+"px");
107  	p.setAttributeNS(null,"stroke-width",1/myScale);
108  
109  	c = c.firstChild;
110  	c.nodeValue = country;
111  
112  	i.setAttributeNS(null,'x',myX+10/myScale);
113  	i.setAttributeNS(null,'y',myY-30/myScale);
114  	i.setAttributeNS(null,'width',eval(30/myScale));
115  	i.setAttributeNS(null,'height',eval(20/myScale));
116  	i.setAttributeNS('https://www.w3.org/1999/xlink','href','/srv/img/flags/'+tld+'.png');
117  }
118  
119  function clearName(tld) {
120  	document.getElementById(tld).setAttributeNS(null,"stroke-width",0.1);
121  	t = document.getElementById('tldtext')
122  	t = t.firstChild;
123  	t.nodeValue = ' ';
124  }
125  function clearFlag() {
126  	var i = document.getElementById('flag');
127  	i.setAttributeNS('https://www.w3.org/1999/xlink','href','/srv/img/flags/white.png');
128  }
129  ]]>
130  </script>
131  
132  <desc>This is a country selector</desc>
133  <defs>
134   <style type="text/css">
135    <![CDATA[
136      .fil3 {fill:#009240;fill-opacity:0.15}
137      .landpolygone {stroke:#999999;stroke-width:0.1}
138    ]]>
139   </style>
140  </defs>
141  <rect id="background" x="-6148" y="0" width="11425" height="8275"
142  	onmouseover="clearFlag()" style="fill:white;stroke:none" />
143  <g id="countrygroup">
144  $geom
145  </g>
146  <image id="flag" xlink:href="flags/white.png" x="0" y="0" width="300" height="200" />
147  <text id="tldtext" x="0" y="0" style="font-size:0;">Names go here ...</text></svg>
148  *;
149  
150  # error handling ...
151  sub execute_failed {
152  	print $q->header();
153  	my $error = shift;
154  	print "Error: $error<br />\n";
155  }

Lines 7 to 9 define which Perl modules are needed, in our case: DBI for handling Database actions & CGI for parsing the query and sending the header. Both modules can be found at cpan.perl.org, the Comprehensive Perl Archive Network and if you want information on MySQL & DBI just have a look at the according https://www.mysql.com/doc/en/Perl_DBI_Class.html.

Lines 11 to 35 parse the input, define global variables like $width & $height of our svg document or variables that are needed later (13 to 15), check if a query has been sent at all (17 to 22), split up the query_string into it's components (25) and fill an array that holds our first "sql statements" (at least a part of it) in the form of countrygeom.tld='$dom' (27 to 30) where countrygeom is the name of our MySQL table, tld the name of our MySQL variable and $dom the according top level domain abreviation gained from the query. If we're done with parsing and get a query "we can understand" the program continues, otherwise it ends with "sorry incorrect query string".

Lines 37 to 42 define the actual sql query, combining sql statements we've generated in line 29 with logical "or" and adding them to our where clause. This results in strings like countrygeom.tld=at or countrygeom.tld=ch or countrygeom.tld=de and is necessary to be able to search for more than one country.

Lines 44 to 58 connect to the database using dbname, dbhost, dbuser & dbpass as parameters (44), abort the program if the database can not be reached (45 to 54), prepare and execute the query (55 - 56) and abort the program if execution of our sql statement fails.

Lines 58 to 66 parse the result (59), generate the <path ../> with onmouseover, onmouseout events & style and append this code to our global $geom variable (60 to 62). If you want to add things like xlink:href, animations or what so ever bound to each country shape - here's the place to do it. Line 66 examines whether $geom holds any values and aborts if $geom is empty (i.e. specified domain may be valid but not in our database, like e.g. za for South Africa).

Line 69 sends the image/svg+xml header and the rest of the code (except the last 5 lines that handle errors for the whole program) are nedded to print out the resulting svg code. Well, no Perl needed here except the qq* .... * operator that allows you to get rid of escaping all the quotes (") in svg (although you have to be careful not to forget to escape all the stars (*) in your document.

Lines 69 to 75 contain the document's header, establish an initial viewport and define that init(evt) will be called when the svg is loaded.

Lines 76 to 130 contain four javascript functions that handling the following tasks:

• init(evt): set global variables, re-size & re-center the viewbox according to selected countries
• showName(tld,country): highlight country polygon and display country name and flag onmouseover
• clearName(tld): return to original polygon style and remove country name onmouseout
• clearFlag(): remove flag if mouse is outside of country geometries

Lines 134 to 139 contain style definitions

Lines 141 to 147 contain the actual geometry as well as text and image that will be replaced by javascript functions showName(tld,country) and clearName(tld).

---

To access the database you need to know what is in the database tables. See below for the two used tables. If you want to build those tables with content you may use countrynames.sql for the bilingual names and countrygeom.sql for geometry.

mysql> show columns from countrygeom;
+-------+-----------------+------+-----+---------+----------------+
| Field | Type            | Null | Key | Default | Extra          |
+-------+-----------------+------+-----+---------+----------------+
| cid   | int(8) unsigned |      | PRI | NULL    | auto_increment |
| tld   | char(2)         |      |     |         |                |
| geom  | text            | YES  |     | NULL    |                |
+-------+-----------------+------+-----+---------+----------------+

mysql> select * from countrygeom;
+-----+-----+---------------------------------------------------------
| cid | tld | geom
+-----+-----+---------------------------------------------------------
|   1 | yu  | M-1431 6308l-3 -14 9 -8 7 6c-4,6 -6,13 -13,16z -12,1....
|   2 | ua  | M-2337 6531l4 -22 0 -4 -33 -14 -15 14 -1 29 9 2 29,2....
|  .  |  .  |    .
|  .  |  .  |    .
|  .  |  .  |    .

mysql> show columns from countrynames;
+---------+-----------------+------+-----+---------+----------------+
| Field   | Type            | Null | Key | Default | Extra          |
+---------+-----------------+------+-----+---------+----------------+
| cid     | int(8) unsigned |      | PRI | NULL    | auto_increment |
| tld     | char(2)         |      |     |         |                |
| cnameen | char(128)       | YES  |     | NULL    |                |
| cnamede | char(128)       | YES  |     | NULL    |                |
+---------+-----------------+------+-----+---------+----------------+

mysql> select * from countrynames;
+-----+-----+------------------------+-------------------------+
| cid | tld | cnameen                | cnamede                 |
+-----+-----+------------------------+-------------------------+
|   1 | yu  | Yougoslavia            | Jugoslawien             |
|   2 | ua  | Ukraine                | Ukraine                 |
|   3 | uk  | United Kingdom         | Gross Britannien        |
|   4 | tr  | Turkey                 | Turkei                  |
|   5 | sm  | San Marino             | San Marino              |
|   6 | sk  | Slovakia               | Slowakei                |
|   7 | si  | Slovenia               | Slowenien               |
|  .  |  .  |    .                   |   .                     |
|  .  |  .  |    .                   |   .                     |
|  .  |  .  |    .                   |   .                     |