Gathering plants and fruit with Martin

On Friday, I spent a very happy afternoon with Martin, meeting him at his studio in order to gather some plants with him to try out some good old alchemical distilling with the equipment he’s bought.

After dining, we repaired to one of the increasingly rare stretches of undeveloped wasteland that was once the Berlin Wall and attendant no-mans land, the mauerstreifen. This fragment runs between Alte Jakobstraße and Alexandrinenstr. next to Stallschreiberstr. (here).

It is a wonderful habitat, containing many varieties of edible and medicinal plants, such as fat hen (Chenopodium album), tansy (Tanacetum vulgare), St. John’s Wort (Hypericum perforatum), mullein (Verbascum thapsus) and yarrow (Achillea millefolium) among many others.

We collected St. John’s Wort, tansy, rowan berries and a tiny cherry like crab apple I think is a Cherry-crab (Malus x robusta) after referring to my Collins Tree Guide (Owen Johnson & David More 2006).

MartinCrabAppleNotes

Posted in Diary, Walking | Tagged , , , , , , , , | 1 Comment

Using Spatialite to store and represent our GPX data

Background

A quick bit of background. As people who read this blog know, we have recorded every journey we make, every day, with a GPS since 2003 (Dan) and 2007 (Sophia). As you can imagine, this practice has evolved technologically quite a lot over the decade, not so much because of changes in the way GPS data can be stored but more because of what I have learned in the mean time about storing and accessing data.

File Formats

In the beginning, back in the bad old days of using Windoze XP, when you still had to have some funky cable to connect your Garmin GPS (we’ve nearly always used the most basic eTrex) to a computer, I used Heinrich Pfeifer’s still excellent Gartrip shareware programme. In the beginning I was pretty confused about what file format to use to save my GPS traces, since there seemed to be hundreds of different formats and so I made the mistake of saving some of my early tracks only in Gartrip’s binary format, just because it made for small file sizes plus it was compete (it didn’t throw away information, like KML files for example which used to throw out elevation data as I recall).

GPX

Luckily Gartrip could also save as GPX which in few years became my standard storage format – not because it is economical – it is actually pretty redundantly verbose – but because it’s just so damn transferable, being XML and having been around for so long, most GIS programmes feature at least importers if not means of representing the file. Also it’s a good choice for writing your own scripts as there are so many XML parsers out there to adapt. And so presently, the archive of GPS data consists of folders from each year with a hodge-podge of various text and binary file formats settling down to GPX only files after about 2010.

Databases

Meeting Peter Vasil and then Mathieu Baudier transformed our understanding of how to access this stuff in order to cope with the different outcomes we present with the data. I initially asked Peter whether he would be interested in hacking together something in openFrameworks which would represent our GPS data as an animation, the lines drawing themselves in sequence. This eventually lead to (I can’t bear to look at how many hours) drawinglife, the open source release of which I talked about here.

Spatialite

We finally settled on Alessandro Furieri’s very wonderful Spatialite extension to Sqlite as the database drawinglife reads from to produce our visualisation work. Spatialite gives Sqlite the ability to represent standard geographic information like points, lines, polygons, use coordinate representation systems and perform coordinate transformations, for instance into projected coordinates which are useful for representing global spherical coordinates on something 2D like a computer screen.

Track Points and Lines

I have gone into this issue here, but the challenge we face is how to represent our data as lines, when we come to print them out or make an updated image for our archive, while making sure we retain information about each point.

Our solution was to create an importer in Python, gpx2spatialite, which has helper scripts to create and initialise a spatialite database and then import GPX files into it. This is great, because it automates and standardises the process of representing all of the data a GPX file has in it (waypoints too, although I don’t personally have a need for this). As you can see by the link, gpx2spatialite is also available as an open source script for you to use, fork, hack, whatever.

For those still interested, I thought I’d list the schema we use here as a way of perhaps prompting response. Who knows, maybe we’re not the only ones who need a way of storing a lot of data stored as GPX files in a nice spatially enabled database, ready to use with our drawinglife animation and GIS tools like QGIS.

Our Spatialite Database schema for storing GPX data

Here’s the sql file that generates a new spatialite database, ready to hold our GPX files. (This is the create_db.sql file in the gpx2spatialite/data/sql folder you get when you download gpx2spatialite)

 -- Creates tables for homebrew gps repository.
