1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 package com.totalchange.osopendata.generator;
23
24 import java.io.File;
25 import java.io.FileFilter;
26 import java.io.FileReader;
27 import java.io.IOException;
28 import java.sql.Connection;
29 import java.sql.PreparedStatement;
30 import java.sql.SQLException;
31 import java.sql.Statement;
32 import java.sql.Types;
33 import java.util.logging.Logger;
34
35 import uk.me.jstott.jcoord.LatLng;
36 import uk.me.jstott.jcoord.OSRef;
37
38 import au.com.bytecode.opencsv.CSVReader;
39
40 final class PopulateCodepoint {
41 private static final Logger logger = Logger
42 .getLogger(PopulateCodepoint.class.getName());
43
44 private static String lineToString(String[] line) {
45 StringBuffer str = new StringBuffer();
46
47 str.append('[');
48 for (int num = 0; num < line.length; num++) {
49 if (num > 0) {
50 str.append(',');
51 }
52 str.append(line[num]);
53 }
54 str.append(']');
55
56 return str.toString();
57 }
58
59 private static String concatenateEndOfLine(String[] line, int start) {
60 StringBuffer str = new StringBuffer();
61
62 for (int num = start; num < line.length; num++) {
63 if (num > 0) {
64 str.append(',');
65 }
66 str.append(line[num]);
67 }
68
69 return str.toString();
70 }
71
72 private static void populateCodeList(Connection conn, File codeListFile)
73 throws SQLException, IOException {
74 logger.info("Processing code list file "
75 + codeListFile.getAbsolutePath());
76
77
78 PreparedStatement st = conn.prepareStatement("INSERT INTO "
79 + "County(countyCode, county) " + "VALUES(?, ?)");
80 try {
81 st.setString(1, "00");
82 st.setString(2, "Unitary Authority");
83
84 st.executeUpdate();
85 } finally {
86 st.close();
87 }
88
89
90 CSVReader reader = new CSVReader(new FileReader(codeListFile));
91 try {
92 String[] line;
93 while ((line = reader.readNext()) != null) {
94 if ((line.length >= 4) && (line[0].length() == 2)
95 && (line[1].length() == 0) && (line[2].length() == 0)) {
96
97 st = conn.prepareStatement("INSERT INTO "
98 + "County(countyCode, county) " + "VALUES(?, ?)");
99 try {
100 st.setString(1, line[0]);
101 st.setString(2, concatenateEndOfLine(line, 3));
102
103 st.executeUpdate();
104 } catch (SQLException sqlEx) {
105 throw new SQLException("Inserting county "
106 + lineToString(line) + ": " + sqlEx, sqlEx);
107 } finally {
108 st.close();
109 }
110 } else if ((line.length >= 4) && (line[0].length() == 2)
111 && (line[1].length() == 2) && (line[2].length() == 0)) {
112
113 st = conn.prepareStatement("INSERT INTO "
114 + "District(countyCode, districtCode, "
115 + "district) " + "VALUES(?, ?, ?)");
116 try {
117 st.setString(1, line[0]);
118 st.setString(2, line[1]);
119 st.setString(3, concatenateEndOfLine(line, 3));
120
121 st.executeUpdate();
122 } catch (SQLException sqlEx) {
123 throw new SQLException("Inserting district "
124 + lineToString(line) + ": " + sqlEx, sqlEx);
125 } finally {
126 st.close();
127 }
128 } else if ((line.length >= 4) && (line[0].length() == 2)
129 && (line[1].length() == 2) && (line[2].length() == 2)) {
130
131 st = conn.prepareStatement("INSERT INTO "
132 + "Ward(countyCode, districtCode, "
133 + "wardCode, ward) " + "VALUES(?, ?, ?, ?)");
134 try {
135 st.setString(1, line[0]);
136 st.setString(2, line[1]);
137 st.setString(3, line[2]);
138 st.setString(4, concatenateEndOfLine(line, 3));
139
140 st.executeUpdate();
141 } catch (SQLException sqlEx) {
142 throw new SQLException("Inserting ward "
143 + lineToString(line) + ": " + sqlEx, sqlEx);
144 } finally {
145 st.close();
146 }
147 } else if ((line.length >= 3) && (line[0].length() == 3)
148 && (line[1].length() == 0)) {
149
150 st = conn.prepareStatement("INSERT INTO "
151 + "NhsRegion(nhsRegionCode, nhsRegion) "
152 + "VALUES(?, ?)");
153 try {
154 st.setString(1, line[0]);
155 st.setString(2, concatenateEndOfLine(line, 2));
156
157 st.executeUpdate();
158 } catch (SQLException sqlEx) {
159 throw new SQLException("Inserting nhs region "
160 + lineToString(line) + ": " + sqlEx, sqlEx);
161 } finally {
162 st.close();
163 }
164 } else if ((line.length >= 3) && (line[0].length() == 3)
165 && (line[1].length() == 3)) {
166
167 st = conn.prepareStatement("INSERT INTO "
168 + "NhsArea(nhsRegionCode, nhsAreaCode, "
169 + "nhsArea) " + "VALUES(?, ?, ?)");
170 try {
171 st.setString(1, line[0]);
172 st.setString(2, line[1]);
173 st.setString(3, concatenateEndOfLine(line, 2));
174
175 st.executeUpdate();
176 } catch (SQLException sqlEx) {
177 throw new SQLException("Inserting nhs area "
178 + lineToString(line) + ": " + sqlEx, sqlEx);
179 } finally {
180 st.close();
181 }
182 }
183 }
184 } finally {
185 reader.close();
186 }
187 }
188
189 private static void populateCsvFile(Connection conn, File csvFile)
190 throws SQLException, IOException {
191 logger.info("Processing CodePoint file " + csvFile.getAbsolutePath());
192
193
194 CSVReader reader = new CSVReader(new FileReader(csvFile));
195 try {
196
197 String[] line;
198 while ((line = reader.readNext()) != null) {
199 PreparedStatement st = conn.prepareStatement("INSERT INTO "
200 + "CodePoint(postcodeUnit, easting, northing, "
201 + "latitude, longitude, nhsRegionCode, nhsAreaCode,"
202 + "countyCode, districtCode, wardCode) "
203 + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
204 try {
205 st.setString(1, line[0]);
206
207 int easting = Integer.parseInt(line[10]);
208 int northing = Integer.parseInt(line[11]);
209
210 st.setInt(2, easting);
211 st.setInt(3, northing);
212
213
214 OSRef osRef = new OSRef(easting, northing);
215 LatLng latLng = osRef.toLatLng();
216 latLng.toWGS84();
217
218 st.setDouble(4, latLng.getLat());
219 st.setDouble(5, latLng.getLng());
220
221
222 if (line[13].length() > 0) {
223 st.setString(6, line[13]);
224 } else {
225 st.setNull(6, Types.CHAR);
226 }
227
228 if (line[14].length() > 0) {
229 st.setString(7, line[14]);
230 } else {
231 st.setNull(7, Types.CHAR);
232 }
233
234 if (line[15].length() > 0) {
235 st.setString(8, line[15]);
236 } else {
237 st.setNull(8, Types.CHAR);
238 }
239
240 if (line[16].length() > 0) {
241 st.setString(9, line[16]);
242 } else {
243 st.setNull(9, Types.CHAR);
244 }
245
246 if (line[17].length() > 0) {
247 st.setString(10, line[17]);
248 } else {
249 st.setNull(10, Types.CHAR);
250 }
251
252 try {
253 st.executeUpdate();
254 } catch (SQLException sqlEx) {
255 throw new SQLException("Inserting code point "
256 + lineToString(line) + ": " + sqlEx, sqlEx);
257 }
258 } finally {
259 st.close();
260 }
261 }
262 } finally {
263 reader.close();
264 }
265 }
266
267 private static void createIndexes(Connection conn) throws SQLException {
268 Statement st = conn.createStatement();
269 try {
270 st.execute("CREATE INDEX CodePoint_postcodeUnit "
271 + "ON CodePoint(postcodeUnit)");
272 } finally {
273 st.close();
274 }
275 }
276
277 static void populate(Connection conn, File csvRoot, File codeListFile)
278 throws SQLException, IOException {
279 logger.info("Dropping tables");
280
281
282 Statement st = conn.createStatement();
283 try {
284 st.execute("DROP TABLE CodePoint");
285 } catch (SQLException sqlEx) {
286
287 } finally {
288 st.close();
289 }
290
291 st = conn.createStatement();
292 try {
293 st.execute("DROP TABLE Ward");
294 } catch (SQLException sqlEx) {
295
296 } finally {
297 st.close();
298 }
299
300 st = conn.createStatement();
301 try {
302 st.execute("DROP TABLE District");
303 } catch (SQLException sqlEx) {
304
305 } finally {
306 st.close();
307 }
308
309 st = conn.createStatement();
310 try {
311 st.execute("DROP TABLE County");
312 } catch (SQLException sqlEx) {
313
314 } finally {
315 st.close();
316 }
317
318 st = conn.createStatement();
319 try {
320 st.execute("DROP TABLE NhsArea");
321 } catch (SQLException sqlEx) {
322
323 } finally {
324 st.close();
325 }
326
327 st = conn.createStatement();
328 try {
329 st.execute("DROP TABLE NhsRegion");
330 } catch (SQLException sqlEx) {
331
332 } finally {
333 st.close();
334 }
335
336 st = conn.createStatement();
337 try {
338 logger.info("Creating tables");
339 st.execute("CREATE TABLE NhsRegion("
340 + "nhsRegionCode CHAR(2) NOT NULL, "
341 + "nhsRegion VARCHAR(255) NOT NULL, "
342
343 + "PRIMARY KEY(nhsRegionCode));");
344
345 st.execute("CREATE TABLE NhsArea("
346 + "nhsRegionCode CHAR(2) NOT NULL, "
347 + "nhsAreaCode CHAR(2) NOT NULL, "
348 + "nhsArea VARCHAR(255) NOT NULL, "
349
350 + "PRIMARY KEY(nhsRegionCode, nhsAreaCode),"
351
352 + "FOREIGN KEY(nhsRegionCode) "
353 + "REFERENCES NhsRegion(nhsRegionCode));");
354
355 st.execute("CREATE TABLE County(" + "countyCode CHAR(2) NOT NULL, "
356 + "county VARCHAR(255) NOT NULL, "
357
358 + "PRIMARY KEY(countyCode));");
359
360 st.execute("CREATE TABLE District("
361 + "countyCode CHAR(2) NOT NULL, "
362 + "districtCode CHAR(2) NOT NULL, "
363 + "district VARCHAR(255) NOT NULL, "
364
365 + "PRIMARY KEY(countyCode, districtCode), "
366
367 + "FOREIGN KEY(countyCode) "
368 + "REFERENCES County(countyCode));");
369
370 st.execute("CREATE TABLE Ward(" + "countyCode CHAR(2) NOT NULL, "
371 + "districtCode CHAR(2) NOT NULL, "
372 + "wardCode CHAR(2) NOT NULL, "
373 + "ward VARCHAR(255) NOT NULL, "
374
375 + "PRIMARY KEY(countyCode, districtCode, wardCode), "
376
377 + "FOREIGN KEY(countyCode) "
378 + "REFERENCES County(countyCode), "
379
380 + "FOREIGN KEY(countyCode, districtCode) "
381 + "REFERENCES District(countyCode, districtCode));");
382
383 st.execute("CREATE TABLE CodePoint("
384 + "postcodeUnit CHAR(7) NOT NULL, "
385 + "easting INTEGER NOT NULL, "
386 + "northing INTEGER NOT NULL, "
387 + "latitude DOUBLE NOT NULL, "
388 + "longitude DOUBLE NOT NULL, "
389 + "nhsRegionCode CHAR(3) NULL, "
390 + "nhsAreaCode CHAR(3) NULL, "
391 + "countyCode CHAR(2) NULL, "
392 + "districtCode CHAR(2) NULL, " + "wardCode CHAR(2) NULL, "
393
394 + "PRIMARY KEY(postcodeUnit), "
395
396 + "FOREIGN KEY(nhsRegionCode) "
397 + "REFERENCES NhsRegion(nhsRegionCode), "
398
399 + "FOREIGN KEY(nhsRegionCode, nhsAreaCode) "
400 + "REFERENCES NhsArea(nhsRegionCode, nhsAreaCode), "
401
402 + "FOREIGN KEY(countyCode) "
403 + "REFERENCES County(countyCode), "
404
405 + "FOREIGN KEY(countyCode, districtCode) "
406 + "REFERENCES District(countyCode, districtCode), "
407
408 + "FOREIGN KEY(countyCode, districtCode, wardCode) "
409 + "REFERENCES Ward(countyCode, districtCode, wardCode));");
410 } finally {
411 st.close();
412 }
413
414
415 populateCodeList(conn, codeListFile);
416
417
418 File[] files = csvRoot.listFiles(new FileFilter() {
419 public boolean accept(File pathname) {
420 return pathname.isFile() && pathname.getName().endsWith(".csv");
421 }
422 });
423
424
425 createIndexes(conn);
426
427 logger.info("Found " + files.length + " code point files in directory "
428 + csvRoot.getAbsolutePath());
429 for (int num = 0; num < files.length; num++) {
430
431 populateCsvFile(conn, files[num]);
432 }
433 }
434 }