[HE] import HE specific road data for road lookup

As well as adding junction data this adds data to enable lookup by
crossing roads and placenames, also service stations.

1 files changed, 90 insertions, 0 deletions

diff --git a/bin/highwaysengland/augment-junctions-database b/bin/highwaysengland/augment-junctions-database
new file mode 100755
index 000000000..537556d6a
--- /dev/null
+++ b/bin/highwaysengland/augment-junctions-database
@@ -0,0 +1,90 @@
+#!/usr/bin/env perl
+
+=head1 NAME
+
+make-junctions-database
+
+=head1 USAGE
+
+augment-junctions-database path/to/data.csv
+
+=head1 DESCRIPTION
+
+Adds to the SQLite file created by `make-junctions-database`. This requires that
+script to have been run and the initial database created.
+
+It expects a CSV with a header row: road,junction,point_x,point_y
+and the data in those columns to be:
+
+* road - name of road, eg A1
+* junction - description of the point
+* point_x - easting
+* point_y - northing
+
+The junction field can be either
+
+* a junction number, e.g J12
+* a junction number and a description, e.g J12 Town Name
+* a junction/point descrption, e.g Town Name
+
+Where the function field is a junction number and a description two rows will be created,
+one for the junction number and one for the description.
+
+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);
+
+my $q_junction_chk = $db->prepare('SELECT road, name FROM junction WHERE road = ? AND name = ?');
+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 $junction = uc $row->{junction};
+    my $alt_name;
+    if ($junction =~ /^(J\d+)\s*(.*)$/) {
+        ($junction, $alt_name) = ($1, $2);
+    }
+
+    my $road = uc $row->{road};
+    next unless $road =~ /^[AM]/;
+
+    # strip extra precision
+    (my $e = $row->{point_x}) =~ s/\.\d+$//;
+    (my $n = $row->{point_y}) =~ s/\.\d+$//;
+
+    if ($alt_name) {
+        $q_junction_chk->execute($road, $alt_name) or warn $db->errstr . " $road $alt_name";
+        unless ($q_junction_chk->fetchrow_hashref) {
+            $q_junction->execute($road, $alt_name, $e, $n) or warn $db->errstr . " $road $alt_name";
+        }
+    }
+
+    $q_junction_chk->execute($road, $junction) or warn $db->errstr . " $road $junction";
+    next if $q_junction_chk->fetchrow_hashref;
+    $q_junction->execute($road, $junction, $e, $n) or warn $db->errstr . " $road $junction";
+}
+
+close $fh;