View Javadoc

1   /*
2    * Copyright (c) 2010 Ralph Jones
3    *
4    * Permission is hereby granted, free of charge, to any person obtaining a copy
5    * of this software and associated documentation files (the "Software"), to deal
6    * in the Software without restriction, including without limitation the rights
7    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8    * copies of the Software, and to permit persons to whom the Software is
9    * furnished to do so, subject to the following conditions:
10   *
11   * The above copyright notice and this permission notice shall be included in
12   * all copies or substantial portions of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
20   * THE SOFTWARE.
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          // Insert the special "Unitary" county
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          // Read in the sort of CSV file
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                      // County Name
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                     // District Name
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                     // Ward Name
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                     // NHS Region Name
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                     // NHS Area Name
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         // Read in the CSV data
194         CSVReader reader = new CSVReader(new FileReader(csvFile));
195         try {
196             // Copy across
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                     // Convert the easting/northing to lat/long
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                     // The NHS and ward codes
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         // Drop and re-create the table
282         Statement st = conn.createStatement();
283         try {
284             st.execute("DROP TABLE CodePoint");
285         } catch (SQLException sqlEx) {
286             // Don't care - might be didn't exist to begin with
287         } finally {
288             st.close();
289         }
290 
291         st = conn.createStatement();
292         try {
293             st.execute("DROP TABLE Ward");
294         } catch (SQLException sqlEx) {
295             // Don't care - might be didn't exist to begin with
296         } finally {
297             st.close();
298         }
299 
300         st = conn.createStatement();
301         try {
302             st.execute("DROP TABLE District");
303         } catch (SQLException sqlEx) {
304             // Don't care - might be didn't exist to begin with
305         } finally {
306             st.close();
307         }
308 
309         st = conn.createStatement();
310         try {
311             st.execute("DROP TABLE County");
312         } catch (SQLException sqlEx) {
313             // Don't care - might be didn't exist to begin with
314         } finally {
315             st.close();
316         }
317 
318         st = conn.createStatement();
319         try {
320             st.execute("DROP TABLE NhsArea");
321         } catch (SQLException sqlEx) {
322             // Don't care - might be didn't exist to begin with
323         } finally {
324             st.close();
325         }
326 
327         st = conn.createStatement();
328         try {
329             st.execute("DROP TABLE NhsRegion");
330         } catch (SQLException sqlEx) {
331             // Don't care - might be didn't exist to begin with
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         // Populate the lookups
415         populateCodeList(conn, codeListFile);
416 
417         // Get all CSV files in the directory
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         // Create indexes for the tables
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             // Pass all files through
431             populateCsvFile(conn, files[num]);
432         }
433     }
434 }