Aug 302018
 

So, I’m happy enough with the driver now that I’ll collapse down the commits and throw it up onto the Github repository.  I might take another look at kernel 4.18, but for now, you’ll find them on the ts7670-4.14.67 branch.

Two things I observe about this voltage monitor:

  1. The voltage output is not what you’d call, accurate.  I think it’s only a 10-bit ADC, which is still plenty good enough for this application, but the reading I think is “high” by about 50mV.
  2. There’s significant noise on the reading, with noticeable quantisation steps.

Owing to these, and to thwart the possibility of using this data in side-channel attacks using power analysis, I’ve put a 40-sample moving-average filter on the “public” data.

Never the less, it’s a handy party trick, and not one I expected these devices to be able to do.  My workplace manages a big fleet of these single-board computers in the residential towers at Barangaroo where they spend all day polling Modbus and M-Bus meters.  In the event we’re at all suspicious about DC power supplies though, it’s a simple matter to load this kernel tree (they already run U-Boot) and configure collectd (which is also installed).

I also tried briefly switching off the mains power to see that I was indeed reading the battery voltage and not just a random number that looked like the voltage.  That yielded an interesting effect:

You can see where I switched the mains supply off, and back on again.  From about 8:19PM the battery voltage predictably fell until about 8:28PM where it was at around 12.6V.

Then it did something strange, it rose about 100mV before settling at 12.7V.  I suspect if I kept it off all night it’d steadily decrease: the sun has long set.  I’ve turned the mains charger back on now, as you can see by the step-rise shortly after 8:44PM.

The bands on the above chart are the alert zones.  I’ll get an email if the battery voltage strays outside of that safe region of 12-14.6V.  Below 12V, and I run the risk of deep-cycling the batteries.  Above 14.6V, and I’ll cook them!

The IPMI BMCs on the nodes already sent me angry emails when the battery got low, so in that sense, Grafana duplicates that, but does so with pretty charts.  The BMCs don’t see when the battery gets too high though, for the simple matter that what they see is regulated by LDOs.

Aug 302018
 

I’ve succeeded in getting a working battery monitor kernel module. This is basically taking the application note by Technologic Systems and spinning that into a power supply class driver that reports the voltage via sysfs.

As it happens, the battery module in collectd does not see this as a “battery”, something I’ll look at later. For now the exec plug-in works well enough. This feeds through eventually to an InfluxDB database with Grafana sitting on top.

https://netmon.longlandclan.id.au/d/IyZP-V2mk/battery-voltage?orgId=1

Aug 282018
 

So, I successfully last night, parted the core bits out of ts_wdt.c and make ts-mcu-core.c.  This is a multi-function device, and serves to provide a shared channel for the two drivers that’ll use it.

Tonight, I took a stab at writing the PSU part of it.  Suffice to say, I’ve got work to do:

