November 26, 2015

Add text to a layer create a new transparent layer below the text layer select text, in text layer right-click on ‘text to path select transparent layer click “Select > From Path” and click [...]

November 24, 2015

We use Semaphore for automated testing. Just push changes and the tests are automatically run. Great. But there is a caveat as the test suite grows. Semaphore kills processes that run longer than an hour. At this point we integrated parallel_tests to reduce runtime. The result is great, overall runtime decreased to about one third. […]

November 23, 2015

So you’re the guy who is allowed to setup a local DBpedia mirror or more generally a local Linked Data mirror for your work group? OK, today is your lucky day and you’re in the right place. I hope you’ll be able to benefit from my many hours of trials and errors. If anything goes wrong (or everything works fine), feel free to leave a comment below.

Versions of this guide

There are four older versions of this guide:

  • Oct. 2010: The first version focusing on DBpedia 3.5 – 3.6 and Virtuoso 6.1
  • May 2012: A bigger update to DBpedia 3.7 (new local language versions) and Virtuoso 6.1.5+ (with a lot of updates making pre-processing of the dumps easier)
  • Apr. 2014: Update to DBpedia 3.9 and Virtuoso 7
  • Nov. 2011: Update to DBpedia 2014 and other Datasets and Virtuoso 7.1.0

In this step by step guide I’ll tell you how to install a local Linked Data mirror of the DBpedia 2015-04, hosting a combination of the regular English and (exemplary) the i18n German datasets adding up to nearly 850 M triples.

I’ll also mention how you can add the following datasets / vocabularies adding up to nearly 6 G triples:

As DBpedia is quite modular and has many internationalized (i18n) versions it has its own section in this guide, the other datasets don’t, as they maximally need minor repacking and a single line to load as explained below.

Used Versions

  • DBpedia 2015-04
  • Virtuoso OpenSource 7.2.1
  • Ubuntu 14.04 LTS or Debian 8


A strong machine with root access and enough RAM: We used a VM with 4 Cores and 32 GBs of RAM for DBpedia only. If you intend to also load Freebase and other datasets i recommend at least 64 GBs of RAM (we actually ended up using a 16 Core, 256 GB RAM Server in our research group). For installing i recommend more than 128 GB free HD space for DBpedia alone, 512 GB if you want to load Freebase as well, especially for downloading and repacking the datasets, as well as the growing database file when importing (mine grew to 64 GBs for DBpedia and 320 GB with all the datasets mentioned above).

For the impatient and docker affine

As an alternative to the following sections, which will explain how to build everything from source yourself and go into details about the DBpedia dump files, i also provide a docker image (source) that you can use to automate and simplify the process a lot:

mkdir -p "$dump_dir"
cd "$dump_dir"

# downloading
wget -r -nc -nH --cut-dirs=1 -np -l1 \
    -A '*.nt.bz2' -A '*.owl' -R '*unredirected*' \

# repacking
apt-get install pigz pbzip2
for i in */*.nt.bz2 ; do echo $i ; pbzip2 -dc "$i" | pigz - > "${i%bz2}gz" && rm "$i"; done
mkdir classes
cd classes

# install some VAD packages for DBpedia into our db which we'll keep in db_dir
docker run -d --name dbpedia-vadinst \
    -v "$db_dir":/var/lib/virtuoso-opensource-7 \
    joernhees/virtuoso run &&
docker exec dbpedia-vadinst wait_ready &&
docker exec dbpedia-vadinst isql-vt PROMPT=OFF VERBOSE=OFF BANNER=OFF \
    "EXEC=vad_install('/usr/share/virtuoso-opensource-7/vad/rdf_mappers_dav.vad');" &&
docker exec dbpedia-vadinst isql-vt PROMPT=OFF VERBOSE=OFF BANNER=OFF \
    "EXEC=vad_install('/usr/share/virtuoso-opensource-7/vad/dbpedia_dav.vad');" &&
docker stop dbpedia-vadinst &&
docker rm -v dbpedia-vadinst &&

# starting the import
docker run --rm \
    -v "$db_dir":/var/lib/virtuoso-opensource-7 \
    -v "$dump_dir"/classes:/import:ro \
    joernhees/virtuoso import '' &&
# docker import of the actual data (will use 64 GB RAM and take about 1 hour)
docker run --rm \
    -v "$db_dir":/var/lib/virtuoso-opensource-7 \
    -v "$dump_dir"/core:/import:ro \
    -e "NumberOfBuffers=$((64*85000))" \
    joernhees/virtuoso import '' &&

# running the local endpoint on port 8891 with 32 GB RAM:
docker run --name dbpedia \
    -v "$db_dir":/var/lib/virtuoso-opensource-7 \
    -p 8891:8890 \
    -e "NumberOfBuffers=$((32*85000))" \
    joernhees/virtuoso run

# access one of the following for example:
# http://localhost:8891/sparql
# http://localhost:8891/resource/Bonn
# http://localhost:8891/conductor (user: dba, pw: dba)

The manual version

Download and build Virtuoso

We’ll download Virtuoso OpenSource: either from SourceForge or GitHub (make sure you get v7.2.1 as in this guide or a newer version).

Unlike in earlier versions of this guide we’ll now first build the .deb packages and then install them with apt-get.

As building will install a lot of extra packages that you only need for building, i prepared another docker image (source) that will do the whole building job inside a container for you and put the resulting .deb packages (and DBpedia VAD) into your ~/virtuoso_deb folder:

docker run --rm -it -v ~/virtuoso_deb:/export/ joernhees/dpkg_build \ \
# this should run for about 15 minutes
# compilation by default sadly does not create the dbpedia VAD package, so
# to do that, the above command stops after compilation in interactive mode.
# in there just execute this:
cd /tmp/build/virtuoso*/ &&
./configure --with-layout=debian --enable-dbpedia-vad &&
cd binsrc &&
make &&
cp dbpedia/dbpedia_dav.vad /export &&

If you used this, you can skip the following down to installing the .deb packages.

If not, to do the building manually run this to download the file, put it in your home dir on the server, then extract it and switch to the directory:

mkdir ~/virtuoso_deb
cd ~/virtuoso_deb
tar -xvzf virtuoso-7.2.1.tar.gz
cd virtuoso-opensource-7.2.1  # or newer, depending what you got

Afterwards you can use the following to install the build dependencies and actually build the .deb packages:

# install build tools
sudo apt-get install -y build-essential devscripts
# to install Virtuoso build dependencies
mk-build-deps -irt'apt-get --no-install-recommends -yV' && dpkg-checkbuilddeps
# to build Virtuoso with 5 processes in parallel
# choose something like your server's #CPUs + 1
dpkg-buildpackage -us -uc -5

This will take about 15 min.
Afterwards if everything worked out, you should have the *.deb files in ~/virtuoso_deb.

We continue to also build the DBpedia VAD:

./configure --with-layout=debian --enable-dbpedia-vad && \
cd binsrc && make \
cp dbpedia/dbpedia_dav.vad ~/virtuoso_deb/

Finally, let’s create a small local repository out of the .deb files you just built. The advantage of this is that you can simply install virtuoso-server with its dependencies with apt. In theory you could also resolve them manually and install everything with dpkg -i ..., but where’s the fun in that?

cd ~/virtuoso_deb
dpkg-scanpackages ./ | gzip > Packages.gz

Installing Virtuoso

No matter if you used the docker or manual building approach for the .deb packages of Virtuoso, you should now be able to install them with apt-get install ... after telling it where to look for the files for example by doing this:

sudo echo "deb file:~/virtuoso_deb ./" >> /etc/apt/sources.list.d/virtuoso_local_packages.list
sudo apt-get update

After this just install Virtuoso with the following command (it should warn you about untrusted sources of the Virtuoso packages, which is because we just built them ourselves):

sudo apt-get install virtuoso-server \
  virtuoso-vad-bpel \
  virtuoso-vad-conductor \
  virtuoso-vad-demo \
  virtuoso-vad-doc \
  virtuoso-vad-isparql \
  virtuoso-vad-ods \
  virtuoso-vad-rdfmappers \
  virtuoso-vad-sparqldemo \
  virtuoso-vad-syncml \

The above will ask you for a DBA password. Please pick one.

Installing the VAD packages here will actually not install them in the Virtuoso DB file, but just move them in the right place so they can for example be installed as mentioned later.

To also move the DBpedia VAD in place for later you can just run this:

