Main menu:

Site search




Oracle und SQL


Apache Webserver becoming unresponsive

After running perfectly for nearly a year a Apache Webserver became instable. Every few hours the server wasn’t responding anymore. The MaxClients were maxed out, machine performance was normal, but no response on http.

As nothing had changed (and after some pondering on software issues), I investigated the apache connections in detail.

lsof -i :80 | grep CLOSE_WAIT

showed lots of connections in CLOSE_WAIT state coming from one IP Address.

Grepping the IP Address in apache log dir showed requests to wordpress’ xmlrpc.php. Some minor googling showed an brute force attack on wordpress installations. A fact which may have gone unnoticed, had the attackers closed their connections properly.

So I disabled xmlrpc for wordpress, and so should you!

The internet is a dangerous place …


PostGIS: find the regions with the highest density

For my perl Module Geo::Heatmap you need to find a the maximum number of point per tile (or rather per bin). You may achieve this by parsing the the logs, or use the Power of PostGIS:

To group geographically close points you may use the ST_GeoHash function:

with geohash as (
select ST_GeoHash(geom::geometry, 5) st_geohash,
from geodata where
not(St_X(geom) < -180 or St_Y(geom)< -90 or St_X(geom) > 180 or St_Y(geom) > 90)
SELECT ST_Extent(geom) as extent FROM geohash
where st_geohash =
(select st_geohash from
(select st_geohash, count(*) c
from geohash
group by st_geohash
order by c desc limit 1) max_geohash)

The number 5 used in the function denotes the length of the hash the geom points should have in common. If you group the points and order them by their count you’ll find the most densely populated regions in/of your data.

The function ST_Extent then gives you the lat/long Box containing this Points. Using only the first line of the result may not lead to the absolutly “correct” solution but for the purpose of scaling it is “good enough”

Geo::Heatmap for Google Maps

Yesterday I released a new Version of Geo::Heatmap (0.16) it should be available on the CPAN mirror of your choice
Please see my older post to the same topic

Two major changes:

  • I got rid of Image::Magik (which ain’t fun to install) and replaced it with Imager which installs easily
  • The example provided now uses the Version 3 of the Google Maps API

The example (Geo::Heatmap Demonstration) still looks the same

Coding example

The “calling” HTML

     <meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
     <style type="text/css">
       html { height: 100% }
       body { height: 100%; margin: 0; padding: 0 }
       #map-canvas { height: 100% }
     <script type="text/javascript"
     <script type="text/javascript">
       var overlayMaps = [{
         getTileUrl: function(coord, zoom) {
           return "hm.fcgi?tile="+coord.x+"+"+coord.y+"+"+zoom;

         tileSize: new google.maps.Size(256, 256),
         isPng: true,
         opacity: 0.4

       function initialize() {
         var mapOptions = {
           center: new google.maps.LatLng(48.2130, 16.375),
           zoom: 9
         var map = new google.maps.Map(document.getElementById("map-canvas"),

       var overlayMap = new google.maps.ImageMapType(overlayMaps[0]);

       google.maps.event.addDomListener(window, 'load', initialize);

     <div id="map-canvas"/>

The (f)cgi script

#!/usr/bin/env perl

use strict;
use FCGI;
use DBI;
use CHI;
use FindBin qw/$Bin/;
use lib "$Bin/../lib";

use Geo::Heatmap;

my $cache = CHI->new( driver => 'File',
         root_dir => '/tmp/domainmap'

our $dbh = DBI->connect("dbi:Pg:dbname=gisdb", 'gisdb', 'gisdb', {AutoCommit => 0});

my $request = FCGI::Request();

while ($request->Accept() >= 0) {
  my $env = $request->GetEnvironment();
  my $p = $env->{'QUERY_STRING'};

  my ($tile) = ($p =~ /tile=(.+)/);
  $tile =~ s/\+/ /g;

  # package needs a CHI Object for caching
  #               a Function Reference to get LatLOng within a Google Tile
  #               maximum number of points per zoom level

  my $ghm = Geo::Heatmap->new();

  # in this case the zoom scale is static - basically it is the count of the tile with the most values 
  $ghm->zoom_scale( {
    1 => 298983,
    2 => 177127,
    3 => 104949,
    4 => 90185,
    5 => 70338,
    6 => 37742,
    7 => 28157,
    8 => 12541,
    9 => 3662,
    10 => 1275,
    11 => 417,
    12 => 130,
    13 => 41,
    14 => 18,
    15 => 10,
    16 => 6,
    17 => 2,
    18 => 0,
  } );

  $ghm->return_points( \&get_points );
  my $image = $ghm->tile($tile);

  my $length = length($image);

  print "Content-type: image/png\n";
  print "Content-length: $length \n\n";
  binmode STDOUT;
  print $image;


sub get_points {
  my $r = shift;

  my $sth = $dbh->prepare( qq(select ST_AsEWKT(geom) from geodata
                         where geom &&
              ST_SetSRID(ST_MakeBox2D(ST_Point($r->{LATN}, $r->{LNGW}),
                                                       ST_Point($r->{LATS}, $r->{LNGE})


  my @p;
  while (my @r = $sth->fetchrow) {
    my ($x, $y) = ($r[0] =~/POINT\((.+?) (.+?)\)/);
    push (@p, [$x ,$y]);
  return \@p;

Migration Julianischer zu Gregorianischer Kalender

Migration Julianischer zu Gregorianischer Kalender


select adddate('1582-10-04', interval 1 day);
| adddate('1582-10-04', interval 1 day) |
| 1582-10-05 |


$date = new DateTime('1582-10-04');
$interval = $date->add(new DateInterval('P01D'));
echo $interval->format('Y-m-d');


>>> import datetime
>>> e =, 10, 4)
>>> delta = datetime.timedelta(days=1)
>>> print e+delta


postgres=# select to_char(to_date('15821004', 'YYYYMMDD') + 1, 'DD.MM.YYYY');
to_char ------------
05.10.1582 (1 row)


perl -MDateTime -le 'print DateTime->new(year => 1582, month => 10, day => 4)->add(days => 1)'


SQL> select to_char( to_date('1582104', 'yyyymmdd')+1, '') from dual;
TO_CHAR(TO ----------


> dtj <- strptime(c("5.10.1582"), format = "%d.%m.%Y");
> dtj + 86400
[1] "1582-10-06 CET"

Registry Data Escrow format

a downloadable/versioned Version of the Registry Data Escrow format from may be found at github

Generating Heatmap/Density Overlays for Google Maps

Quite often one wants to visualize how your customers/incidents/whatever are distributed geographically.


I’ve created a quick hack which does most of the stuff for you. The only obstacle you have to overcame is to encode all your data into long/lat format – Google Geocoder might be of some help. The coordinates have to be stored in database which allows for querying which points are within a defined long/lat box. I would recommand postgres/postgis for this task.

A real life example for Geo::Heatmap – Google Heatmaps is here

The most recent code may be found at github

Or preferably via cpan, so just have to do

cpanm Geo::Heatmap

The code still has a lot of rough corners, but if there is enough demand I’ll keep on working on it. And as everybody who sees it has some idea what he/she would like to have visualized with it, I am quite optimistic that this is going to happen. So don’t hesitate to tell me about your suggestions/bugs/fixes/etc. (admin at

Have fun, but do not forget:

XML Generation in Perl – how it should have always been

At first big thanks to Mark Overmeer for XML::Compile. I had the pleasure to meet Mark in .nl once – cheers and carry on the great work.

Whenever I had to create XML (either with perl or php) I did it one way or the other with some sort of templating toolkit. In php I use(d) for creating xml . In perl several templating systems come into mind like

  • tt2
  • Template::Declare
  • and many others more
  • Feeling Wrong

    this always felt wrong and awkward for several reasons:

  • You need to build/have the xml before (and creating xml from xsd is not one of my many hobbies)
  • You/the templating system have to take care whether or not a whole tree needs to be displayed and so on
  • The Rescue: XML::Compile

    This nifty Module comes to the rescue. Using XML::Compile make xml+xsd behave like I always wanted to.

    I want to demonstrate this using epp as an example. I want to create a valid epp frame for creating a contact object.

    The epp schemas may be obtained by a simple internet search eg here.

    You may create a valid epp frame by reading the both the contact-1.0.xsd and the epp-1.0.xsd each approx. 400 lines of thrilling xml. Or use some code like:

    1. Convert xsd to perl hash

    use strict;
    use warnings;
    use XML::Compile::Schema;
    my $schema = XML::Compile::Schema->new([
    my $s = $schema->template('PERL' => '{urn:ietf:params:xml:ns:contact-1.0}create');
    print $s;

    which gives you a more readable idea what your data should look like:

    # is a x0:createType
    { # sequence of id, postalInfo, voice, fax, email, authInfo, disclose
      # is a xs:token
      # length <= 16
      # length >= 3
      id => "token",
      # is a x0:postalInfoType
      # occurs 1 <= # <= 2 times
      postalInfo =>
      [ { # sequence of name, org, addr
          # is a xs:normalizedString
          # length <= 255
          # length >= 1
          name => "example",

    Using this perl hash template you create the first part of your epp-xml

    2. Use perl hash to xml conversion

    use strict;
    use warnings;
    use XML::Compile::Schema;
    my $schema = XML::Compile::Schema->new([
    my $write  = $schema->compile(WRITER => '{urn:ietf:params:xml:ns:contact-1.0}create');
    my $doc    = XML::LibXML::Document->new('1.0', 'UTF-8');
    my $hash = {
                  id => 'idid',
                  postalInfo => {
                    'name' => 'name', 
                    'addr' => {
                        'street' => ['street', 'street2'],
                        'city'   => 'city',
                        'cc'     => 'cc',
                    type => 'int',
                  email => 'mymail',
                  "authInfo" => {pw => "PWauthInfo"},
    my $xml    = $write->($doc, $hash);
    print $doc->toString(1);

    this leads to the following xml

    <?xml version="1.0" encoding="UTF-8"?>
    <x0:create xmlns:x0="urn:ietf:params:xml:ns:contact-1.0">
      <x0:postalInfo type="int">

    this xml has to be wrapped into an epp frame. As the epp frame uses xml any elements some “manual” work is necessary for creating the complete epp frame. In principle you start with {urn:ietf:params:xml:ns:epp-1.0}epp at step one.

    3. Generate epp-xml-frame and wrap contact-create command into it

    use strict;
    use warnings;
    use XML::Compile::Cache;
    use XML::Compile::Schema;
    my $cache = XML::Compile::Cache->new([
    my $create_contact_ns = '{urn:ietf:params:xml:ns:contact-1.0}create';
    my $epp_frame_ns = '{urn:ietf:params:xml:ns:epp-1.0}epp';
    my $prefixes = {'urn:ietf:params:xml:ns:contact-1.0' => 'contact'};
    $cache->declare(WRITER => [$create_contact_ns, $epp_frame_ns, ], 
             prefixes => $prefixes,
             use_default_namespace => 1,
             include_namespaces => 1,         
    my $doc = XML::LibXML::Document->new('1.0', 'UTF-8');
    my $contact_data = {
                    id => 'idid',
                    postalInfo => {
                      'name' => 'name', 
                      'addr' => {
                          'street' => ['street', 'street2'],
                          'city'   => 'city',
                          'cc'     => 'cc',
                      type => 'int',
                    email => 'mymail',
                    "authInfo" => {pw => "PWauthInfo"},
    my $xml = $cache->writer($create_contact_ns)->($doc, $contact_data);
    my $epp_frame_data = {
    	         command => {
                  create => {                
                    '{urn:ietf:params:xml:ns:contact-1.0}create' => $xml,
                  clTRID => "token",
    my $eppxml = $cache->writer($epp_frame_ns)->($doc, $epp_frame_data);
    $eppxml->setNamespace( '', 'xsi', 0 ); ## append additonal ns for the feelinx
    print $doc->toString(1) . 
          $eppxml->toString(1) ."n";

    Voila, we have a valid epp frame without even touching the xml! Again Kudos to Mark Overmeer for making this possible!

    4. The result

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <epp xmlns="urn:ietf:params:xml:ns:epp-1.0" xmlns:xsi="">
          <contact:create xmlns:contact="urn:ietf:params:xml:ns:contact-1.0">
            <contact:postalInfo type="int">

    Das XML der Statistik Austria

    Lobenswerterweise stellt die die Statistik-Austria sehr viele Daten zur “freien” Verfügung ins Netz. So auch alle Straßen- und Ortsnamen Österreichs. Wer sich allerdings das XML ausgedacht hat zb:

    <element>Josef Stanislaus Albach-Gasse</element>

    der hat geistig den Umstieg von csv zu xml noch nicht ganz verkraftet ;-)

    Registrar(Partner) bei der werden

    Nachdem ich ja beruflich mit einigen Registries zu tun habe – ich arbeite primär für die – kenne ich einige Zulassungsprozesse für ccTLD Registries.

    Der bei weitem “lustigste” Prozess ist der der, der Schweizer Registry. Dort sind nicht nur finanzielle und organisatorische Hürden zu nehmen (und auch die sind nicht zu knapp) sondern auch ein technischer Parcours.

    Dieser Parcours (der heißt wirklich so) besteht aus 23 epp transactionen (Querbeet, dh personen, hostobjekte, domains anlegen, löschen und so weiter) die einem Durchgang durchzuführen sind.

    Das bedeutet, daß jeder Registrar neben des Implementierungsaufwands für den normalen Clienten, sich nochmals zwei Tage (solange habe ich alles in allem gebraucht) Zeit & Geld nehmen darf um den Parcours zu implementieren. Man muß allerdings auch wirklich zugeben, wenn man den Parcour implementiert (und somit verstanden) hat, daß man auch die Prozesse der switch “durchschaut” hat.

    Scraping web pages in JavaScript with Perl

    Sometimes you want to scrape Webpages which contain JavaScript and therefore resist beeing scraped with Web::Scraper or the likes. Imagine some JavaScript code like the following to disguise a email address.

    function mail() {
    var name = "mail";
    var domain = "";
    var mailto = 'mailto:' + name + '@' + domain;

    One could use somethink elaborate like Selenium to execute the code within a browser and then extract the address with “conventional” means. There are cases when this isn’t sufficent.
    Enter JavaScript::SpiderMonkey, which allows you to execute JavaScript Code on the console without a browser. The only problem remaining is that the console doesn’t provide some properties and methods the browser has, so you have to define them yourself. This happens from line 11-14 where we define the “document” and the method “write”. The rest of the code is pretty self explanatory.

    000: use strict;
    001: use warnings;
    003: use Slurp;
    004: use JavaScript::SpiderMonkey;
    006: my $js = JavaScript::SpiderMonkey->new();
    007: my $code = slurp('mailto.js');
    009: $js->init();
    011: my $obj = $js->object_by_path("document");
    013: my @write;
    014: $js->function_set("write", sub { push @write, @_ }, $obj);
    016: my $rc = $js->eval(
    017: $code
    018: );
    020: printf "document.write:\n%s\n", join "\n", @write;
    021: printf "Error: %s\n", $@;
    022: printf "Return Code: %s\n", $rc;
    024: $js->destroy();

    The output is:
    Return Code: 1