[  158.712960] Unable to handle kernel NULL pointer dereference at virtual address 00000005
[  158.721328] pgd = c3854000
[  158.724089] [00000005] *pgd=4384f831, *pte=00000000, *ppte=00000000
[  158.730629] Internal error: Oops: 1 [#3] ARM
[  158.734947] Modules linked in: 8021q garp mrp stp llc nf_conntrack_ipv4 nf_defrag_ipv4 iptable_filter ip_tables xt_tcpudp nf_conntrack_ipv6 nf_defrag_ipv6 xt_conntrack nf_conntrack ip6table_filter ip6_tables x_tables flexcan can_dev
[  158.755812] CPU: 0 PID: 2059 Comm: cat Tainted: G      D         4.14.67-vrt-ts7670+ #3
[  158.763840] Hardware name: Freescale MXS (Device Tree)
[  158.769008] task: c68f3a20 task.stack: c3846000
[  158.773598] PC is at ts_mcu_transfer+0x1c/0x48
[  158.778073] LR is at 0x3
[  158.780630] pc : []    lr : [<00000003>]    psr: 60000013
[  158.786918] sp : c3847e44  ip : 00000000  fp : 014000c0
[  158.792165] r10: c5035000  r9 : c5305900  r8 : c777b428
[  158.797412] r7 : c0a7fa80  r6 : c777b400  r5 : c5035000  r4 : c3847e6c
[  158.803961] r3 : c3847e58  r2 : 00000001  r1 : c3847e4c  r0 : c07b8c68
[  158.810512] Flags: nZCv  IRQs on  FIQs on  Mode SVC_32  ISA ARM  Segment none
[  158.817671] Control: 0005317f  Table: 43854000  DAC: 00000051
[  158.823440] Process cat (pid: 2059, stack limit = 0xc3846190)
[  158.829212] Stack: (0xc3847e44 to 0xc3848000)
[  158.833611] 7e40:          c05bd150 00000001 00010000 00000004 c3847e48 00000150 c5035000
[  158.841833] 7e60: c777b420 c05bcaa4 c4f70c60 c777e070 c0a7fa80 c05bca20 00000fff c07ad4b0
[  158.850051] 7e80: c777b428 c04e46dc c4f52980 00001000 00000fff c01b1994 c4f52980 c4f70c60
[  158.858268] 7ea0: c3847ec8 ffffe000 00000000 c3847f88 00000001 c0164eac c3847fb0 c4f529b0
[  158.866487] 7ec0: 00020000 b6e3d000 00000000 00000000 c4f73f70 00000800 00000000 c01b0f60
[  158.874703] 7ee0: 00020000 c4f70c60 ffffe000 c3847f88 00000000 00000000 00000000 c013eb84
[  158.882918] 7f00: 000291ac 00000000 00000000 c0009344 00000077 b6e3c000 00000022 00000022
[  158.891135] 7f20: c686bdc0 c0117838 000b6e3c c3847f80 00022000 c686be14 b6e3c000 00000000
[  158.899354] 7f40: 00000000 00022000 b6e3d000 00020000 c4f70c60 ffffe000 c3847f88 c013ed0c
[  158.907571] 7f60: 00000022 00000000 000b6e3c c4f70c60 c4f70c60 b6e3d000 00020000 c000a9e4
[  158.915786] 7f80: c3846000 c013f2d8 00000000 00000000 00000000 00000000 00000000 00000000
[  158.924002] 7fa0: 00000003 c000a820 00000000 00000000 00000003 b6e3d000 00020000 00000000
[  158.932217] 7fc0: 00000000 00000000 00000000 00000003 00020000 00000000 00000001 00000000
[  158.940434] 7fe0: be8a62c0 be8a62ac b6eb77c4 b6eb6b9c 60000010 00000003 00000000 00000000
[  158.948691] [] (ts_mcu_transfer) from [] (ts_psu_get_prop+0x38/0xb0)
[  158.956847] [] (ts_psu_get_prop) from [] (power_supply_show_property+0x84/0x220)
[  158.966036] [] (power_supply_show_property) from [] (dev_attr_show+0x1c/0x48)
[  158.974974] [] (dev_attr_show) from [] (sysfs_kf_seq_show+0x84/0xf0)
[  158.983129] [] (sysfs_kf_seq_show) from [] (seq_read+0xcc/0x4f4)
[  158.990930] [] (seq_read) from [] (__vfs_read+0x1c/0x11c)
[  158.998117] [] (__vfs_read) from [] (vfs_read+0x88/0x158)
[  159.005304] [] (vfs_read) from [] (SyS_read+0x3c/0x90)
[  159.012232] [] (SyS_read) from [] (ret_fast_syscall+0x0/0x28)
[  159.019766] Code: e52de004 e281300c e590e004 e25cc001 (e1dee0b2) 
[  159.026278] ---[ end trace 2807dc313991fd87 ]---

The good news is the machine didn’t crash.c

Aug 262018
 

So, I had a brief look after getting kernel 4.18.5 booting… sure enough the problem was I had forgotten the watchdog, although I did see btrfs trigger a deadlock warning, so I may not be out of the woods yet.  I’ve posted the relevant kernel output to the linux-btrfs list.

Anyway, as it happens, that watchdog driver looks like it’ll need some re-factoring as a multi-function device.  At the moment, ts-wdt.c claims it based on this binding.

If I try to add a second driver, they’ll clash, and I expect the same if I try to access it via userspace.  So the sensible thing to do here, is to add a ts-companion.c MFD driver here, then re-factor ts-wdt.c to use it.  From there, I can write a ts-psu.c module which will go right here.

I think I’ll definitely be digging into those older sources to remind myself how that all worked.

Aug 252018
 

So, after some argument, and a bit of sitting on a concrete floor with the netbook, I managed to get Gentoo loaded onto the TS-7670.  Right now it’s running off the MicroSD card, I’ll get things right, then shift it across to eMMC.

ts7670 ~ # emerge --info
Portage 2.3.40 (python 3.5.5-final-0, default/linux/musl/arm/armv7a, gcc-6.4.0, musl-1.1.19, 4.14.15-vrt-ts7670-00031-g1a006273f907-dirty armv5tejl)
=================================================================
System uname: Linux-4.14.15-vrt-ts7670-00031-g1a006273f907-dirty-armv5tejl-ARM926EJ-S_rev_5_-v5l-with-gentoo-2.4.1
KiB Mem:      111532 total,     13136 free
KiB Swap:    4194300 total,   4191228 free
Timestamp of repository gentoo: Fri, 17 Aug 2018 16:45:01 +0000
Head commit of repository gentoo: 563622899f514c21f5b7808cb50f6e88dbd7d7de
sh bash 4.4_p12
ld GNU ld (Gentoo 2.30 p2) 2.30.0
app-shells/bash:          4.4_p12::gentoo
dev-lang/perl:            5.24.3-r1::gentoo
dev-lang/python:          2.7.14-r1::gentoo, 3.5.5::gentoo
dev-util/pkgconfig:       0.29.2::gentoo
sys-apps/baselayout:      2.4.1-r2::gentoo
sys-apps/openrc:          0.34.11::gentoo
sys-apps/sandbox:         2.13::musl
sys-devel/autoconf:       2.69-r4::gentoo
sys-devel/automake:       1.15.1-r2::gentoo
sys-devel/binutils:       2.30-r2::gentoo
sys-devel/gcc:            6.4.0-r1::musl
sys-devel/gcc-config:     1.8-r1::gentoo
sys-devel/libtool:        2.4.6-r3::gentoo
sys-devel/make:           4.2.1::gentoo
sys-kernel/linux-headers: 4.13::musl (virtual/os-headers)
sys-libs/musl:            1.1.19::gentoo
Repositories:

gentoo
    location: /usr/portage
    sync-type: rsync
    sync-uri: rsync://virtatomos.longlandclan.id.au/gentoo-portage
    priority: -1000
    sync-rsync-verify-jobs: 1
    sync-rsync-extra-opts: 
    sync-rsync-verify-metamanifest: yes
    sync-rsync-verify-max-age: 24

ACCEPT_KEYWORDS="arm"
ACCEPT_LICENSE="* -@EULA"
CBUILD="arm-unknown-linux-musleabi"
CFLAGS="-Os -pipe -march=armv5te -mtune=arm926ej-s -mfloat-abi=soft"
CHOST="arm-unknown-linux-musleabi"
CONFIG_PROTECT="/etc /usr/share/gnupg/qualified.txt"
CONFIG_PROTECT_MASK="/etc/ca-certificates.conf /etc/env.d /etc/gconf /etc/gentoo-release /etc/sandbox.d /etc/terminfo"
CXXFLAGS="-Os -pipe -march=armv5te -mtune=arm926ej-s -mfloat-abi=soft"
DISTDIR="/home/portage/distfiles"
ENV_UNSET="DBUS_SESSION_BUS_ADDRESS DISPLAY PERL5LIB PERL5OPT PERLPREFIX PERL_CORE PERL_MB_OPT PERL_MM_OPT XAUTHORITY XDG_CACHE_HOME XDG_CONFIG_HOME XDG_DATA_HOME XDG_RUNTIME_DIR"
FCFLAGS="-O2 -pipe -march=armv7-a -mfpu=vfpv3-d16 -mfloat-abi=hard"
FEATURES="assume-digests binpkg-logs config-protect-if-modified distlocks ebuild-locks fixlafiles merge-sync multilib-strict news parallel-fetch preserve-libs protect-owned sandbox sfperms strict unknown-features-warn unmerge-logs unmerge-orphans userfetch userpriv usersandbox usersync xattr"
FFLAGS="-O2 -pipe -march=armv7-a -mfpu=vfpv3-d16 -mfloat-abi=hard"
GENTOO_MIRRORS=" http://virtatomos.longlandclan.id.au/portage http://mirror.internode.on.net/pub/gentoo http://ftp.swin.edu.au/gentoo http://mirror.aarnet.edu.au/pub/gentoo"
INSTALL_MASK="charset.alias"
LANG="en_AU.UTF-8"
LDFLAGS="-Wl,-O1 -Wl,--as-needed"
PKGDIR="/usr/portage/packages"
PORTAGE_CONFIGROOT="/"
PORTAGE_RSYNC_OPTS="--recursive --links --safe-links --perms --times --omit-dir-times --compress --force --whole-file --delete --stats --human-readable --timeout=180 --exclude=/distfiles --exclude=/local --exclude=/packages --exclude=/.git"
PORTAGE_TMPDIR="/var/tmp"
USE="arm bindist cli crypt cxx dri fortran iconv ipv6 modules ncurses nls nptl openmp pam pcre readline seccomp ssl tcpd unicode xattr zlib" APACHE2_MODULES="authn_core authz_core socache_shmcb unixd actions alias auth_basic authn_alias authn_anon authn_dbm authn_default authn_file authz_dbm authz_default authz_groupfile authz_host authz_owner authz_user autoindex cache cgi cgid dav dav_fs dav_lock deflate dir disk_cache env expires ext_filter file_cache filter headers include info log_config logio mem_cache mime mime_magic negotiation rewrite setenvif speling status unique_id userdir usertrack vhost_alias" CALLIGRA_FEATURES="karbon plan sheets stage words" COLLECTD_PLUGINS="df interface irq load memory rrdtool swap syslog" ELIBC="musl" GPSD_PROTOCOLS="ashtech aivdm earthmate evermore fv18 garmin garmintxt gpsclock isync itrax mtk3301 nmea ntrip navcom oceanserver oldstyle oncore rtcm104v2 rtcm104v3 sirf skytraq superstar2 timing tsip tripmate tnt ublox ubx" INPUT_DEVICES="libinput keyboard mouse" KERNEL="linux" LCD_DEVICES="bayrad cfontz cfontz633 glk hd44780 lb216 lcdm001 mtxorb ncurses text" LIBREOFFICE_EXTENSIONS="presenter-console presenter-minimizer" OFFICE_IMPLEMENTATION="libreoffice" PHP_TARGETS="php5-6 php7-0" POSTGRES_TARGETS="postgres9_5 postgres10" PYTHON_SINGLE_TARGET="python3_6" PYTHON_TARGETS="python2_7 python3_6" RUBY_TARGETS="ruby23" USERLAND="GNU" VIDEO_CARDS="dummy fbdev v4l" XTABLES_ADDONS="quota2 psd pknock lscan length2 ipv4options ipset ipp2p iface geoip fuzzy condition tee tarpit sysrq steal rawnat logmark ipmark dhcpmac delude chaos account"
Unset:  CC, CPPFLAGS, CTARGET, CXX, EMERGE_DEFAULT_OPTS, LC_ALL, LINGUAS, MAKEOPTS, PORTAGE_BINHOST, PORTAGE_BUNZIP2_COMMAND, PORTAGE_COMPRESS, PORTAGE_COMPRESS_FLAGS, PORTAGE_RSYNC_EXTRA_OPTS

I still have to update the kernel.  I actually did get kernel 4.18 to boot, but I forgot to add in support for the watchdog, so U-Boot tickled it, then the watchdog got hungry and kicked the reset half way through the boot sequence.

Rolling back to my older 4.14 kernel works.  I’ll try again with 4.18.5 in a moment.  Failing that, I have also brought the 4.14 patches up to 4.14.69 which is the latest LTS release of the kernel.

I’ve started looking at the power supply sysfs device class, with a view to exposing the supply voltage via sysfs.  The thinking here is that collectd supports reading this via the “battery” module (and realistically, it is a battery that is being measured: two 105Ah AGMs).

Worst case is I do something a little proprietary and deal with it in user space.  I’ll have to dig up the Linux kernel tree I did for Jacques Electronics all those years ago, as that had some examples of interfacing sysfs to a Cypress PSOC device that was acting as an I²C slave.  Rather than using an off-the-shelf solution, they programmed up a MCU that did power management, touchscreen sensing, keypad sensing, RGB LED control and others, all in one chip.  (Fun to try and interface that to the Linux kernel.)

Technologic Systems appear to have done something similar.  The device ID 0x78 implies a 10-bit device, but I think they’re just squatting on that 7-bit address.  They hail 0x78 then read out 4 bytes, which the last two bytes are the supply voltage ADC readings.  They do their own byte swapping before scaling the value to get mV.

Aug 222018
 

It’s taken several months and had a few false starts, but at long last I have some stage tarballs for Gentoo Linux MUSL for ARMv5 processors.  I’m not the only one wanting such a port, even looking for my earlier thread on the matter, I stumbled on this post.  (Google translate is hopeless with Russian, but I can get the gist of what’s being said.)

This was natively built on the TS-7670 using an external hard drive connected over USB 2.0 as both swap and the chroot.  It took two passes to clean everything up and get something that’s in a “release-able” state.

I think my next steps now will be:

  • Build an updated kernel … I might see if I can expose that I²C register via a sysfs file or something that collectd can pick up whilst I’m at it.  I have the kernel sources and bootloader sources.
  • Prepare the 32GB MicroSD card I bought a few weeks back with the needed partitions and load Gentoo onto that.
  • Install the MicroSD card and boot off it.
  • Back up the eMMC
  • Re-format the eMMC and copy the MicroSD card to it.

It’s supposed to be wet this weekend, so it sounds like a good project for indoors.

Aug 212018
 

I have a bad habit where it comes to updating systems, I tend to do it less frequently than I should, and that can sometimes snowball like it has for my mail server.  Even if it’s a fresh install, sometimes there’s a large number of packages that need installing.

Now Portage does report where it’s up to, but often that has long scrolled past the buffer on your terminal.  You can look at /var/log/emerge.log for this information, but sometimes it’s nice to just see a percentage progress and a pseudo graphical representation.

With this in mind, I cooked up a little script which just tails /var/log/emerge.log and displays a progress bar along with the last message reported. The script is quite short:

#!/bin/bash

shopt -s checkwinsize

stdbuf -o L tail -n 0 -F /var/log/emerge.log | while read line; do
	changed=0
	eval $( echo ${line} | \
		sed -ne '/[0-9]\+ of [0-9]\+/ { s:^.*(\([0-9]\+\) of \([0-9]\+\)).*$:done=\1 total=\2 changed=1:; p; }' )

	if [ "${changed}" = 1 ]; then
		case "${line}" in
			*"::: completed emerge"*)
				;;
			*)
				done=$(( ${done} - 1 ))
				;;
		esac

		percent=$(( ( ${done}*100 ) / ${total} ))
		width=$(( ${COLUMNS:-80} - 8 ))
		progress=$(( ( ${done}*${width} ) / ${total} ))
		remain=$(( ${width} - ${progress} ))

		progressbar="$( for n in $( seq 1 ${progress} ); do echo -n '#'; done )"
		remainbar="$( for n in $( seq 1 ${remain} ); do echo -n ':'; done )"

		printf '\033[2A\033[2K%s\n\033[2K\033[1G[\033[1m%s\033[0m%s] \033[1m%3d%%\033[0m\n' \
			"${line:0:${COLUMNS:-80}}" "$progressbar" "$remainbar" "$percent"
	else
		printf '\033[2A\033[2K%s\n\n' "${line:0:${COLUMNS:-80}}"
	fi

	if echo "${line}" | grep -q '*** terminating.'; then
		exit
	fi