sudo cp ~/virtuoso_deb/dbpedia_dav.vad /usr/share/virtuoso-opensource-7/vad/

Configuring Virtuoso

Now change the following values in /etc/virtuoso-opensource-7/virtuoso.ini, the performance tuning stuff is according to

# note: Virtuoso ignores lines starting with whitespace and stuff after a ;
# you need to include the directory where your datasets will be downloaded
# to, in our case /usr/local/data/datasets:
DirsAllowed = ., /usr/share/virtuoso/vad, /usr/local/data/datasets
# IMPORTANT: for performance also do this
# the following two are as suggested by comments in the original .ini
# file in order to use the RAM on your server:
NumberOfBuffers = 2720000
MaxDirtyBuffers = 2000000
# each buffer caches a 8K page of data and occupies approx. 8700 bytes of
# memory. It's suggested to set this value to 65 % of ram for a db only server
# so if you have 32 GB of ram: 32*1000^3*0.65/8700 = 2390804
# default is 2000 which will use 16 MB ram ;)
# Make sure to remove whitespace if you uncomment existing lines!
MaxCheckpointRemap = 625000
# set this to 1/4th of NumberOfBuffers
# I like to increase the ResultSetMaxrows, MaxQueryCostEstimationTime
# and MaxQueryExecutionTime drastically as it's a local store where we
# do quite complex queries... up to you (don't do this if a lot of people
# use it).
# In any case for the importer to be more robust add the following setting
# to this section:
ShortenLongURIs = 1

Afterwards restart Virtuoso:

sudo /etc/init.d/virtuoso-opensource-7 stop

You should now have a running Virtuoso server.

DBpedia URIs (en) vs. DBpedia IRIs (i18n)

The DBpedia 2015-04 consists of several datasets: one “standard” English version and several localized versions for other languages (i18n). The standard version mints URIs by going through all English Wikipedia articles. For all of these the Wikipedia cross-language links are used to extract corresponding labels in other languages for the en URIs (e.g., core/labels-en-uris_de.nt.bz2). This is problematic as for example articles which are only in the German Wikipedia won’t be extracted. To solve this problem the i18n versions exists and create IRIs in the form of for every article in the German Wikipedia (e.g., core-i18n/de/labels_de.nt.bz2).

This approach has several implications. For backwards compatibility reasons the standard DBpedia makes statements about URIs such as while the local chapters, like the German one, make statements about IRIs such asöder (note the ö). In other words and as written above: the standard DBpedia uses URIs to identify things, while the localized versions use IRIs. This also means thatöder shouldn’t work. That said, clicking the link will actually work as there is magic going on in your browser to give you what you probably meant. Using curl curl -i -L -H "Accept: application/rdf+xml"öder or SPARQLing the endpoint will nevertheless not be so nice/sloppy and can cause quite some headache. Observe how the following two SPARQL queries return different results: select * where { dbpedia:Gerhard_Schröder ?p ?o. } vs. select * where { <> ?p ?o. }. In order to mitigate this historic problem a bit DBpedia actually offers owl:sameAs links from IRIs to URIs: core/iri-same-as-uri_en.nt.bz2 which you should load, so you at least have a link to what you want if someone tries to get info about an IRI.

As the standard DBpedia provides labels, abstracts and a couple other things in several languages, there are two types of files in the localized DBpedia folders: There are triples directly associating the English URIs with for example the German labels ({core,core-i18n/de}/labels-en-uris_de.nt.bz2) and there are the localized triple files which associate for example the DE IRIs with the German labels (core-i18n/de/labels_de.nt.bz2).

Downloading the DBpedia dump files, de-duplication & Repacking

For our group we decided that we wanted a reasonably complete mirror of the standard DBpedia (EN) (have a look at the core directory, which contains all datasets loaded into the public DBpedia SPARQL Endpoint), but also the i18n versions for the German DBpedia loaded in separate graphs, as well as each of their pagelink datasets in yet another separate graph each. For this we download the corresponding files in (NT) format as follows. If you need something different do so (and maybe report back if there were problems and how you solved them).

# see comment above, you could also get another DBpedia version...
mkdir -p /usr/local/data/datasets/dbpedia/2015-04
cd /usr/local/data/datasets/dbpedia/2015-04
wget -r -nc -nH --cut-dirs=1 -np -l1 -A '*.nt.bz2' -A '*.owl' -R '*unredirected*'{core/,core-i18n/en,core-i18n/de,dbpedia_2015-04.owl}

As already mentioned, the DBpedia 2015-04 introduced a core folder which contains all files loaded on the public DBpedia endpoint. Be aware that if you download other folders like above you’ll be downloading some files twice in other folders (e.g., labels-en-uris_de.nt.bz2 can be found in both, the core folder and the core-i18n/de folder). Quite obvious, but especially the core-i18n/en folder contains very many duplicate files from core. If want to see which downloaded files are duplicates (independent of their name) and especially which core-i18n/en files were not loaded on the public endpoint, so are not in core, you can do the following:

# compute md5 hashes for all downloaded files
find . -mindepth 2 -type f -print0 | xargs -0 md5sum > md5sums

# first check if there are duplicates in other folders without core
LC_ALL=C sort md5sums | grep -v '/core/' | uniq -w32 -D
ba3fc042b14cb41e6c4282a6f7c45e02  ./core-i18n/en/instance-types-dbtax-dbo_en.nt.bz2
ba3fc042b14cb41e6c4282a6f7c45e02  ./core-i18n/en/instance_types_dbtax-dbo.nt.bz2

So it seems the ./core-i18n/en/instance-types-dbtax-dbo_en.nt.bz2 and ./core-i18n/en/instance_types_dbtax-dbo.nt.bz2 files are actually the same.

To list all the files in core-i18n/en which are duplicates do this:

# list all dup files in core-i18n/en
LC_ALL=C sort md5sums | uniq -w32 -D | grep '/core-i18n/en'
068975f6dd60f29d13c8442b0dbe403d  ./core-i18n/en/skos-categories_en.nt.bz2
14a770f293524a5713f741a1a448bcfa  ./core-i18n/en/short-abstracts_en.nt.bz2
1904ad5bc4579fd7efe7f40673c32f79  ./core-i18n/en/specific-mappingbased-properties_en.nt.bz2
1958649209bc90944c65eccd30d37c6c  ./core-i18n/en/infobox-property-definitions_en.nt.bz2
2774d36ce14e0143ca4fa25ed212a598  ./core-i18n/en/external-links_en.nt.bz2
314162db2acb516a1ef5fcb3a2c7df2b  ./core-i18n/en/geonames_links_en.nt.bz2
3b42f351fc30f6b6b97d3f2a16ef6db3  ./core-i18n/en/instance-types-transitive_en.nt.bz2
3b61b11bdcb50a0d44ca8f4bd68f4762  ./core-i18n/en/revision-ids_en.nt.bz2
43a8b17859c50d37f4cab83573c2992e  ./core-i18n/en/instance_types_sdtyped-dbo_en.nt.bz2
4c847b2754294c555236d09485200435  ./core-i18n/en/instance-types_en.nt.bz2
63e2cde88e7bdefb6739c62aa234fc1e  ./core-i18n/en/category-labels_en.nt.bz2
64cbbac14769aadf560496b4d948d5e1  ./core-i18n/en/interlanguage-links-chapters_en.nt.bz2
75f2d135459c824feee1d427e4165a4f  ./core-i18n/en/transitive-redirects_en.nt.bz2
82fe80c3868a89d54fec26c919a4fa50  ./core-i18n/en/revision-uris_en.nt.bz2
8407c84d262b573418326bdd8f591b95  ./core-i18n/en/mappingbased-properties_en.nt.bz2
87df057913a05dbb5666f360d20fa542  ./core-i18n/en/freebase-links_en.nt.bz2
8cc921fbab5d02ad83b1fda2f87c23f0  ./core-i18n/en/wikipedia-links_en.nt.bz2
9152e34db96df2dd4991e78b7e53ff3f  ./core-i18n/en/article-categories_en.nt.bz2
94b48e9df78f746e60a9d0c1aafa3241  ./core-i18n/en/infobox-properties_en.nt.bz2
a254ce4596d045cc047959831edd318a  ./core-i18n/en/disambiguations_en.nt.bz2
ab29899e43fab1c6f060cdb8955c5b19  ./core-i18n/en/images_en.nt.bz2
ae046e03be0cf29eac1e3b8a8b3d6b03  ./core-i18n/en/persondata_en.nt.bz2
b4710d36b8dc915f07f5cec2d9971a27  ./core-i18n/en/page-ids_en.nt.bz2
ba3fc042b14cb41e6c4282a6f7c45e02  ./core-i18n/en/instance-types-dbtax-dbo_en.nt.bz2
ba3fc042b14cb41e6c4282a6f7c45e02  ./core-i18n/en/instance_types_dbtax-dbo.nt.bz2
bd90ce4064a120794b5eb5a8d024a97d  ./core-i18n/en/long-abstracts_en.nt.bz2
e4c422d1d23c69eff3b9d7d7df3f2f80  ./core-i18n/en/homepages_en.nt.bz2
eafc557cde69fd1cd8f78565c385ee16  ./core-i18n/en/iri-same-as-uri_en.nt.bz2
ef48deae48c9c9c5e17585e3f0243663  ./core-i18n/en/labels_en.nt.bz2
fa8800165c7e80509a4ebddc5f0caf90  ./core-i18n/en/geo-coordinates_en.nt.bz2

