Add Highways England junction lookup

This is taken from the sort-my-sign repo. It allows the user to search
for motorways names and junction numbers, e.g. "M60, Junction 2", and
then geocodes that using a SQLite database created from Highways England
markerpost locations.

1 files changed, 116 insertions, 0 deletions

diff --git a/bin/make-junctions-database b/bin/make-junctions-database
new file mode 100755
index 000000000..f7a12c586
--- /dev/null
+++ b/bin/make-junctions-database
@@ -0,0 +1,116 @@
+#!/usr/bin/env perl
+
+=head1 NAME
+
+make-junctions-database
+
+=head1 USAGE
+
+make-junctions-database path/to/markerposts.csv
+
+=head1 DESCRIPTION
+
+Creates a SQLite database of Highways England junctions to facilitate looking
+up locations by junction name, e.g. "M60, Junction 2" or "M6 323.5".
+
+
+1. Download the database of all marker posts from
+https://www.whatdotheyknow.com/request/positions_of_driver_location_sig
+The filename is "Gazetteer All Mposts only.zip".
+
+2. Unzip and export the XLSX file as a CSV (using in2csv from csvkit, for example).
+
+3. Run this script to build the database.
+
+The resulting database is used by the L<HighwaysEngland> package to lookup junctions.
+
+=cut
+
+use strict;
+use warnings;
+
+BEGIN {
+    use File::Basename qw(dirname);
+    use File::Spec;
+    my $d = dirname(File::Spec->rel2abs($0));
+    require "$d/../setenv.pl";
+}
+
+use DBI;
+use Text::CSV;
+use FixMyStreet;
+use HighwaysEngland;
+
+my $db = DBI->connect('dbi:SQLite:dbname='. HighwaysEngland::database_file);
+
+$db->do(<<EOF) or die $db->errstr;
+CREATE TABLE IF NOT EXISTS junction (
+    name TEXT NOT NULL,
+    road TEXT NOT NULL,
+    easting INTEGER NOT NULL,
+    northing INTEGER NOT NULL,
+    PRIMARY KEY (name, road)
+);
+EOF
+
+$db->do(<<EOF) or die $db->errstr;
+CREATE TABLE IF NOT EXISTS sign (
+    road TEXT NOT NULL,
+    distance TEXT NOT NULL,
+    side TEXT NOT NULL,
+    easting INTEGER NOT NULL,
+    northing INTEGER NOT NULL
+);
+EOF
+
+$db->do(<<EOF) or die $db->errstr;
+CREATE INDEX IF NOT EXISTS sign_idx ON sign (road, distance);
+EOF
+
+my $q_sign = $db->prepare('INSERT INTO sign (road, distance, side, easting, northing) VALUES (?, ?, ?, ?, ?)');
+my $q_junction = $db->prepare('INSERT INTO junction (road, name, easting, northing) VALUES (?, ?, ?, ?)');
+
+my $csv = Text::CSV->new ({ binary => 1, auto_diag => 1 });
+die "Usage: $0 <csv_file>\n" unless @ARGV;
+open my $fh, "<:encoding(utf8)", $ARGV[0] or die "$ARGV[0]: $!";
+$csv->header($fh);
+
+my %all;
+my $c = 0;
+while (my $row = $csv->getline_hr($fh)) {
+    my $marker = $row->{bd};
+    $marker =~ /P(\d+)\/(\d+)([ABJMKL])/ or next;
+
+    my $kms = "$1.$2";
+    my $letter = $3;
+
+    my $road = $row->{dd};
+    next unless $road =~ /^[AM]/;
+
+    my $e = $row->{easting};
+    my $n = $row->{northing};
+    my $name = $row->{tn};
+
+    $q_sign->execute($road, $kms, $letter, $e, $n) or warn $db->errstr . " $road $kms $letter";
+    print '.' unless $c++ % 1000;
+
+    next unless $name =~ /ASIDE|BSIDE/;
+    $name =~ s/ ASIDE| BSIDE//;
+    push @{$all{$road}{$name}}, $row;
+}
+
+close $fh;
+
+for my $road (sort keys %all) {
+    foreach my $junction (sort keys %{$all{$road}}) {
+        my $tot_e = 0; my $tot_n = 0; my $n = 0;
+        foreach (@{$all{$road}{$junction}}) {
+            $tot_e += $_->{easting};
+            $tot_n += $_->{northing};
+            $n++;
+        }
+        my $avg_e = int($tot_e/$n+0.5);
+        my $avg_n = int($tot_n/$n+0.5);
+        $q_junction->execute($road, $junction, $avg_e, $avg_n);
+    }
+}