done

What’s it look like?

It works well with GNU Screen as seen above.

Aug 192018
 

So, I was just updating the project details for this project, and I happened to see this blog post about reading the DC voltage input on the TS-7670v2.

I haven’t yet gotten around to finishing the power meters that I was building which would otherwise be reading these values directly, but they were basically going to connect via Modbus to the TS-7670v2 anyway.  One of its roles, aside from routing between the physical management network (IPMI and switch console access), was to monitor the battery.

I will have to explore this.  Collectd doesn’t have a general-purpose I²C module, but it does have one for barometer modules, so with a bit of work, I could make one to measure the voltage input which would tell me what the battery is doing.

Jul 232018
 

Lately, I’ve been doing a lot of development work on Tridium Niagara kit.  The Tridium platform is fundamentally built on Sun^WOracle’s Java environment, and is very popular in the building management industry.  There’s an estimate of over 600000 JACE devices (building management controllers) deployed worldwide, so I can fully understand why my workplace is chasing them.

That means coming to grips with their environment, and getting it to talk to ours.  Officially, VRT is a Debian/Ubuntu shop.  They used to dabble with Red Hat years ago, back when VRT and Red Hat were next-door neighbours (in Gardner Close, Milton) but VRT switched to Ubuntu around 2008 after a brief flirt with Gentoo.  Thus, must of our tooling assumes a Debian-based system.