# to delete the duplicates from /core-i18n/en, leaving just one of each:
LC_ALL=C sort md5sums | uniq -w32 -D | grep '/core-i18n/en' | uniq -w32 | cut -d' ' -f3 | xargs rm

# afterwards these should be left:
ls -1 core-i18n/en

As Virtuoso can only import plain (uncompressed) or gzipped files, but the DBpedia dumps are bzipped, you can either repack them into gzip format or extract them. On our server the importing procedure was reasonably slower from extracted files than from gzipped ones (ignoring the vast amount of wasted disk space for the extracted files). File access becomes a bottleneck if you have a couple of cores idling. This is why I decided on repacking all the files from bz2 to gz. As you can see I do the repacking with the parallel versions of bz2 and gz. If that’s not suitable for you, feel free to change it. You might also want to change this if you want to do it in parallel to downloading. The repackaging process below took about 30 minutes but was worth it in the end. The more CPUs you have, the more you can parallelize this process.

# if you want to save space do this:
apt-get install pigz pbzip2
for i in core/*.nt.bz2 core-i18n/*/*.nt.bz2 ; do echo $i ; pbzip2 -dc "$i" | pigz - > "${i%bz2}gz" && rm "$i" ; done

# else do:
#pbzip2 */*.bz2

# notice that the extraction (and repacking) of *.bz2 takes quite a while (about 30 minutes)
# gzipped data is reasonably packed, but still very fast to access (in contrast to bz2), so maybe this is the best choice.

Data Cleaning and The bulk loader scripts

In contrast to the previous versions of this article the Virtuoso import will take care of shortening too long IRIs itself. Also it seems the bulk loader script is included in the more recent Virtuoso versions, so as a reference only: see the old version for the cleaning script and VirtBulkRDFLoaderExampleDbpedia and
for info about the bulk loader scripts.

Importing DBpedia dumps into Virtuoso

Now AFTER the re-/unpacking of the DBpedia dumps we will register all files in the DBpedia dir (recursively ld_dir_all) to be added to the DBpedia graph. If you use this method make sure that only files reside in the given subtree that you really want to import.
Also don’t forget to import the dbpedia_2015-04.owl file!
If you only want one directory’s files to be added (non recursive) use ld_dir('dir', '*.*', 'graph');.
If you manually want to add some files, use ld_add('file', 'graph');.
See the VirtBulkRDFLoaderScript file for details.

Be warned that it might be a bad idea to import the normal and i18n dataset into the same graph if you didn’t select specific languages, as it might introduce a lot of duplicates that are hard to disentangle.

In order to keep track (and easily reproduce) what was selected and imported into which graph, I actually link (ln -s) the repacked files into a directory structure beneath /usr/local/data/datasets/dbpedia/2015-04/importedGraphs/ and import from there instead. To make sure you think about this, I use that path below, so it won’t work if you didn’t pay attention. If you really want to import all downloaded files, just import /usr/local/data/datasets/dbpedia/2015-04/.

Also be aware of the fact that if you load certain parts of dumps in different graphs (such as I did with the pagelinks, as well as the i18n versions of the DE and FR datasets) that only triples from the graph will be shown when you visit the local pages with your browser (SPARQL is unaffected by this)!

So if you only want to load the same datasets as loaded on the official endpoint then importing the core folder (first section below) and dbpedia_2015-04.owl file should be enough.

The following will prepare the linking for the datasets we loaded:

cd /usr/local/data/datasets/dbpedia/2015-04/
mkdir importedGraphs
cd importedGraphs