-- Tables:
-- users
-- files
-- trackpoints
-- tracklines
 
PRAGMA foreign_keys = ON;
 
BEGIN TRANSACTION;
 
CREATE TABLE users (
user_uid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL,
UNIQUE (username));
 
CREATE TABLE files (
file_uid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
filename TEXT NOT NULL,
md5hash TEXT NOT NULL,
date_entered TEXT NOT NULL,
first_timestamp TEXT,
last_timestamp TEXT,
user_uid INTEGER NOT NULL,
FOREIGN KEY (user_uid)
REFERENCES users (user_uid),
UNIQUE (md5hash));
 
CREATE TABLE trackpoints (
trkpt_uid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
trkseg_id INTEGER,
trksegpt_id INTEGER,
ele REAL NOT NULL,
utctimestamp TEXT NOT NULL,
comment TEXT,
course REAL,
speed REAL,
file_uid INTEGER NOT NULL,
user_uid INTEGER NOT NULL,
citydef_uid INTEGER,
FOREIGN KEY (file_uid)
REFERENCES files (file_uid) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (user_uid)
REFERENCES users (user_uid) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (trkseg_id)
REFERENCES tracksegments (trkseg_uid) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (citydef_uid)
REFERENCES citydefs (citydef_uid),
UNIQUE (utctimestamp, user_uid));
 
SELECT AddGeometryColumn('trackpoints', 'geom', 4326, 'POINT', 'XY', 1);
SELECT CreateSpatialIndex('trackpoints', 'geom');
 
CREATE TABLE tracklines (
trkline_uid INTEGER PRIMARY KEY AUTOINCREMENT,
trkseg_id INTEGER,
comment TEXT,
timestamp_start TEXT NOT NULL,
timestamp_end TEXT NOT NULL,
length_m REAL,
time_sec REAL,
speed_kph REAL,
file_uid INTEGER NOT NULL,
user_uid INTEGER NOT NULL,
FOREIGN KEY (file_uid)
REFERENCES files (file_uid) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (user_uid)
REFERENCES users (user_uid) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (trkseg_id)
REFERENCES tracksegments (trkseg_uid) ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE (timestamp_start, user_uid, trkseg_id)
);
 
SELECT AddGeometryColumn('tracklines', 'geom', 4326, 'LINESTRING', 'XY', 1);
SELECT CreateSpatialIndex('tracklines', 'geom');
 
CREATE TABLE citydefs (
citydef_uid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
city TEXT NOT NULL,
country TEXT NOT NULL,
UNIQUE(city, country)
);
 
SELECT AddGeometryColumn('citydefs', 'geom', 4326, 'POLYGON', 'XY', 1);
SELECT CreateSpatialIndex('citydefs', 'geom');
 
CREATE TABLE tracksegments (
trkseg_uid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
trkseg_uuid TEXT NOT NULL,
UNIQUE(trkseg_uuid)
);
 
CREATE TABLE waypoints (
wpt_uid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
wpt_name TEXT,
ele REAL,
utctimestamp TEXT,
sym TEXT,
file_uid INTEGER,
user_uid INTEGER,
citydef_uid INTEGER,
FOREIGN KEY (file_uid)
REFERENCES files (file_uid) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (user_uid)
REFERENCES users (user_uid) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (citydef_uid)
REFERENCES citydefs (citydef_uid));
 
SELECT AddGeometryColumn('waypoints', 'geom', 4326, 'POINT', 'XY', 1);
SELECT CreateSpatialIndex('waypoints', 'geom');
 
COMMIT;

The trackpoints table is where each individual point gets stored. In our drawinglife visualisation application, we needed an idea of where these lines were. To see what I mean, see our piece Narrating Our Lines, where you can see us try and work out where we were from the visualisation. The locations for this is pulled through from the citydef_uid column in the trackpoints table which is a foreign id to the citydefs table, where we have started to define the locations we have been to in the last years. This is a pretty custom aspect and I suspect will give us issues if our userbase grows beyond the three people I know use it at the moment. It’s also something that slows down the importing process a lot as each trackpoint needs to be queried against all the locations in citydefs.

As well as the points, we needed to represent our traces as lines which needs a different geometry type and so lives in another table, tracklines. This is the table that we normally pull through in Qgis when we produce our archive images or laser engravings or printed drawings.

