1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
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);
}
}
|