# ln -s ../../dbpedia*.owl ./  # see below!
ln -s ../../core/*.nt.gz ./
cd ..

ln -s ../../core-i18n/en/anchor-text_en.nt.gz ./
ln -s ../../core-i18n/en/article-templates_en.nt.gz ./
ln -s ../../core-i18n/en/genders_en.nt.gz ./
ln -s ../../core-i18n/en/instance_types_dbtax-dbo.nt.gz ./
ln -s ../../core-i18n/en/instance_types_dbtax_ext.nt.gz ./
ln -s ../../core-i18n/en/instance_types_lhd_dbo_en.nt.gz ./
ln -s ../../core-i18n/en/instance_types_lhd_ext_en.nt.gz ./
ln -s ../../core-i18n/en/out-degree_en.nt.gz ./
ln -s ../../core-i18n/en/page-length_en.nt.gz ./
cd ..

ln -s ../../core-i18n/en/page-links_en.nt.gz ./
cd ..

ln -s ../../core-i18n/en/topical-concepts_en.nt.gz ./
cd ..

ln -s ../../core-i18n/de/article-categories_de.nt.gz ./
ln -s ../../core-i18n/de/article-templates_de.nt.gz ./
ln -s ../../core-i18n/de/category-labels_de.nt.gz ./
ln -s ../../core-i18n/de/disambiguations_de.nt.gz ./
ln -s ../../core-i18n/de/external-links_de.nt.gz ./
ln -s ../../core-i18n/de/freebase-links_de.nt.gz ./
ln -s ../../core-i18n/de/geo-coordinates_de.nt.gz ./
ln -s ../../core-i18n/de/geonames_links_de.nt.gz ./
ln -s ../../core-i18n/de/homepages_de.nt.gz ./
ln -s ../../core-i18n/de/images_de.nt.gz ./
ln -s ../../core-i18n/de/infobox-properties_de.nt.gz ./
ln -s ../../core-i18n/de/infobox-property-definitions_de.nt.gz ./
ln -s ../../core-i18n/de/instance-types_de.nt.gz ./
ln -s ../../core-i18n/de/instance_types_lhd_dbo_de.nt.gz ./
ln -s ../../core-i18n/de/instance_types_lhd_ext_de.nt.gz ./
ln -s ../../core-i18n/de/instance-types-transitive_de.nt.gz ./
ln -s ../../core-i18n/de/interlanguage-links-chapters_de.nt.gz ./
ln -s ../../core-i18n/de/interlanguage-links_de.nt.gz ./
ln -s ../../core-i18n/de/iri-same-as-uri_de.nt.gz ./
ln -s ../../core-i18n/de/labels_de.nt.gz ./
ln -s ../../core-i18n/de/long-abstracts_de.nt.gz ./
ln -s ../../core-i18n/de/mappingbased-properties_de.nt.gz ./
ln -s ../../core-i18n/de/out-degree_de.nt.gz ./
ln -s ../../core-i18n/de/page-ids_de.nt.gz ./
ln -s ../../core-i18n/de/page-length_de.nt.gz ./
ln -s ../../core-i18n/de/persondata_de.nt.gz ./
ln -s ../../core-i18n/de/pnd_de.nt.gz ./
ln -s ../../core-i18n/de/revision-ids_de.nt.gz ./
ln -s ../../core-i18n/de/revision-uris_de.nt.gz ./
ln -s ../../core-i18n/de/short-abstracts_de.nt.gz ./
ln -s ../../core-i18n/de/skos-categories_de.nt.gz ./
ln -s ../../core-i18n/de/specific-mappingbased-properties_de.nt.gz ./
ln -s ../../core-i18n/de/transitive-redirects_de.nt.gz ./
ln -s ../../core-i18n/de/wikipedia-links_de.nt.gz ./
cd ..

ln -s ../../core-i18n/de/page-links_de.nt.gz ./
cd ..

This should have prepared your importedGraphs directory. From this directory you can run the following command which prints out the necessary isql-vt commands to register your graphs for importing:

for g in * ; do echo "ld_dir_all('$(pwd)/$g', '*.*', 'http://$g');" ; done

One more thing (thanks to Romain): In order for the DBpedia.vad package (which is installed at the end) to work correctly, the dbpedia_2014.owl file needs to be imported into graph

Note: In the following i will assume that your Virtuoso isql command is called isql-vt. If you’re in lack of such a command, it might be called isql or isql-v, but this usually means you installed it using some other method than described in here

isql-vt # enter Virtuoso isql mode
-- we are in sql mode now
ld_add('/usr/local/data/datasets/remote/dbpedia/2015-04/dbpedia_2015-04.owl', '');
ld_dir_all('/usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/', '*.*', '');
ld_dir_all('/usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/', '*.*', '');
ld_dir_all('/usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/', '*.*', '');
ld_dir_all('/usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/', '*.*', '');
ld_dir_all('/usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/', '*.*', '');
ld_dir_all('/usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/', '*.*', '');

-- do the following to see which files were registered to be added:
-- if unsatisfied use:
-- delete from DB.DBA.LOAD_LIST;

You can now also register other datasets like Freebase, DBLP, Yago, Umbel and … that you want to be loaded after downloading them to the appropriate directories like this:

ld_add('/usr/local/data/datasets/remote/', '');
ld_dir_all('/usr/local/data/datasets/remote/umbel/External Ontologies', '*.n3', '');
ld_add('/usr/local/data/datasets/remote/umbel/Ontology/umbel.n3', '');
ld_add('/usr/local/data/datasets/remote/umbel/Reference Structure/umbel_reference_concepts.n3', '');
ld_add('/usr/local/data/datasets/remote/yago/yago3/2015-11-04/yagoLabels.ttl.gz', '');

ld_add('/usr/local/data/datasets/remote/dblp/l3s/2015-11-04/dblp.nt.gz', '');

ld_dir_all('/usr/local/data/datasets/remote/wikidata/', '*.nt.gz', '');
ld_dir_all('/usr/local/data/datasets/remote/freebase/2015-08-09', '*.nt.gz', '');
ld_dir_all('/usr/local/data/datasets/remote/linkedgeodata/2014-09-09', '*.*', '');

Our full DB.DBA.LOAD_LIST currently looks like this:

SELECT ll_graph, ll_file FROM DB.DBA.LOAD_LIST;
ll_graph                               ll_file
VARCHAR                                VARCHAR NOT NULL
____________________________________                     /usr/local/data/datasets/remote/dblp/l3s/2015-11-04/dblp.nt.gz   /usr/local/data/datasets/remote/dbpedia/2015-04/dbpedia_2015-04.owl                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                  /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                 /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                 /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                 /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                 /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                 /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                 /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                 /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                 /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                 /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/           /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/        /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/     /usr/local/data/datasets/remote/dbpedia/2015-04/importedGraphs/                /usr/local/data/datasets/remote/freebase/2015-08-09/fb2w.nt.gz                /usr/local/data/datasets/remote/freebase/2015-08-09/freebase-rdf-2015-08-09-00-01.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-Abutters.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-Abutters.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-AerialwayThing.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-AerialwayThing.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-AerowayThing.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-AerowayThing.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-Amenity.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-Amenity.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-BarrierThing.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-BarrierThing.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-Boundary.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-Boundary.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-Craft.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-Craft.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-CyclewayThing.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-CyclewayThing.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-EmergencyThing.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-EmergencyThing.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-HistoricThing.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-HistoricThing.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-Leisure.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-Leisure.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-LockThing.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-LockThing.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-ManMadeThing.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-ManMadeThing.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-MilitaryThing.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-MilitaryThing.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-Office.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-Office.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-Place.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-Place.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-PowerThing.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-PowerThing.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-PublicTransportThing.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-PublicTransportThing.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-RailwayThing.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-RailwayThing.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-RouteThing.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-RouteThing.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-Shop.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-Shop.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-SportThing.node.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-SportThing.way.sorted.nt.gz               /usr/local/data/datasets/remote/linkedgeodata/2014-09-09/2014-09-09-ontology.sorted.nt.gz                      /usr/local/data/datasets/remote/              /usr/local/data/datasets/remote/umbel/External Ontologies/dbpedia-ontology.n3              /usr/local/data/datasets/remote/umbel/External Ontologies/geonames.n3              /usr/local/data/datasets/remote/umbel/External Ontologies/opencyc.n3              /usr/local/data/datasets/remote/umbel/External Ontologies/same-as.n3              /usr/local/data/datasets/remote/umbel/External Ontologies/              /usr/local/data/datasets/remote/umbel/External Ontologies/wikipedia.n3                 /usr/local/data/datasets/remote/umbel/Ontology/umbel.n3              /usr/local/data/datasets/remote/umbel/Reference Structure/umbel_reference_concepts.n3                /usr/local/data/datasets/remote/wikidata/                /usr/local/data/datasets/remote/wikidata/                /usr/local/data/datasets/remote/wikidata/                /usr/local/data/datasets/remote/wikidata/                /usr/local/data/datasets/remote/wikidata/                /usr/local/data/datasets/remote/wikidata/                /usr/local/data/datasets/remote/wikidata/                /usr/local/data/datasets/remote/wikidata/     /usr/local/data/datasets/remote/yago/yago3/2015-11-04/yagoLabels.ttl.gz

219 Rows. -- 8 msec.

OK, now comes the fun (and long part: about 1.5 hours (new Virtuoso 7 is cool 😉 for DBpedia alone, +~6 hours for Freebase)… After we registered the files to be added, now let’s finally start the process. Fire up screen if you didn’t already. (For more detailed metering than below see VirtTipsAndTricksGuideLDMeterUtility.)

sudo apt-get install screen
screen isql-vt
-- depending on the amount of CPUs and your IO performance you can run
-- more rdf_loader_run(); commands in other isql-vt sessions which will
-- speed up the import process.
-- you can watch the progress from another isql-vt session with:
-- select * from DB.DBA.LOAD_LIST;
-- if you need to stop the loading for any reason: rdf_load_stop();
-- if you want to force stopping: rdf_load_stop(1);
commit WORK;

After this:
Take a look into var/lib/virtuoso/db/virtuoso.log and run this:


Should you find any errors in there… FIX THEM! You might be able to use the dump, but it’s incomplete in those cases. Any error quits out of the loading of the corresponding file and continues with the next one, so you’re only using the part of that file up to the place where the error occurred. (Should you find errors you can’t fix, please leave a comment.)

Final polishing

You can & should now install the DBpedia and RDF Mappers packages from the Virtuoso Conductor.

login: dba
pw: dba

Go to System Admin / Packages. Install the DBpedia (v. 1.4.30) and rdf_mappers (v. 1.34.74) packages (takes about 5 minutes).

Testing your local mirror

Go to the sparql-endpoint of your server http://your-server:8890/sparql (or in isql-vt prefix with: SPARQL)

sparql SELECT COUNT(*) WHERE { ?s ?p ?o } ;

This shouldn’t take long in Virtuoso 7 anymore and for me now returns 849,521,186 for DBpedia (en+de) or 5,959,006,725 with all the datasets mentioned above.

I also like this query showing all the graphs and how many triples are in them:

sparql SELECT ?g COUNT(*) AS ?c { GRAPH ?g {?s ?p ?o.} } GROUP BY ?g ORDER BY DESC(?c);
g                                                            c
LONG VARCHAR                                                 LONG VARCHAR
__________________________________________________________                                      3126890738                                     1013866920                                      841008708                                           411914840                                 158878272                                        119876594                                       99042212                                           81987210                              59622795                           44963422                                    480616   256065                           157560                         28880                                            8727
http://localhost:8890/DAV/                                   4806                   2472                                       1584                                  1480                               1226                             937                                    857                  804                   741                 696                691                         661
virtrdf-label                                                638                                   557                                     553                       482                 444                 386                             332                     311                          252                       225                      183                    172                               160                                160                     144                     143                             139                          117                    103             102                      90                        87                  85                       79                68                        41              32                    26                      23         21                      21
http://localhost:8890/sparql                                 14                    12
dbprdf-label                                                 6                                    3

62 ROWS. -- 58092 msec.

Congratulations, you just imported nearly 850 million triples (or nearly 6 G triples for all datasets).

Backing up this initial state

Now is a good moment to backup the whole db (takes about half an hour):

sudo -i
cd /
/etc/init.d/virtuoso-opensource stop &&
tar -cvf - /var/lib/virtuoso | lzop > virtuoso-7.1.0-DBDUMP-$(date '+%F')-dbpedia-2015-04-en_de.tar.lzop &&
/etc/init.d/virtuoso-opensource start

Afterwards you might want to repack this with xz (lzma) like this:

# apt-get install xz pxz
for f in virtuoso-7.1.0-DBDUMP-*.tar.lzop ; do lzop -d -c "$f" | pxz > "${f%lzop}.xz" ; done

Yay, done 😉
As always, feel free to leave comments if i made a mistake or to tell us about your problems or how happy you are :D.


Many thanks to the DBpedia team for their endless efforts of providing us all with a great dataset. Also many thanks to the Virtuoso crew for releasing an OpenSource version of their DB.

October 22, 2015

if (condition) { statements; /* ... */ } if (condition) { statements; /* ... */ } … and those who switch from one [...]