Docker CE on Debian and Ubuntu is a snap.  However, Tridium it would seem, are Red Hat fans, and only support their development environment on Microsoft Windows (yes shudder) or Red Hat Enterprise Linux.  Thus, we have a RHEL 7.3 VM we pass around when we’re doing VM development.  I figured since we’re trying to link Niagara to WideSky, it would be nice to be able to deploy WideSky on RHEL.

WideSky uses Docker as the basis for its deployment, so this sounded simple enough.  Install Docker and docker-compose, throw a bog-standard deployment in there, docker-compose up -d, off we go.

Not so fast.

While there’s Docker EE for RHEL, budget is tight and we really don’t need the support as this isn’t a “production” instance as such.  If the VM gets sick we just roll it back to a known good version and continue from there.  It doesn’t make sense to spend money on purchasing Docker EE.  There’s a CentOS version of Docker CE, and even unofficial instructions on how to shoehorn this into RHEL.  I dutifully followed these, but then hit a road-block with container-selinux: the repository no longer has that version.

Rather than looking for what version they have now, or play Russian Roulette hunting for a random RPM from some mirror site (been there, done that many moons ago before I knew better)… a better plan was to grab the sources and sic rpmbuild onto them so we get a RHEL-native binary.

Building container-selinux on RHEL

  1. Begin by installing dependencies:
    # yum install -y selinux-policy selinux-policy-devel rpm-build rpm-devel git
  2. Download the sources for the RPM:
    $ git clone https://git.centos.org/r/rpms/container-selinux.git
    $ git checkout c7-alt
    $ cd SPECS
  3. Have a look at the .spec file to see where it expects to source the sources from, up the top of the file I downloaded, I saw:
    %global git0 https://github.com/projectatomic/%{name}
    %global commit0 dfb449b771ca4977bb7d5fb6cd7be3cfc14d6fca
  4. Fetch the sources, then check out that commit:
    $ git clone https://github.com/projectatomic/container-selinux
    $ git checkout dfb449b771ca4977bb7d5fb6cd7be3cfc14d6fca
  5. Rename the check-out directory as container-selinux-${GIT_COMMIT_ID}
    $ cd ..
    $ mv container-selinux container-selinux-dfb449b771ca4977bb7d5fb6cd7be3cfc14d6fca
  6. Package it up into a tarball, excluding the .git directory and plop that file in ~/rpmbuild/SOURCES
    $ tar --exclude container-selinux-dfb449b771ca4977bb7d5fb6cd7be3cfc14d6fca/.git \
    -czvf ~/rpmbuild/sources/container-selinux-dfb449b.tar.gz \
    container-selinux-dfb449b771ca4977bb7d5fb6cd7be3cfc14d6fca
  7. Build!
    $ rpmbuild -ba container-selinux.spec

