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.

```\$ 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",
"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",
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,
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,
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).

For years up until last month, I ran a public NTP server which was on pool.ntp.org.  This mostly sat at Stratum II and was synchronised from random stratum I servers.

Last month, I had to knock that on the head because of a sudden spike in Internet traffic.  Through tcpdump on the border router, I identified the first culprit: NTP client traffic.  For whatever reason, I was getting a lot of traffic.  Dumping packets to a file over a 15 minute period and analysing showed about 90% of the traffic was NTP.

For about 3 weeks straight, I had an effectively constant 4~5Mbps incoming stream … a pelican flew into my inbox, carrying a AU\$300 bill for the nearly 800GB consumed in May.  (My plan was originally 250GB.  Many thanks for Internode there: they capped the overusage charge at AU\$300 and provided an instant upgrade of the plan to 500GB/month.)

The other culprit I tracked down to HackChat’s websockets, which was a big contributor to June’s data usage.  (Thank-you OpenBSD pflow and nfdump!)

So right now, I’m looking at whether I re-join the NTP server pool.  I have the monitoring in place to keep track of data usage now.  For sure the experience has cost me over \$400, but that’s still cheaper than university studies … and they didn’t teach me this stuff anyway!

One thought is that I could do away with any external NTP server altogether by using local sources to synchronise time.  With a long-wire antenna, I could sync to WWV.  Usually I can pick it up on 10MHz or 15MHz, but it’s weak, and I’d have to rig up the receiver, the antenna and a suitable decoder.

Then there’s the problem of lightning strikes, I already have a Yaesu FT-897D in the junk box thanks to Thor’s past efforts.

The more practical approach would appear to be using GPS time sync.  You can do it with any NMEA-compatible module, but you get far better results using one that supports PPS.  Some even have a 10PPS option, but I’m not sure if kpps supports that.

As it happens, I could have got a GPS module in the TS-7670.  Here’s where it is on the schematic:

and here are the connections to its UART (bottom two):

and the PPS pin (LCD_D16):

The footprints are there on the board, and I can buy the module.  No problems.  Buying the TS-7670 with the GPS option would have meant buying the top-of-the-line “development” model which would have included WiFi (not needed), an extra 128MB RAM (nice to have, but not essential) and an extra CAN port (not needed).

The other option is to go something else, such as this module.  Either way, I need to watch logic levels, Freescale like their 1.8V, although it looks like the I/O pins can be switched between 1.8V and 3.3V from the GPIO registers (chapter 9 of the datasheet).

Doing this, would allow me to run Stratum 1 within the network, and perhaps to IPv6 NTP clients.  IPv4 clients might get Stratum 2, we’ll see how I feel about it later.

So last post, I mentioned about the installation of the new battery charger, which is fed from 240V mains. Over the last few days this charger has held the batteries at a rock-solid 14.4V. Not once did the batteries drop below that voltage setpoint.

So good in fact, the solar charger does no work at all.

By the way, this is what the install looks like. I promised pictures last post.

That’s the DC end … and the nasty AC end is all sealed up…

I will eventually move this to a spot on the back of the rack, but it can sit here for now.

Ultimately, the proper fix to this will be to have the mains-powered charger power off when the sun is up. On the DC output connector, the two rightmost screw terminals go to an opto-isolator that, when powered, shuts off the charger, putting it into stand-by mode. This was one of the reasons I bought this particular unit. The other was the wide range of voltage adjustment.

The question is when to turn on, and when to go to stand-by. Basically if the following expression is true, then turn off the mains:

$(V_{batt} > 12.8) \\wedge (V_{solar} > 15)$

We do not want solar if the battery is very low, as there’s a possibility that the solar output will not be sufficient.  Likewise, if the sun’s out, we need the mains to keep the battery topped up.

The solar output is nearly always above 15V when the sun is up, so there’s our first clue.  We can safely get to 12.8V before things start going pear shaped on the cluster, so we can use that as our low-voltage safety net.  If both of these conditions are met, then it’s safe to turn off the mains power and rely on solar only.

We need a +5V signal when both these conditions are met.  This very much sounds like the job of a dual-comparator with diode-OR outputs pulling on a 5V pull-up.  Maybe a wee bit of hysteresis on those to prevent flapping, and we should be good.

Unfortunately, to do that, I need to unscrew terminals to feed some wires in.  I don’t feel like doing that just now… we’re packing up to go away for a while, and I think this sort of job can wait until we return.

In the meantime, I’ve done something of a hack.  I mentioned the PSU is adjustable.  I wound Vfloat back to 12V… thus Vboost has gone to 12.8V.  Right now, the mains PSU is showing a green LED, meaning it is in floating mode.

We have good sun right now, and the solar controller is currently boosting the battery.  When the battery gets low, the charging circuitry of the mains PSU should kick in, and bring the battery voltage up, holding it at 12.8V until the sun comes up.  I’ll leave it for now and see how this hack goes.

On other news… I might need to re-consider my NTP server arrangements.  I’m not sure if it’s a quirk of OpenBSD, or of the network here, but it seems OpenNTPD struggles to keep good time.  Never tried using the Advantech PC as a NTP server until now, and I’m also experimenting with using my VPS at Vultr as a NTP server.

http://www.pool.ntp.org/user/Redhatter

Both are drifting like crazy.  I have a GPS module lying around that I might consider hooking up to the TS-7670… perhaps make it a Stratum 1 NTP server on the NTP server pool, then the Advantech can sync to that.

This won’t help the VPS though, and I’m at a loss to explain why a Geode LX800 running on an ADSL link in my laundry, outperforms a VPS in a nicely climate-controlled data centre with gigabit Internet.

But at least now that’s one less job for my aging server.  I’ve also moved mail server duties off the old box onto a VM, so I’ll be looking at the BIOS settings there to see if I can get the box to wake up some time in the evening, let cron run the back-up jobs, then power the whole lot back down again, save some juice.

Prior to my road trip to LCA 2012 Ballarat, I bought a new toy, namely a Yaesu VX8-DR handheld.

At that point it turned up only just before I was due to leave, so I wasn’t able to get the accessories I wanted. I cobbled together my own 12V charger lead by snipping the original power supply and soldering on a cigarette lighter socket, but otherwise I used the handheld in its out-of-the-box configuration.

Having gotten back, I have purchased the FGPS-2 GPS module, CT-136 GPS adaptor and the BU-1 Bluetooth module.

### Transceiver performance

The set works quite well. The antenna is pretty deaf and useless on 6m, maybe I can get a better after-market tri-bander whip, but on 2m and 70cm it works reasonably well. I’ve heard APRS traffic over distances of 100km, and even been heard on APRS by a digipeater some 90km away.

Audio quality is good, both transmit and receive. Plug in a pair of stereo headphones, and the wideband FM receiver sounds excellent; in stereo to boot.

Probably my biggest nit, is you can’t simultaneously charge and externally power the set. To charge, you must either detach the battery and drop it into a separate charger cradle (an optional extra) or turn off the set.

### GPS Performance

When I purchased the VX-8DR, it was a real toss up between it and the VX-8GR. The reason I went the VX-8DR was because it had 6m, and Bluetooth. Having gotten the GPS, I’ve run into the problem a lot have reported; the GPS module is deaf as a post.

The VX8-GR doesn’t improve on this either. However, the good news, is that because my module is external, I can (1) mount it in a better spot, or (2) replace it with a better compatible module.  For VX8-GR owners, this is the end of the road, they can do nothing but moan to Yaesu.  I at least have options.

The module is mounted vertically inside the FGPS-2 casing. Usually with GPS modules such as these, they embed a small patch antenna, whose radiation pattern is perpendicular to the plane of the antenna surface. Being vertical, this means when you hold the radio vertically (as you normally would), GPS reception is poor because the radiation pattern is directly in front of the radio.

The radio seems to perform a lot better, if the radio is held with the screen facing upwards towards the sky. It’ll even work inside my house if I do this. It seems this is a screw-up on par with the iPhone 4.  Another alternative is to replace the module, the FGPS-2 apparently uses 9600 baud serial with NMEA format strings.  However, it seems the parser in the VX-8 is rather crude.  I have a module that does NMEA at 4800 baud, so I’ll either need to coax it up to 9600, or use a microcontroller to buffer and convert rates, and perhaps do some tweaking of the sentence format to make up for the VX-8’s shortcomings.

My hunch; if I make an alternative bracket to the CT-136 adaptor, I can nail this, and another problem, the inability to plug in the GPS and a headset. I have the CT-M11 cable, and thus I plan to make a bracket to connect the FGPS-2 to the end of this cable; allowing me to also plug in a wired headset.

### Bluetooth

I bought the Bluetooth as an insurance policy to give me another means of interfacing a headset. Then began the fun of getting it to work with my headsets. I have a couple; a Bullant earmuff-headset, a lightweight mono Digitech headset, and a “MyTalker” headset.

The first was one set I bought some years ago, back when the Bluez was far less stable than it is today, and also long before I was into Amateur Radio or possessed a Bluetooth-capable phone. I tried pairing using a USB Bluetooth dongle, but had little luck, so they got put on one side. Also despite advertising being able to stream music, it only supports HFP and HSP profiles, so you get to listen to your tunes in 8kHz 8-bit mono. They are sold at some hardware stores, such as Mitre 10 The Gap (where I bought my set).

The handheld did pair with this set, but I couldn’t get PTT to work, and the headset itself also had a few faults; namely it was always noisy, and the broadcast receiver stopped working, so I’ve taken them apart for now to see if I can fix these issues. I can key the radio up using the radio’s PTT, but then both internal and headset microphones go live.

The second set is sold by Jaycar, catalog number AA2080. This would be my preferred set to use with the radio as it can pair with two devices simultaneously. It supports the same profiles as the earmuffs, but it’s at least more lightweight.

The BU-1 takes one look at this set, and turns its nose up at it, with the VX-8 giving up and displaying “PAIRING ERROR”.

I also bought the MyTalker set from Jaycar, catalog number XC4894. This set is much like the earmuffs. It embeds its own microphone, but the unit itself provides a 3.5mm socket for you to plug in your own headphones, or use the supplied earphones (which are awful and uncomfortable, don’t use them). At the other end of the unit, is a lead terminated with a 3.5mm plug to plug into a music player. I’ve modded this set to be able to use an external microphone, switchable between a transceiver and the Bluetooth set, allowing a headset connected to a radio to also connect to a phone. I’m still working on this bit.

The VX-8 treats this set with much the same contempt as the mono headset before.

Today, I poppsed in and bought a more expensive set; this time I looked for A2DP functionality, Jaycar have one, catalog number AA2082. Like the AA2080 it can talk to two devices, unlike the AA2080 it supports AVRCP and A2DP. Also, not advertised, is it can function as an analogue headset; supplied in the box is a dual 3.5mm to mini-USB cable that can plug into the headset and allow you to use it with a non-Bluetooth capable device.

I plugged it into the bicycle’s battery to charge on the way home. When I got home, I read the instructions (which are in awful Chinglish). Basically, the English translation of the pairing instructions go like this:

1. Hold in the MFB button (the centre one on the right ear-cup) in for several seconds. You will hear the voice prompts “Hello”, followed by “Enter Pin Code 0000 on phone”.
2. When you hear the latter prompt, tell your device to start looking for the headset
3. When it finds a device called “AA2082”, select it, and enter 0000 as the pin code

So, the steps I followed:

1. Turn on the VX-8
2. Hold in the MENU key to bring up the Set menu, then select BLUETOOTH PCODE
3. Enter 0000 on the keypad.
4. Hold in the MFB button on the headset until you hear the “Enter Pin Code” prompt
5. Hit V/M on the VX-8
6. After a few brief moments, you should see “PAIRING COMPLETE”, press PTT to confirm.

Having got this working, I notice a few things:

• Stereo (A2DP) sounds a little weird, perfectly clear, but the compression is apparent. I’ll experiment with the laptop later to see if it’s the headset or the radio.
• Mono works well, pressing MFB toggles PTT on the VX-8. VOX doesn’t seem to work, but no great loss as I find VOX to be a disaster when outdoors.
• In mono mode, a buzzing is apparent on the received audio. This isn’t audible on transmitted audio, nor did I notice this on received audio when I tried using the headset with my mobile phone.
• Range seems to be quite restricted, possibly due to where the module is installed it doesn’t get the reception it perhaps needs. A2DP suffers more from this than HFP, with drop-outs being frequent. Again, I’ll need to do some experimentation with the laptop, and perhaps some experimentation with the radio without the battery installed to see if that helps performance.

I’m tossing up whether I get one of these motorcycle Bluetooth headsets.  I ride on the bicycle quite a lot, and at the moment I use headsets embedded in the helmet that are home-built from old computer headsets.  The longevity of the microphone seems to be the biggest problem  I also am on the look-out for an earmuff headset for things like the Imbill car rally, ideally one that can do A2DP.  The Bullant ones I know can’t do this.  I see some earmuffs in the \$400+ price bracket that offer Bluetooth, but no idea if that includes A2DP, and frankly, I shudder at that price.

The motorcycle ones are designed to fit a wide range of helmets, and they look as if they’ll fit a set of cheap regular earmuffs quite well.  They typically sell for about \$200, support A2DP, multiple devices, and intercom.  Add in \$30 for a set of earmuffs, and it makes this a much more attractive option.

More experimentation will be needed I think, but this is looking promising.  I’ll probably post up more details as I come across them.

It’d be nice if Yaesu had been a bit more up-front on what the BU-1 supports: the AA2080 supports both HFP and HSP, yet the BU-1 won’t touch it, the Bullant set supports the same profiles yet the BU-1 works fine with it.  The reasoning for this is not clear, but it does seem that it’ll reliably talk to A2DP capable headsets, so maybe that is a starting point for others.

Likewise with the CT-136, I’ll see if I can fabricate a bracket using the CT-M11 cable, and see where that gets me.