October 19, 2015

Weblog der Fachschaft Informatik

Scotland Yard 4.0

Weblog der Fachschaft Informatik

Die Bilder vom Scotland Yard 4.0 sind jetzt auf zu sehen. Viel Spaß damit =)

October 07, 2015

Weblog der Fachschaft Informatik

Ewoche (05. bis 24. Oktober)

Weblog der Fachschaft Informatik

Es ist EWoche!

Wie jedes Semester veranstaltet die Fachschaft in den ersten drei Wochen vor Vorlesungsbeginn eine Einführungswoche für Erstemester. Es ist die beste Zeit, um andere Erstsemester kennenzulernen, Freundschaften zu schließen und sich mit dem ein oder anderen Alt-Fachschaftler auzutauschen.

Für weitere Infos und um zu sehen, welche Events ihr leider schon verpasst habt, besucht einfach

October 01, 2015

Weblog der Fachschaft Informatik

Orientierungseinheit WS 2015/2016

Weblog der Fachschaft Informatik

Update: Die Folien einiger Präsentation sind jetzt online.

Vom 12.10 bis zum 14.10.2015 stellen sich wieder die Lehrgebiete vor, aus denen ihr eure Vertiefungen und Schwerpunkte im Bachelor wählen könnt. Ihr bekommt also einen kurzen Einblick in das Themengebiet und erste Informationen über die zugehörigen Vorlesungen.

Die Vorstellungen finden alle im Raum 48-231  zu folgenden Terminen statt:

 Wann?   Was? Folien*
Mo 12.10. 10:30 – 11:00 Software-Engineering
Mo 12.10. 11:00 – 11:30 Intelligente Systeme Folien Intelligente Systeme
Di 13.10. 10:00 – 10:30 Eingebettete Systeme und Robotik Folien Eingebette System und Robotik
Di 13.10. 10:30 – 11:00 Verteilte und vernetzte Systeme Folien Verteilte und vernetzte Systeme
Di 13.10. 11:00 – 11:30 Computergrafik
Mi 14.10. 10:30 – 11:00 Algorithmik und Deduktion
Mi 14.10. 11:00 – 11:30 Informationssysteme Folien Informationsysteme

* Die Information auf den Folien sind ohne Gewähr.

September 13, 2015

The Surface Pro 3 is a neat device but there are a bunch of user experience flaws concerning the Cover and the Pen (which we will solve in this post) such as:

  1. The Cover’s function keys F1-F12 can only be reached using the Fn button – especially annoying for developers.
  2. The Cover has no button for changing the screen brightness.
  3. The Surface Pen’s top button (the purple one) cannot be configured to open a custom program. I want the Snipping Tool to be opened.
  4. The Surface Pen lacks a “back” button, e.g. for quickly correct a wrong pen stroke in Photoshop, Mischief or similar drawing programs.
  5. I never use CapsLock – let’s map it to a different key, e.g. AltGr (this is of course not Surface-specific but a nice productivity boost nonetheless)


The solutions 1 & 2 are just simple keyboard shortcuts:

  1. Function key lock: Press Fn+CapsLock to toggle the behavior. Unfortunately, this introduces a new problem which will be solved in the paragraph after this:
    1. For F1-F8 I want the default to be the function keys themselves but for F9-F12 I want the reverse as default: Home, End, Page Up, Page DownSurface Pro cover: function keys
  2. Secret shortcuts for screen brightness: Fn + Del/Backspace

The remaining problems are solved by using AutoHotKey with its extensive scripting capabilities and a lively community sharing knowledge thereof.
After installing it, copy the following script to the autostart folder (e.g. under the name of EnableSurfaceProductivity.ahk) to have it running with every start. To test the effect immediately, double-click the file.

; Solution for 1b: Reverse behavior for F9-F12
; The dollar signs prevent that the hooks call themselves

$Home::SendInput, {F9}

$End::SendInput, {F10}

$PgUp::SendInput, {F11}

$PgDn::SendInput, {F12}

$F9::Send, {Home}

$F10::Send, {End}

$F11::Send, {PgUp}

$F12::Send, {PgDn}

; Solution 3: Open the Snipping Tool when double-pressing the purple button
; The script waits for the program to start and then simulates
; Win+PrintScreen which directly jumps to the selection process
Run, "C:\Windows\Sysnative\SnippingTool.exe"
WinActivate, Snipping Tool ; This makes sure it is active
WinWaitActive, Snipping Tool
Send, ^{PrintScreen}

; Solution 4: Simulate Ctrl+Z when single-clicking the purple button
#F20::Send, ^z ; Left Arrow, Browser Back Button

; Solution 5: Map CapsLock to AltGr

September 03, 2015

Example: utf_decode can’t handle that character because what that function does is converting utf-8 characters to latin-1.

See the screenshots made offline.

September 01, 2015

Using the root scope // app.js angular.module('myApp', [ 'ngCookies', 'ngResource', 'ngSanitize', [...]

August 28, 2015

Posted by: [personal profile] t_fischer

I spent the better part of today's evening preparing and testing .ebuild files (available in my GitLab repository) for the so-called ‘free branch’ versions of targetcli, rtslib, and configshell called targetcli-fb, rtslib-fb, and configshell-fb. The ‘free branch’ versions are the recommended way of setting up a iSCSI target on Linux, according to the Arch Linux wiki.

Read more... )

comment count unavailable comments

August 26, 2015

This is a tutorial on how to use scipy's hierarchical clustering.

One of the benefits of hierarchical clustering is that you don't need to already know the number of clusters k in your data in advance. Sadly, there doesn't seem to be much documentation on how to actually use scipy's hierarchical clustering to make an informed decision and then retrieve the clusters.

In the following I'll explain:

Naming conventions:

Before we start, as i know that it's easy to get lost, some naming conventions:

  • X samples (n x m array), aka data points or "singleton clusters"
  • n number of samples
  • m number of features
  • Z cluster linkage array (contains the hierarchical clustering information)
  • k number of clusters

So, let's go.

Imports and Setup

In [1]:
# needed imports
from matplotlib import pyplot as plt
from scipy.cluster.hierarchy import dendrogram, linkage
import numpy as np
In [2]:
# some setting for this notebook to actually show the graphs inline, you probably won't need this
%matplotlib inline
np.set_printoptions(precision=5, suppress=True)  # suppress scientific float notation

Generating Sample Data

You'll obviously not need this step to run the clustering if you have own data.

The only thing you need to make sure is that you convert your data into a matrix X with n samples and m features, so that X.shape == (n, m).

In [3]:
# generate two clusters: a with 100 points, b with 50:
np.random.seed(4711)  # for repeatability of this tutorial
a = np.random.multivariate_normal([10, 0], [[3, 1], [1, 4]], size=[100,])
b = np.random.multivariate_normal([0, 20], [[3, 1], [1, 4]], size=[50,])
X = np.concatenate((a, b),)
print X.shape  # 150 samples with 2 dimensions
plt.scatter(X[:,0], X[:,1])
(150, 2)

Perform the Hierarchical Clustering

Now that we have some very simple sample data, let's do the actual clustering on it:

In [4]:
# generate the linkage matrix
Z = linkage(X, 'ward')

Done. That was pretty simple, wasn't it?

Well, sure it was, this is python ;), but what does the weird 'ward' mean there and how does this actually work?

As the scipy linkage docs tell us, 'ward' is one of the methods that can be used to calculate the distance between newly formed clusters. 'ward' causes linkage() to use the Ward variance minimization algorithm.

