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 \
  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
 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:
  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
Docker Root Dir: /var/lib/docker
Debug Mode (client): false
Debug Mode (server): false
Registry: https://index.docker.io/v1/
Experimental: false
Insecure Registries:
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
    START 1
    MAXVALUE 2147483647
    CACHE 1;

CREATE SEQUENCE public.gpx_tracks_ogc_fid_seq
    START 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)
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
TABLESPACE pg_default;

The wrapper script

 1 #!/bin/sh
 3 DB=tracklog
 5 for f in "$@"; do
 6         psql tracklog <<EOF
 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
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;
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;
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;
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;
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).

Jul 162018

So, the local media here (can’t comment for other parts of the world) have been quite busy reporting on the fate of The Wild Boars soccer team and their coach, stuck in a flooded cave in Thailand.  With the great work of many, the group is now free of the cave, and getting the medical attention they need.

Pats on the back all around.  It could have very well been a dozen funerals that needed to be organised instead of servings of various meals.

Overshadowing this somewhat, has been the somewhat childish spat between Vern Unsworth and Elon Musk over the miniature submarine that was proposed as a vehicle for transporting the children through the cave system.

Now, I’ll admit right up front, what I know is what I’ve heard from the media here.  In amongst the reports, it was commented that the gaps though which people had to squeeze through, were as small as 38cm in places.

That does not leave you much room.  That’s bloody confined in the extreme.  A submarine that could fit a child and squeeze thorough such a gap?  It’d be positively claustrophobic!

Now, Mr Unsworth did label this as a PR stunt.  Maybe it was … maybe the design was just naïve.  I think the goal was a noble one, and Elon Musk’s team did a great job in giving it a go, even if they did overlook a few critical details.

However, I think I’ll take Mr Unsworth’s advice over Mr Musk’s regarding whether the device was practical, as he was actually there.  If the device got stuck, the results could have been fatal.  The team was already in a dangerous situation and had lost one member of their team already, they really weren’t in a position to experiment.  I think responding with “stick it where it hurts” is being overly harsh, but otherwise I think the criticism was entirely valid.

You do not, however, call someone a “pedo”, without very good grounds for doing so.  That is slanderous.  And what exactly is “sus” about living in Thailand?  Tesla’s been suffering some quite bad press lately, I really do not think this juvenile behaviour helps anyone.

One is free to believe that ego is not a dirty word, but that does not mean one’s humility should be locked under the stairs!

Update 2018-07-17: Hmm, I was saying…? Tesla sheds almost $US2b after Elon Musk’s ‘pedo’ attack on British diver.