-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathcreatedb.php
150 lines (120 loc) · 6.17 KB
/
createdb.php
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
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
<?php
// Enter database connection details here!
$pdo = new PDO('mysql:host=HOSTNAME;port=3306;dbname=DBNAME;charset=utf8', 'USERNAME', 'PASSWORD', array(PDO::ATTR_PERSISTENT => true));
// Enter list of countries to be imported. For every country there must be a directory with that name containing the TMC location code tables in TISA format.
$countries = array('de'=>"Germany", 'it'=>"Italy", 'se'=>"Sweden", 'no'=>"Norway", 'fi'=>"Finland", 'fr'=>"France", 'be'=>"Belgium", 'es'=>"Spain", 'sk'=>"Slovakia");
$pdo->exec("CREATE TABLE IF NOT EXISTS countries (cid INTEGER, tabcd INTEGER, name VARCHAR(100), dcomment VARCHAR(100), version VARCHAR(8), versiondescription VARCHAR(100), PRIMARY KEY (cid, tabcd)) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;");
foreach($countries as $country)
{
$charset = get_charset($country);
$csv = fopen("$country/LOCATIONDATASETS.DAT", "r");
$header = explode(";", utf8_encode(trim(remove_utf8_bom(fgets($csv)))));
$cols = strtolower("(" . implode(", ", array_merge($header, array('NAME'))) . ")");
for(;;)
{
$text = fgets($csv);
if(!$text)
break;
$data = array_combine($header, explode(";", iconv($charset, 'UTF-8', trim($text)), count($header)));
$data['NAME'] = $country;
$vals = "('" . implode("', '", $data) . "')";
$query = "INSERT INTO countries $cols VALUES $vals;";
echo $pdo->exec($query) . ": $query\n";
}
fclose($csv);
}
function create_table($file, $layout)
{
global $pdo;
global $countries;
$table = strtolower($file);
$pdo->exec("CREATE TABLE IF NOT EXISTS $table ($layout) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;");
foreach($countries as $country)
{
$charset = get_charset($country);
$csv = fopen("$country/$file.DAT", "r");
$header = explode(";", utf8_encode(trim(remove_utf8_bom(fgets($csv)))));
$cols = strtolower("(" . implode(", ", $header) . ")");
for(;;)
{
$text = fgets($csv);
if(!$text)
break;
$data = array_combine($header, explode(";", iconv($charset, 'UTF-8', trim($text)), count($header)));
if(strpos($cols, 'xcoord') !== false)
{
$data['XCOORD'] = ((float)$data['XCOORD'])/1e5;
$data['YCOORD'] = ((float)$data['YCOORD'])/1e5;
}
$vals = "('" . implode("', '", $data) . "')";
$query = "INSERT INTO $table $cols VALUES $vals;";
echo $pdo->exec($query) . ": $query\n";
}
fclose($csv);
}
}
function create_types($table, $file, $layout)
{
global $pdo;
global $countries;
$table = strtolower($table);
$pdo->exec("CREATE TABLE IF NOT EXISTS $table ($layout) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;");
foreach($countries as $cid=>$country)
{
$charset = get_charset($country);
$csv = fopen("$country/$file.DAT", "r");
$header = explode(";", utf8_encode(trim(remove_utf8_bom(fgets($csv)))));
$cols = strtolower("(" . implode(", ", $header) . ")");
// Add National COL
$pdo->exec("ALTER TABLE $table ADD desc_$cid VARCHAR(255);");
// International Cols
$headerInt = array('CLASS', 'TCD', 'STCD', '`DESC`');
$colsInt = strtolower("(" . implode(", ", $headerInt) . ")");
for(;;)
{
$text = fgets($csv);
if(!$text)
break;
$data = array_combine($header, explode(";", iconv($charset, 'UTF-8', trim($text)), count($header)));
// Only Cols: CLASS;TCD;STCD;SDESC
$desc_lang = $data['SNATDESC'];
unset($data['SNATDESC']);
unset($data['SNATCODE']);
$vals = "('" . implode("', '", $data) . "')";
$query = "INSERT INTO $table $colsInt VALUES $vals;";
echo $pdo->exec($query) . ": $query\n";
// SNATDESC
if($desc_lang){
$query = "UPDATE $table SET desc_$cid='$desc_lang' WHERE class='".$data['CLASS']."' AND tcd='".$data['TCD']."' AND stcd='".$data['STCD']."'";
echo $pdo->exec($query) . ": $query\n";
}
}
}
}
function get_charset($country)
{
$readme = explode(";", trim(remove_utf8_bom(file_get_contents("$country/README.DAT"))));
$arr = explode(' ', $readme[4]);
if($arr[0] == 'ISO')
// Variant for German ISO 8859 without minus
$arr = explode(' (', $readme[4]);
return $arr[0];
}
function remove_utf8_bom($text)
{
$bom = pack('H*','EFBBBF');
$text = preg_replace("/^$bom/", '', $text);
return $text;
}
create_types('TYPES', 'SUBTYPES', 'class CHAR(1), tcd TINYINT(4), stcd TINYINT(4), `desc` VARCHAR(255), PRIMARY KEY (class, tcd, stcd)');
create_table('LOCATIONCODES', 'cid INTEGER, tabcd INTEGER, lcd INTEGER, allocated BOOLEAN, PRIMARY KEY (cid, tabcd, lcd)');
create_table('NAMES', 'cid INTEGER, lid INTEGER, nid INTEGER, name VARCHAR(255), ncomment TEXT, KEY cid (cid, nid)');
create_table('ADMINISTRATIVEAREA', 'cid INTEGER, tabcd INTEGER, lcd INTEGER, class CHAR, tcd TINYINT, stcd TINYINT, nid INTEGER, pol_lcd INTEGER, PRIMARY KEY (cid, tabcd, lcd)');
create_table('OTHERAREAS', 'cid INTEGER, tabcd INTEGER, lcd INTEGER, class CHAR, tcd TINYINT, stcd TINYINT, nid INTEGER, pol_lcd INTEGER, PRIMARY KEY (cid, tabcd, lcd)');
create_table('ROADS', 'cid INTEGER, tabcd INTEGER, lcd INTEGER, class CHAR, tcd TINYINT, stcd TINYINT, roadnumber VARCHAR(16), rnid INTEGER, n1id INTEGER, n2id INTEGER, pol_lcd INTEGER, pes_lev INTEGER, PRIMARY KEY (cid, tabcd, lcd)');
create_table('SEGMENTS', 'cid INTEGER, tabcd INTEGER, lcd INTEGER, class CHAR, tcd TINYINT, stcd TINYINT, roadnumber VARCHAR(16), rnid INTEGER, n1id INTEGER, n2id INTEGER, roa_lcd INTEGER, seg_lcd INTEGER, pol_lcd INTEGER, PRIMARY KEY (cid, tabcd, lcd)');
create_table('SOFFSETS', 'cid INTEGER, tabcd INTEGER, lcd INTEGER, neg_off_lcd INTEGER, pos_off_lcd INTEGER, PRIMARY KEY (cid, tabcd, lcd)');
create_table('POINTS', 'cid INTEGER, tabcd INTEGER, lcd INTEGER, class CHAR, tcd TINYINT, stcd TINYINT, junctionnumber VARCHAR(16), rnid INTEGER, n1id INTEGER, n2id INTEGER, pol_lcd INTEGER, oth_lcd INTEGER, seg_lcd INTEGER, roa_lcd INTEGER, inpos BOOLEAN, inneg BOOLEAN, outpos BOOLEAN, outneg BOOLEAN, presentpos BOOLEAN, presentneg BOOLEAN, diversionpos BOOLEAN, diversionneg BOOLEAN, xcoord REAL, ycoord REAL, interruptsroad INTEGER, urban BOOLEAN, PRIMARY KEY (cid, tabcd, lcd)');
create_table('POFFSETS', 'cid INTEGER, tabcd INTEGER, lcd INTEGER, neg_off_lcd INTEGER, pos_off_lcd INTEGER, PRIMARY KEY (cid, tabcd, lcd)');
create_table('INTERSECTIONS', 'cid INTEGER, tabcd INTEGER, lcd INTEGER, int_cid INTEGER, int_tabcd INTEGER, int_lcd INTEGER, PRIMARY KEY (cid, tabcd, lcd)');
?>