I think it's a good default choice, but it never hurts to play around with some other common linkage methods like 'single', 'complete', 'average', ... and the different distance metrics like 'euclidean' (default), 'cityblock' aka Manhattan, 'hamming', 'cosine'... if you have the feeling that your data should not just be clustered to minimize the overall intra cluster variance in euclidean space. For example, you should have such a weird feeling with long (binary) feature vectors (e.g., word-vectors in text clustering).

As you can see there's a lot of choice here and while python and scipy make it very easy to do the clustering, it's you who has to understand and make these choices. If i find the time, i might give some more practical advice about this, but for now i'd urge you to at least read up on the linked methods and metrics to make a somewhat informed choice. Another thing you can and should definitely do is check the Cophenetic Correlation Coefficient of your clustering with help of the cophenet() function. This (very very briefly) compares (correlates) the actual pairwise distances of all your samples to those implied by the hierarchical clustering. The closer the value is to 1, the better the clustering preserves the original distances, which in our case is pretty close:

In [5]:
from scipy.cluster.hierarchy import cophenet
from scipy.spatial.distance import pdist

c, coph_dists = cophenet(Z, pdist(X))

No matter what method and metric you pick, the linkage() function will use that method and metric to calculate the distances of the clusters (starting with your n individual samples (aka data points) as singleton clusters)) and in each iteration will merge the two clusters which have the smallest distance according the selected method and metric. It will return an array of length n - 1 giving you information about the n - 1 cluster merges which it needs to pairwise merge n clusters. Z[i] will tell us which clusters were merged in the i-th iteration, let's take a look at the first two points that were merged:

In [6]:
array([ 52.     ,  53.     ,   0.04151,   2.     ])

We can see that ach row of the resulting array has the format [idx1, idx2, dist, sample_count].

In its first iteration the linkage algorithm decided to merge the two clusters (original samples here) with indices 52 and 53, as they only had a distance of 0.04151. This created a cluster with a total of 2 samples.

In [7]:
array([ 14.     ,  79.     ,   0.05914,   2.     ])

In the second iteration the algorithm decided to merge the clusters (original samples here as well) with indices 14 and 79, which had a distance of 0.04914. This again formed another cluster with a total of 2 samples.

The indices of the clusters until now correspond to our samples. Remember that we had a total of 150 samples, so indices 0 to 149. Let's have a look at the first 20 iterations:

In [8]:
array([[  52.     ,   53.     ,    0.04151,    2.     ],
       [  14.     ,   79.     ,    0.05914,    2.     ],
       [  33.     ,   68.     ,    0.07107,    2.     ],
       [  17.     ,   73.     ,    0.07137,    2.     ],
       [   1.     ,    8.     ,    0.07543,    2.     ],
       [  85.     ,   95.     ,    0.10928,    2.     ],
       [ 108.     ,  131.     ,    0.11007,    2.     ],
       [   9.     ,   66.     ,    0.11302,    2.     ],
       [  15.     ,   69.     ,    0.11429,    2.     ],
       [  63.     ,   98.     ,    0.1212 ,    2.     ],
       [ 107.     ,  115.     ,    0.12167,    2.     ],
       [  65.     ,   74.     ,    0.1249 ,    2.     ],
       [  58.     ,   61.     ,    0.14028,    2.     ],
       [  62.     ,  152.     ,    0.1726 ,    3.     ],
       [  41.     ,  158.     ,    0.1779 ,    3.     ],
       [  10.     ,   83.     ,    0.18635,    2.     ],
       [ 114.     ,  139.     ,    0.20419,    2.     ],
       [  39.     ,   88.     ,    0.20628,    2.     ],
       [  70.     ,   96.     ,    0.21931,    2.     ],
       [  46.     ,   50.     ,    0.22049,    2.     ]])

We can observe that until iteration 13 the algorithm only directly merged original samples. We can also observe the monotonic increase of the distance.

In iteration 13 the algorithm decided to merge cluster indices 62 with 152. If you paid attention the 152 should astonish you as we only have original sample indices 0 to 149 for our 150 samples. All indices idx >= len(X) actually refer to the cluster formed in Z[idx - len(X)].

This means that while idx 149 corresponds to X[149] that idx 150 corresponds to the cluster formed in Z[0], idx 151 to Z[1], 152 to Z[2], ...

Hence, the merge iteration 13 merged sample 62 to our samples 33 and 68 that were previously merged in iteration 2 (152 - 2).

Let's check out the points coordinates to see if this makes sense:

In [9]:
X[[33, 68, 62]]
array([[ 9.83913, -0.4873 ],
       [ 9.89349, -0.44152],
       [ 9.97793, -0.56383]])

Seems pretty close, but let's plot the points again and highlight them:

In [10]:
idxs = [33, 68, 62]
plt.figure(figsize=(10, 8))
plt.scatter(X[:,0], X[:,1])  # plot all points
plt.scatter(X[idxs,0], X[idxs,1], c='r')  # plot interesting points in red again

We can see that the 3 red dots are pretty close to each other, which is a good thing.

The same happened in iteration 14 where the alrogithm merged indices 41 to 15 and 69:

In [11]:
idxs = [33, 68, 62]
plt.figure(figsize=(10, 8))
plt.scatter(X[:,0], X[:,1])
plt.scatter(X[idxs,0], X[idxs,1], c='r')
idxs = [15, 69, 41]
plt.scatter(X[idxs,0], X[idxs,1], c='y')

Showing that the 3 yellow dots are also quite close.

And so on...

We'll later come back to visualizing this, but now let's have a look at what's called a dendrogram of this hierarchical clustering first:

Plotting a Dendrogram

A dendrogram is a visualization in form of a tree showing the order and distances of merges during the hierarchical clustering.

In [12]:
# calculate full dendrogram
plt.figure(figsize=(25, 10))
plt.title('Hierarchical Clustering Dendrogram')
plt.xlabel('sample index')
    leaf_rotation=90.,  # rotates the x axis labels
    leaf_font_size=8.,  # font size for the x axis labels

(right click and "View Image" to see full resolution)

If this is the first time you see a dendrogram, it's probably quite confusing, so let's take this apart...

  • On the x axis you see labels. If you don't specify anything else they are the indices of your samples in X.
  • On the y axis you see the distances (of the 'ward' method in our case).

Starting from each label at the bottom, you can see a vertical line up to a horizontal line. The height of that horizontal line tells you about the distance at which this label was merged into another label or cluster. You can find that other cluster by following the other vertical line down again. If you don't encounter another horizontal line, it was just merged with the other label you reach, otherwise it was merged into another cluster that was formed earlier.


  • horizontal lines are cluster merges
  • vertical lines tell you which clusters/labels were part of merge forming that new cluster
  • heights of the horizontal lines tell you about the distance that needed to be "bridged" to form the new cluster

You can also see that from distances > 25 up there's a huge jump of the distance to the final merge at a distance of approx. 180. Let's have a look at the distances of the last 4 merges:

In [13]:
array([  15.11533,   17.11527,   23.12199,  180.27043])

Such distance jumps / gaps in the dendrogram are pretty interesting for us. They indicate that something is merged here, that maybe just shouldn't be merged. In other words: maybe the things that were merged here really don't belong to the same cluster, telling us that maybe there's just 2 clusters here.

Looking at indices in the above dendrogram also shows us that the green cluster only has indices >= 100, while the red one only has such < 100. This is a good thing as it shows that the algorithm re-discovered the two classes in our toy example.

In case you're wondering about where the colors come from, you might want to have a look at the color_threshold argument of dendrogram(), which as not specified automagically picked a distance cut-off value of 70 % of the final merge and then colored the first clusters below that in individual colors.

Dendrogram Truncation

As you might have noticed, the above is pretty big for 150 samples already and you probably have way more in real scenarios, so let me spend a few seconds on highlighting some other features of the dendrogram() function:

In [14]:
plt.title('Hierarchical Clustering Dendrogram (truncated)')
plt.xlabel('sample index')
    truncate_mode='lastp',  # show only the last p merged clusters
    p=12,  # show only the last p merged clusters
    show_leaf_counts=False,  # otherwise numbers in brackets are counts
    show_contracted=True,  # to get a distribution impression in truncated branches

The above shows a truncated dendrogram, which only shows the last p=12 out of our 149 merges.

First thing you should notice are that most labels are missing. This is because except for X[40] all other samples were already merged into clusters before the last 12 merges.