As well as these obvious extractions of the GPX data, we track users in the users table, as Soph’s data and my data lives in the same database so that the animation only has to refer to one database. Also, when we make animations with multiple users, their tracks can all remain in one database.

We also track the GPX files that are imported, generating an md5 hash so that we know if we’re reimporting a file with identical content, even if the name is different. gpx2spatialite also finds the first and last date in the file and stores this.

There’s also a tracksegments file as at one stage, we thought we needed a way of tracking segments (those are equivalent to an unbroken line on the drawings or a row in the tracklines table) with uuids. I’m not sure we need this any longer and it might disappear from future versions of the schema.

So there it is, I encourage you to try it out if you have some GPX data floating around you want to visualise in Qgis or if you’re feeling brave, Peter’s fantastic drawinglife application. I’m also starting to write analyses of this data in Python which is pretty simple using pyspatialite and because it’s based on sqlite, the whole thing is really easy to script for, just being one, light file, rather than all that server client architecture the bigger databases require.

Feedback, as always, greatly appreciated.

Posted in Code, Diary, GPS, Linux, Python, Software | Tagged , , , , , , , , , , , , , , , , | 1 Comment

Residency at the Salzamt, Linz

In preparation for the exhibition walks and traces … Off the Map, I accepted the very wonderful Sibylle Ettengruber’s invitation to come and work at the Atelierhaus Salzamt here in Linz.

I have built a second drawing machine (first one documented here) because the idea of the relationship between the GPS as a drawing machine and these sort of aleatoric, body-bound, Victorian-inspired eccentric movement sensors continues to fascinate me.

Drawing Machine 2I keep on thinking of the term ‘expanded drawing’ – a sort of tribute to expanded cinema. I’ll have to see how that weathers – I suspect that there are other terms for different sorts of drawing, as I’m sure that drawing is something that people have thought about a lot. There is the whole debate about the spectrum of drawing from representation to mark-making record of a gesture. However, what distinguishes these machine-made drawings (I am including GPS in this) is that like cinema, they result of some mechanical mediation and I’m interested to find out if there is any writing about this aspect.

I had to steel myself to make the drawings as I felt very conspicuous. Unlike the first drawing machine, this one straps to the front of the body. In fact the first ones I made in the evening after a beer, but I had some nice exchanges with curious people, one man who was really engaged and another, drunken one who observed that “Es ist immer Schamhaar” (It’s always pubic hair). He’s got a point. Won’t be putting that in the exhibition notes though.

Posted in Diary, Walking | Tagged , , , , , , | Comments Off

Goodbye Jitsi, hello again Pidgin

So the world of jabber communication has been a bit unreliable. After successfully trying a jitsi video conversation at home between my distro and Soph’s ubuntu, I tried to have a meeting with someone I’m mentoring and it totally failed. On agreeing to the call, it would hang up. After that I had a successful video meeting with someone and then it failed again for Soph and I.

Because I’m committed to open source, I persisted and discovered something surprising about an old friend. I’ve had pidgin knocking around for a while and occasionally use it to (text only) chat with the rare geeky friends I have. I even put it in my autostart config so it starts automatically (by the way, I found getting a programme to start automatically at startup much easier on my Crunchbang distro using Openbox than in Ubuntu). Then I discovered that pidgin has audio and a video calling! You just right click on your contact and select ‘Audio call’ or ‘Audio/Video call’. Can’t think why I missed this before.

Now I’m happily using good old pidgin, which I think is a bit more mature than Jitsi (I hated their taskbar icon anyway).

Posted in Diary, Linux, Software | Tagged , , | Comments Off

Goodbye Skype, hello Jitsi

In the spirit of Tino Sehgal, who not only refuses to travel by plane himself, but also insists that the performers of his work don’t travel by plane, I am responding to people who want to Skpe(tm) me by asking them to talk over Jitsi.

This is achieved in the following steps:

  1. Download Jitsi https://jitsi.org/
  2. Get an XMPP account – there’s a list here: https://xmpp.net/directory.php or just plump for http://jabber.hot-chilli.net/ which is good for English speakers ;) (lots are German). You can even use GoogleTalk, but let’s not eh?
  3. Add this newly created account to Jitsi. File > Add new account, Select network > XMPP, full jabber id which should look like an id address e.g. danbelasco@jabber.org
  4. Make Jitsi more secure following instructions here https://securityinabox.org/en/jitsi_account#2.4
Posted in Software | Tagged , , , | Comments Off