This will allow you to select and plot each feature and see if your numbers are what are expected

The problem was not with unpacking the data - it was with my understanding!!!

I had erroneously thought that the envelope would be the bounds of the UK. Whereas, it is only the bounds for a single route! The decimal places in the coordinates still confuse me but rounding them gives sufficiently high accuracy.

My test code looks like this:

use DBD::SQLite;
use Data::Dumper;

use strict;
use warnings;

my $dbh = DBI->connect("dbi:SQLite:uri=file:osopenusrn_202203.gpkg?mod
+e=rwc");

my $tab = $dbh->prepare("SELECT * FROM openUSRN LIMIT 1");
$tab->execute;
my $data = $tab->fetchrow_hashref;

my $geo = $data->{'geometry'};

my @test = unpack "(H2)2 CCVd6CVVCVV(d<)6CVV(d<)9", $data->{'geometry'
+};
 
print "\n\nUSRN\t\t" . $data->{'usrn'} . "\n";
 
print "\nMagic bytes\t$test[0] $test[1]\n";
print "version\t\t$test[2]\n";
print "flags\t\t";
printf("%08b\n", $test[3]);
print "SRS ID\t\t$test[4]\n\n";
print "minx\t\t$test[5]\n";
print "maxx\t\t$test[6]\n";
print "miny\t\t$test[7]\n";
print "maxy\t\t$test[8]\n";
print "minz\t\t$test[9]\n";
print "maxz\t\t$test[10]\n";
print "-----\n";

print "NDR\t\t$test[11]\n";
print "type\t\t$test[12]\n";
print "number\t\t$test[13]\n\n";

print "byteOrder\t$test[14]\n";
print "type\t\t$test[15]\n";
print "number\t\t$test[16]\n\n";

#print "byteOrder\t$test[17]\n";
#print "type\t\t$test[18]\n";

print "Point1 x\t$test[17]\n";
print "Point1 y\t$test[18]\n";
print "Point1 z\t$test[19]\n";
print "Point2 x\t$test[20]\n";
print "Point2 y\t$test[21]\n";
print "Point2 z\t$test[22]\n\n";

print "byteOrder\t$test[23]\n";
print "type\t\t$test[24]\n";
print "number\t\t$test[25]\n\n";

print "Point1 x\t$test[26]\n";
print "Point1 y\t$test[27]\n";
print "Point1 z\t$test[28]\n";
print "Point2 x\t$test[29]\n";
print "Point2 y\t$test[30]\n";
print "Point2 z\t$test[31]\n";
print "Point3 x\t$test[32]\n";
print "Point3 y\t$test[33]\n";
print "Point3 z\t$test[34]\n";
[download]

USRN            4601764

Magic bytes     47 50
version         0
flags           00000101
SRS ID          27700

minx            637590.385
maxx            642426.601
miny            309577.58
maxy            310361.391000001
minz            0
maxz            0
-----
NDR             1
type            1005
number          34

byteOrder       1
type            1002
number          2

Point1 x        640921.438
Point1 y        310210.692
Point1 z        0
Point2 x        641634.2632
Point2 y        309910.6438
Point2 z        0

byteOrder       1
type            1002
number          3

Point1 x        637969.3747
Point1 y        309910.6438
Point1 z        0
Point2 x        638226.29
Point2 y        309978.609999999
Point2 z        0
Point3 x        638404.534
Point3 y        310054.294
Point3 z        0
[download]

I've sanity checked the data against online tools. First I looked up the USRN of the first entry in the database. I then plotted the 5 points I got from the first 2 of the 34 LineStrings. All 5 points are on the route shown by the USRN so I take that a success :)

Of course, I still need to generalise the reading of the different geometry data types but at least their structure makes some sense.

The first step is to get the midpoint of each USRN. Just taking the centre of the envelope should provide this and won't require too much delving into the geometries - although that will have to follow in time.

Lots of thanks for your excellent pointers.