The parameter show_contracted allows us to draw black dots at the heights of those previous cluster merges, so we can still spot gaps even if we don't want to clutter the whole visualization. In our example we can see that the dots are all at pretty small distances when compared to the huge last merge at a distance of 180, telling us that we probably didn't miss much there.

As it's kind of hard to keep track of the cluster sizes just by the dots, dendrogram() will by default also print the cluster sizes in brackets () if a cluster was truncated:

In [15]:
plt.title('Hierarchical Clustering Dendrogram (truncated)')
plt.xlabel('sample index or (cluster size)')
    truncate_mode='lastp',  # show only the last p merged clusters
    p=12,  # show only the last p merged clusters
    show_contracted=True,  # to get a distribution impression in truncated branches

We can now see that the right most cluster already consisted of 33 samples before the last 12 merges.

Eye Candy

Even though this already makes for quite a nice visualization, we can pimp it even more by also annotating the distances inside the dendrogram by using some of the useful return values dendrogram():

In [16]:
def fancy_dendrogram(*args, **kwargs):
    max_d = kwargs.pop('max_d', None)
    if max_d and 'color_threshold' not in kwargs:
        kwargs['color_threshold'] = max_d
    annotate_above = kwargs.pop('annotate_above', 0)

    ddata = dendrogram(*args, **kwargs)

    if not kwargs.get('no_plot', False):
        plt.title('Hierarchical Clustering Dendrogram (truncated)')
        plt.xlabel('sample index or (cluster size)')
        for i, d, c in zip(ddata['icoord'], ddata['dcoord'], ddata['color_list']):
            x = 0.5 * sum(i[1:3])
            y = d[1]
            if y > annotate_above:
                plt.plot(x, y, 'o', c=c)
                plt.annotate("%.3g" % y, (x, y), xytext=(0, -5),
                             textcoords='offset points',
                             va='top', ha='center')
        if max_d:
            plt.axhline(y=max_d, c='k')
    return ddata
In [17]:
    annotate_above=10,  # useful in small plots so annotations don't overlap

Selecting a Distance Cut-Off aka Determining the Number of Clusters

As explained above already, a huge jump in distance is typically what we're interested in if we want to argue for a certain number of clusters. If you have the chance to do this manually, i'd always opt for that, as it allows you to gain some insights into your data and to perform some sanity checks on the edge cases. In our case i'd probably just say that our cut-off is 50, as the jump is pretty obvious:

In [18]:
# set cut-off to 50
max_d = 50  # max_d as in max_distance

Let's visualize this in the dendrogram as a cut-off line:

In [19]:
    max_d=max_d,  # plot a horizontal cut-off line

As we can see, we ("surprisingly") have two clusters at this cut-off.

In general for a chosen cut-off value max_d you can always simply count the number of intersections with vertical lines of the dendrogram to get the number of formed clusters. Say we choose a cut-off of max_d = 16, we'd get 4 final clusters:

In [20]:

Automated Cut-Off Selection (or why you shouldn't rely on this)

Now while this manual selection of a cut-off value offers a lot of benefits when it comes to checking for a meaningful clustering and cut-off, there are cases in which you want to automate this.

The problem again is that there is no golden method to pick the number of clusters for all cases (which is why i think the investigative & backtesting manual method is preferable). Wikipedia lists a couple of common methods. Reading this, you should realize how different the approaches and how vague their descriptions are.

I honestly think it's a really bad idea to just use any of those methods, unless you know the data you're working on really really well.

Inconsistency Method

For example, let's have a look at the "inconsistency" method, which seems to be one of the defaults for the fcluster() function in scipy.

The question driving the inconsistency method is "what makes a distance jump a jump?". It answers this by comparing each cluster merge's height h to the average avg and normalizing it by the standard deviation std formed over the depth previous levels:

$$inconsistency = \frac{h - avg}{std}$$

The following shows a matrix of the avg, std, count, inconsistency for each of the last 10 merges of our hierarchical clustering with depth = 5

In [21]:
from scipy.cluster.hierarchy import inconsistent

depth = 5
incons = inconsistent(Z, depth)
array([[  1.80875,   2.17062,  10.     ,   2.44277],
       [  2.31732,   2.19649,  16.     ,   2.52742],
       [  2.24512,   2.44225,   9.     ,   2.37659],
       [  2.30462,   2.44191,  21.     ,   2.63875],
       [  2.20673,   2.68378,  17.     ,   2.84582],
       [  1.95309,   2.581  ,  29.     ,   4.05821],
       [  3.46173,   3.53736,  28.     ,   3.29444],
       [  3.15857,   3.54836,  28.     ,   3.93328],
       [  4.9021 ,   5.10302,  28.     ,   3.57042],
       [ 12.122  ,  32.15468,  30.     ,   5.22936]])

Now you might be tempted to say "yay, let's just pick 5" as a limit in the inconsistencies, but look at what happens if we set depth to 3 instead:

In [22]:
depth = 3
incons = inconsistent(Z, depth)
array([[  3.63778,   2.55561,   4.     ,   1.35908],
       [  3.89767,   2.57216,   7.     ,   1.54388],
       [  3.05886,   2.66707,   6.     ,   1.87115],
       [  4.92746,   2.7326 ,   7.     ,   1.39822],
       [  4.76943,   3.16277,   6.     ,   1.60456],
       [  5.27288,   3.56605,   7.     ,   2.00627],
       [  8.22057,   4.07583,   7.     ,   1.69162],
       [  7.83287,   4.46681,   7.     ,   2.07808],
       [ 11.38091,   6.2943 ,   7.     ,   1.86535],
       [ 37.25845,  63.31539,   7.     ,   2.25872]])

Oups! This should make you realize that the inconsistency values heavily depend on the depth of the tree you calculate the averages over.

Another problem in its calculation is that the previous d levels' heights aren't normally distributed, but expected to increase, so you can't really just treat the current level as an "outlier" of a normal distribution, as it's expected to be bigger.

Elbow Method

Another thing you might see out there is a variant of the "elbow method". It tries to find the clustering step where the acceleration of distance growth is the biggest (the "strongest elbow" of the blue line graph below, which is the highest value of the green graph below):

In [23]:
last = Z[-10:, 2]
last_rev = last[::-1]
idxs = np.arange(1, len(last) + 1)
plt.plot(idxs, last_rev)

acceleration = np.diff(last, 2)  # 2nd derivative of the distances
acceleration_rev = acceleration[::-1]
plt.plot(idxs[:-2] + 1, acceleration_rev)
k = acceleration_rev.argmax() + 2  # if idx 0 is the max of this we want 2 clusters
print "clusters:", k
clusters: 2

While this works nicely in our simplistic example (the green line takes its maximum for k=2), it's pretty flawed as well.

One issue of this method has to do with the way an "elbow" is defined: you need at least a right and a left point, which implies that this method will never be able to tell you that all your data is in one single cluster only.

Another problem with this variant lies in the np.diff(Z[:, 2], 2) though. The order of the distances in Z[:, 2] isn't properly reflecting the order of merges within one branch of the tree. In other words: there is no guarantee that the distance of Z[i] is contained in the branch of Z[i+1]. By simply computing the np.diff(Z[:, 2], 2) we assume that this doesn't matter and just compare distance jumps from different branches of our merge tree.

If you still don't want to believe this, let's just construct another simplistic example but this time with very different variances in the different clusters:

In [24]:
c = np.random.multivariate_normal([40, 40], [[20, 1], [1, 30]], size=[200,])
d = np.random.multivariate_normal([80, 80], [[30, 1], [1, 30]], size=[200,])
e = np.random.multivariate_normal([0, 100], [[100, 1], [1, 100]], size=[200,])
X2 = np.concatenate((X, c, d, e),)
plt.scatter(X2[:,0], X2[:,1])

As you can see we have 5 clusters now, but they have increasing variances... let's have a look at the dendrogram again and how you can use it to spot the problem:

In [25]:
Z2 = linkage(X2, 'ward')

When looking at a dendrogram like this and trying to put a cut-off line somewhere, you should notice the very different distributions of merge distances below that cut-off line. Compare the distribution in the cyan cluster to the red, green or even two blue clusters that have even been truncated away. In the cyan cluster below the cut-off we don't really have any discontinuity of merge distances up to very close to the cut-off line. The two blue clusters on the other hand are each merged below a distance of 25, and have a gap of > 155 to our cut-off line.

The variant of the "elbow" method will incorrectly see the jump from 167 to 180 as minimal and tell us we have 4 clusters:

In [26]:
last = Z2[-10:, 2]
last_rev = last[::-1]
idxs = np.arange(1, len(last) + 1)
plt.plot(idxs, last_rev)

acceleration = np.diff(last, 2)  # 2nd derivative of the distances
acceleration_rev = acceleration[::-1]
plt.plot(idxs[:-2] + 1, acceleration_rev)
k = acceleration_rev.argmax() + 2  # if idx 0 is the max of this we want 2 clusters
print "clusters:", k
clusters: 4

The same happens with the inconsistency metric:

In [27]:
print inconsistent(Z2, 5)[-10:]
[[  13.99222   15.56656   30.         3.86585]
 [  16.73941   18.5639    30.         3.45983]
 [  19.05945   20.53211   31.         3.49953]
 [  19.25574   20.82658   29.         3.51907]
 [  21.36116   26.7766    30.         4.50256]
 [  36.58101   37.08602   31.         3.50761]
 [  12.122     32.15468   30.         5.22936]
 [  42.6137   111.38577   31.         5.13038]
 [  81.75199  208.31582   31.         5.30448]
 [ 147.25602  307.95701   31.         3.6215 ]]

I hope you can now understand why i'm warning against blindly using any of those methods on a dataset you know nothing about. They can give you some indication, but you should always go back in and check if the results make sense, for example with a dendrogram which is a great tool for that (especially if you have higher dimensional data that you can't simply visualize anymore).