All going to plan, you should have a shiny new RPM file in ~/rpmbuild/RPMS.  Install that, then you can proceed with installing the CentOS version of Docker CE.  If you’re doing this for a production environment, and absolutely must use Docker CE, then I’d advise that perhaps taking the source RPMs for Docker CE and building those on RHEL would be advisable over using raw CentOS binaries, but each to your own.

# docker info
Containers: 0
 Running: 0
 Paused: 0
 Stopped: 0
Images: 0
Server Version: 18.06.0-ce
Storage Driver: overlay2
 Backing Filesystem: xfs
 Supports d_type: true
 Native Overlay Diff: true
Logging Driver: json-file
Cgroup Driver: cgroupfs
Plugins:
 Volume: local
 Network: bridge host macvlan null overlay
 Log: awslogs fluentd gcplogs gelf journald json-file logentries splunk syslog
Swarm: inactive
Runtimes: runc
Default Runtime: runc
Init Binary: docker-init
containerd version: d64c661f1d51c48782c9cec8fda7604785f93587
runc version: 69663f0bd4b60df09991c08812a60108003fa340
init version: fec3683
Security Options:
 seccomp
  Profile: default
Kernel Version: 3.10.0-693.11.1.el7.x86_64
Operating System: Red Hat Enterprise Linux
OSType: linux
Architecture: x86_64
CPUs: 4
Total Memory: 3.702GiB
Name: localhost.localdomain
ID: YVHJ:UXQV:TBAS:E5MH:B4GL:VT2H:A2BW:MQMF:3AGA:FBBX:MINO:24Z6
Docker Root Dir: /var/lib/docker
Debug Mode (client): false
Debug Mode (server): false
Registry: https://index.docker.io/v1/
Labels:
Experimental: false
Insecure Registries:
 127.0.0.0/8
Live Restore Enabled: false
Jul 222018
 

So, on the bike, I use a portable GPS to keep track of my speed and to track the mileage done on the bike so I know when to next put it in for service. Originally I just relied on the trip counter in the GPS, but then found that this could develop quite an error if left to tick over for a few months.

Thus, I wrote a simple CGI application in Perl and SQLite3 that would track the odometer readings. Plain, simple, and it’s worked quite well, but remembering to punch in the current odometer reading is a chore, and my stats are only as granular as I submit: if I want to see what distance I did on a particular day, I either have to have had the foresight to store readings at the start and end of that day, or I’m stuffed.

I also keep the GPX tracklogs. While the Garmin 650 is not great at handling lots of tracklogs (and for some moronic reason, they name the files “Day DD-MMM-YY HH.MM.SS.gpx”, not something sensible like “YYYY-MM-DDTHH-MM-SS.gpx”), it’s good enough that I can periodically siphon off the track logs for storage on my laptop. I then have a record of where I’ve been.

Theoretically, this also has the distance travelled, I could make a service that just consumes the GPX files, and tallies up the distances that way. Maybe even visualise heat-map style, where I go most. (No prizes for guessing “work” … but where else?)

The existing system uses SQLite, and specifically, its views, as poor man’s stored procedures. It’s hacky, inefficient, and sooner or later I’ll have performance problems. PostGIS is an extension onto PostgreSQL which supports a large number of spatial operations, including finding the length of a series of points, which is exactly the problem I’m trying to solve right now. The catch is, how do you import the data?

Enter GDAL

GDAL is a library of geographic functions for answering these kinds of questions. It ships with a utility ogr2ogr, which can take geographic information in a variety of formats, and convert to a variety of output formats. Crucially, this tool supports consuming GPX files and writing to a PostGIS database.

Loading one file, is easy enough:

$ ogr2ogr -oo GPX_ELE_AS_25D=YES \
  -dim 3 \
  -gt 65536 \
  -lco GEOM_TYPE=geography \
  -preserve_fid \
  -f PostgreSQL \
  "PG:dbname=yourdb" yourfile.gpx \
  tracks track_points

The arguments here were found by trial-and-error.  Specifically, -oo GPX_ELE_AS_25D=YES and -dim 3 tell ogr2ogr to preserve the elevation in the point information (as well as keeping a copy of it in the ele column). -lco GEOM_TYPE=geography tells ogr2ogr to use the geography data type in PostGIS.