Retrieve the Clusters

Now, let's finally have a look at how to retrieve the clusters, for different ways of determining k. We can use the fcluster function.

Knowing max_d:

Let's say we determined the max distance with help of a dendrogram, then we can do the following to get the cluster id for each of our samples:

In [28]:
from scipy.cluster.hierarchy import fcluster
max_d = 50
clusters = fcluster(Z, max_d, criterion='distance')
array([2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
       2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
       2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
       2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
       2, 2, 2, 2, 2, 2, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
       1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
       1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1], dtype=int32)

Knowing k:

Another way starting from the dendrogram is to say "i can see i have k=2" clusters. You can then use:

In [29]:
fcluster(Z, k, criterion='maxclust')
array([2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
       2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
       2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
       2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
       2, 2, 2, 2, 2, 2, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
       1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
       1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1], dtype=int32)

Using the Inconsistency Method (default):

If you're really sure you want to use the inconsistency method to determine the number of clusters in your dataset, you can use the default criterion of fcluster() and hope you picked the correct values:

In [30]:
from scipy.cluster.hierarchy import fcluster
fcluster(Z, 8, depth=10)
array([2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
       2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
       2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
       2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
       2, 2, 2, 2, 2, 2, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
       1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
       1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1], dtype=int32)

Visualizing Your Clusters

If you're lucky enough and your data is very low dimensional, you can actually visualize the resulting clusters very easily:

In [31]:
plt.figure(figsize=(10, 8))
plt.scatter(X[:,0], X[:,1], c=clusters, cmap='prism')  # plot points with cluster dependent colors

I hope you enjoyed this tutorial. Feedback welcome ;)

Further Reading:

August 22, 2015

Some notes on the MPS-110NF music player, in particular when using it on Linux. The player is sold under the brand "Denver" or "Bazzix" in different places.

The manual for this player is very terse and printed in a tiny size; so here's what I figured out. (TL;DR: It plays OGG, it works well with Linux)


  • Well-suited for listening during exercise
    • Very lightweight
    • Has a clip
  • Very cheap
  • Working audio formats are MP3 and WMA, but it also turns out to play OGG! :)
  • Difficult to see how full the battery is
  • File transfer is via FAT filesystem on the SD card (the player functions as micro SD card reader when plugged in)


  • Press "+" and "-": change the volume
  • Press "|<<" and ">>|": previous and next track
  • Long press "|<<" and ">>|": switch on/off shuffle mode (green LED will blink)

Navigating between files can become unreliable when the file system is broken. The file system driver has a lower tolerance for that than Linux.

Managing music with Banshee

The Banshee music player seems to get things right — if your USB automounting works. :)

For Banshee to recognize the device as a player and to know the supported filetypes, add the file .is_audio_player to the player's root directory, with the following content:


From there on, Banshee will recognize the device just fine. The output_formats line will tell Banshee that the player understands OGG files, so that it won't try to convert to MP3 first.

Managing music on the player — command line

You want the full control! You despise using GUI applications! You're up for a surprise!

The player plays files in the order in which they were created, not in the alphabetical order of the filenames.

Files easily end up in the wrong order, but it's luckily fixable by "touching" (renaming to a temporary name and back) all files in a directory in the right order.

I conveniently placed this script on the player:

"""Recursively touches all file entries in alphabetical order.

  ./ /mnt/bazzix
import os
import sys

def walk(basedir):
  for dirpath, dirnames, filenames in os.walk(basedir):
    touchfiles(dirpath, filenames)

def touchfiles(basedir, files):
  for filename in sorted(files):
    tempname = "temp_%s" % filename
    full_filename = os.path.join(basedir, filename)
    full_tempname = os.path.join(basedir, tempname)
    # Touch the directory entry.
    print full_filename
    os.rename(full_filename, full_tempname)
    os.rename(full_tempname, full_filename)

def main(args):
  if not args:
    sys.exit("Needs directory argument, e.g. /mnt/bazzix")

if __name__ == "__main__":

Other people recommend to run the tool fatsort on the block device. This tool aims to solve exactly the same problem, but fsck.vfat frequently complained about the results and I ended up with broken file systems.

August 17, 2015

Weblog der Fachschaft Informatik

Back again

Weblog der Fachschaft Informatik

Heute Mittag ist es endlich so weit:
Ab 15:00 Uhr wird das neue Fachschaftsbüro endlich offiziell eröffnet. Inklusive Kaffee, Kuchen, kalten Getränken und unserem neuen Wassersprudler.
Als Willkommensangebot bieten wir Krombacher Pils, 0,5l, für gerade einmal 20ct die Flasche feil¹²
Also kommt vorbei und weiht mit uns unseren neuen Raum 48-463 ein!

August 09, 2015

The following two relases of KBibTeX are now available:

Read more... )

comment count unavailable comments

August 06, 2015

Recently I had a create form where one should be able to chose an associated model from a large set of possible values. It wouldn’t be too pleasant to have a drop down listing all these options. My idea was to render paginated radio buttons and a search field to limit the items available. Submitting […]

August 01, 2015

select concat ("Updated ", row_count(), " rows") as ''; #DATE(DATE_SUB(NOW(), INTERVAL ROUND(RAND(1)*10) DAY)); select concat ("Random date: ", DATE(DATE_SUB(NOW(), INTERVAL ROUND(RAND(1)*100) DAY))) as ''; Examples Output a random date: select addtime(concat_ws(' ','1964-12-25' + interval rand()*20000 [...]

July 06, 2015

Weblog der Fachschaft Informatik

Fachschaft geschlossen vom 27.7. bis 16.8.

Weblog der Fachschaft Informatik

Hallo alle zusammen,

ab Ende des Monats finden in 48-4 Umbaumaßnahmen statt, von denen auch wir als Fachschaft betroffen sind. Deswegen sind die Fachschaftsräumlichkeiten (GmF, Sofaraum, Gruft) vom 27.7. bis voraussichtlich zum 16.8. geschlossen, ebenso ist der Seminarraum nicht als Lernraum nutzbar. In dieser Zeit sind wir nur per Mail zu erreichen.
Solltet ihr Altklausuren benötigen, die ihr nur in der FS erhalten könnt, steht euch in diesem Zeitraum ein Terminal im SCI zur Verfügung.

Nach dem Umbau findet ihr uns in 48-463/464. Dieser Raum ist dann die neue GmF, in der ihr wie gewohnt uns, unsere Serviceleistungen und Getränke, Süßkram, Kaffee, Altklausuren, usw. finden könnt.

Viele Grüße,
Eure Fachschaft

July 03, 2015

Guenther Noack

Deep Dream

Guenther Noack

From the Google Research Blog: DeepDream - a code example for visualizing Neural Networks — it's available with source code to play around with now.

On Social Networks, pictures generated with DeepDream are now popping up under the hashtag #DeepDream. The DeepDream pictures generated from fractals by Bernard Geiger are very nice, for instance.

previously on the Google Research Blog: Inceptionism - going deeper into Neural Networks

Wer macht mit?