Look in the database, and you’ll see two tables, tracks and track_points. Sadly, you don’t get to choose the names of these (not easily anyway, there is -nln, but it then will create one table with the given name, put the tracks in it, then blow it away and replace it with a table of the same name containing points), and there’s no foreign keys between the two.

The fun starts when you try to import a second GPX file. Run that command again, and because of -preserve_fid, you’ll get a primary key clash. Take that away, and the track_fid column in track_points becomes meaningless.

If you drop -preserve_fid, then track_fid gets set to 0 for all points.  Useless.

Importing many GPX files

Out of the box, this just wasn’t going to fly, so we needed to do things a little different.  Firstly, I duplicated the schema that GDAL creates, creating my own tables which will ultimately store the data.  I then used a wrapper shell script that calls psql before and after ogr2ogr so I can re-map the primary keys to maintain relationships.

Schema SQL

CREATE SEQUENCE public.gpx_points_ogc_fid_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 2147483647
    CACHE 1;

CREATE SEQUENCE public.gpx_tracks_ogc_fid_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 2147483647
    CACHE 1;

CREATE TABLE public.gpx_tracks
(
    ogc_fid integer NOT NULL,
    name character varying COLLATE pg_catalog."default",
    cmt character varying COLLATE pg_catalog."default",
    "desc" character varying COLLATE pg_catalog."default",
    src character varying COLLATE pg_catalog."default",
    link1_href character varying COLLATE pg_catalog."default",
    link1_text character varying COLLATE pg_catalog."default",
    link1_type character varying COLLATE pg_catalog."default",
    link2_href character varying COLLATE pg_catalog."default",
    link2_text character varying COLLATE pg_catalog."default",
    link2_type character varying COLLATE pg_catalog."default",
    "number" integer,
    type character varying COLLATE pg_catalog."default",
    gpxx_trackextension character varying COLLATE pg_catalog."default",
    the_geog geography(MultiLineStringZ,4326),
    CONSTRAINT gpx_tracks_pkey PRIMARY KEY (ogc_fid)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

CREATE TABLE public.gpx_points
(
    ogc_fid integer NOT NULL,
    track_fid integer,
    track_seg_id integer,
    track_seg_point_id integer,
    ele double precision,
    "time" timestamp with time zone,
    magvar double precision,
    geoidheight double precision,
    name character varying COLLATE pg_catalog."default",
    cmt character varying COLLATE pg_catalog."default",
    "desc" character varying COLLATE pg_catalog."default",
    src character varying COLLATE pg_catalog."default",
    link1_href character varying COLLATE pg_catalog."default",
    link1_text character varying COLLATE pg_catalog."default",
    link1_type character varying COLLATE pg_catalog."default",
    link2_href character varying COLLATE pg_catalog."default",
    link2_text character varying COLLATE pg_catalog."default",
    link2_type character varying COLLATE pg_catalog."default",
    sym character varying COLLATE pg_catalog."default",
    type character varying COLLATE pg_catalog."default",
    fix character varying COLLATE pg_catalog."default",
    sat integer,
    hdop double precision,
    vdop double precision,
    pdop double precision,
    ageofdgpsdata double precision,
    dgpsid integer,
    the_geog geography(PointZ,4326),
    CONSTRAINT gpx_points_pkey PRIMARY KEY (ogc_fid),
    CONSTRAINT gpx_points_track_fid_fkey FOREIGN KEY (track_fid)
        REFERENCES public.gpx_tracks (ogc_fid) MATCH SIMPLE
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

The wrapper script

 1 #!/bin/sh
 2 
 3 DB=tracklog
 4 
 5 for f in "$@"; do
 6         psql tracklog <<EOF
 7 DROP TABLE IF EXISTS tracks;
 8 DROP TABLE IF EXISTS track_points;
 9 EOF
10         ogr2ogr -oo GPX_ELE_AS_25D=YES \
11                 -dim 3 \
12                 -gt 65536 \
13                 -lco SPATIAL_INDEX=FALSE \
14                 -lco GEOM_TYPE=geography \
15                 -overwrite \
16                 -preserve_fid \
17                 -f PostgreSQL \
18                 "PG:dbname=${DB}" "$f" \
19                 tracks track_points
20 
21         # Re-map FIDs then insert into real tables.
22         psql tracklog <<EOF
23         CREATE TEMPORARY TABLE track_fids AS
24         SELECT  ogc_fid AS orig_fid,
25                 nextval('gpx_tracks_ogc_fid_seq') AS ogc_fid
26         FROM    tracks;
27 
28         CREATE TEMPORARY TABLE point_fids AS
29         SELECT  ogc_fid AS orig_fid,
30                 nextval('gpx_points_ogc_fid_seq') AS ogc_fid
31         FROM    track_points;
32 
33         INSERT INTO gpx_tracks
34         SELECT  track_fids.ogc_fid AS ogc_fid,
35                 tracks.name as name,
36                 tracks.cmt as cmt,
37                 tracks."desc" as "desc",
38                 tracks.src as src,
39                 tracks.link1_href as link1_href,
40                 tracks.link1_text as link1_text,
41                 tracks.link1_type as link1_type,
42                 tracks.link2_href as link2_href,
43                 tracks.link2_text as link2_text,
44                 tracks.link2_type as link2_type,
45                 tracks."number" as "number",
46                 tracks.type as type,
47                 tracks.gpxx_trackextension as gpxx_trackextension,
48                 tracks.the_geog as the_geog
49         FROM    track_fids, tracks
50         WHERE   track_fids.orig_fid=tracks.ogc_fid;
51 
52         INSERT INTO gpx_points
53         SELECT  point_fids.ogc_fid AS ogc_fid,
54                 track_fids.ogc_fid AS track_fid,
55                 track_points.track_seg_id AS track_seg_id,
56                 track_points.track_seg_point_id AS track_seg_point_id,
57                 track_points.ele AS ele,
58                 track_points."time" AS "time",
59                 track_points.magvar AS magvar,
60                 track_points.geoidheight AS geoidheight,
61                 track_points.name AS name,
62                 track_points.cmt AS cmt,
63                 track_points."desc" AS "desc",
64                 track_points.src AS src,
65                 track_points.link1_href AS link1_href,
66                 track_points.link1_text AS link1_text,
67                 track_points.link1_type AS link1_type,
68                 track_points.link2_href AS link2_href,
69                 track_points.link2_text AS link2_text,
70                 track_points.link2_type AS link2_type,
71                 track_points.sym AS sym,
72                 track_points.type AS type,
73                 track_points.fix AS fix,
74                 track_points.sat AS sat,
75                 track_points.hdop AS hdop,
76                 track_points.vdop AS vdop,
77                 track_points.pdop AS pdop,
78                 track_points.ageofdgpsdata AS ageofdgpsdata,
79                 track_points.dgpsid AS dgpsid,
80                 track_points.the_geog AS the_geog
81         FROM    track_points, track_fids, point_fids
82         WHERE   point_fids.orig_fid=track_points.ogc_fid
83         AND     track_fids.orig_fid=track_points.track_fid;
84 
85         DROP TABLE tracks;
86         DROP TABLE track_points;
87         DROP TABLE track_fids;
88         DROP TABLE point_fids;
89 EOF
90 done

Getting the length of a track

Having imported all the data, we can do something like this:

SELECT ogc_fid, name,
  ST_Length(the_geog, false)/1000 as dist_in_km
FROM gpx_tracks order by ogc_fid desc limit 10;

and get this:

1754 Day 20-JUL-18 18:09:02′ 9.83689686312541′
1753 Day 15-JUL-18 09:36:16′ 5.75919119415676′
1752 Day 14-JUL-18 17:12:24′ 0.071734341651265′
1751 Day 14-JUL-18 17:12:23′ 0.0729574875289383′
1750 Day 13-JUL-18 08:13:32′ 9.88420745610283′
1749 Day 06-JUL-18 09:00:32′ 9.81221316219109′
1748 Day 30-JUN-18 01:11:26′ 9.77607205972035′
1747 Day 23-JUN-18 05:02:04′ 19.6368592034475′
1746 Day 22-JUN-18 18:03:37′ 9.91964760346248′
1745 Day 12-JUN-18 21:22:26′ 0.0884092391531763′

Visualisation with QGIS

Turns out, this is straightforward…

  1. In your workspace, there’s a tree with the different layer types you can add, including PostGIS… right-click on this and select New Connection… fill in the details for your PostgreSQL database.
  2. Below that is XYZ Tiles…, right click again, select New Connection for OpenStreetMap, and use the URL https://a.tile.openstreetmap.org/{z}/{x}/{y}.png (also, see their policy).
  3. Drag the OpenStreetMap connection to your layers
  4. Expand the PostGIS connection you just made, and look for the gpx_tracks table, drag this on top of your OpenStreetMap layer.

Below is everywhere I’ve been with the GPS tracklog running.  Much of what you see is the big loop a few of us did in 2012, including my trip to Ballarat for the 2012 LCA.

If I zoom in on Brisbane, unsurprisingly, some areas show up very clearly as being common haunts for me:

A bit of SQL voodoo, and I come up with this:

In orange is the territory covered on the Boulder (minus what was covered before I got the GPS), in blue the territory covered on the Talon 29 ER 0, and in red, on my current commuter (Toughroad SLR2).