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.FileReader;
26  import java.io.IOException;
27  import java.sql.Connection;
28  import java.sql.PreparedStatement;
29  import java.sql.SQLException;
30  import java.sql.Statement;
31  import java.util.logging.Logger;
32  
33  import uk.me.jstott.jcoord.LatLng;
34  import uk.me.jstott.jcoord.OSRef;
35  
36  import au.com.bytecode.opencsv.CSVReader;
37  
38  final class PopulateLocator {
39      private static final Logger logger = Logger.getLogger(PopulateLocator.class
40              .getName());
41  
42      private static String lineToString(String[] line) {
43          StringBuffer str = new StringBuffer();
44  
45          str.append('[');
46          for (int num = 0; num < line.length; num++) {
47              if (num > 0) {
48                  str.append(':');
49              }
50              str.append(line[num]);
51          }
52          str.append(']');
53  
54          return str.toString();
55      }
56  
57      private static void populateLocator(Connection conn, File dataFile)
58              throws SQLException, IOException {
59          logger.info("Processing Locator file " + dataFile.getAbsolutePath());
60  
61          // Read in the CSV data
62          CSVReader reader = new CSVReader(new FileReader(dataFile), ':');
63          try {
64              // Copy across
65              String[] line;
66              while ((line = reader.readNext()) != null) {
67                  PreparedStatement st = conn.prepareStatement("INSERT INTO "
68                          + "Locator(name, classification, centreEasting, "
69                          + "centreNorthing, centreLatitude, centreLongitude, "
70                          + "minimumNorthing, maximumNorthing, minimumEasting, "
71                          + "maximumEasting, minimumLatitude, maximumLatitude, "
72                          + "minimumLongitude, maximumLongitude, settlement, "
73                          + "locality, county, localAuthority, tile10k, "
74                          + "tile50k, source) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, "
75                          + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
76  
77                  if (line[0].length() > 0) {
78                      st.setString(1, line[0]);
79                  }
80  
81                  if (line[1].length() > 0) {
82                      st.setString(2, line[1]);
83                  }
84  
85                  int easting = Integer.parseInt(line[2]);
86                  int northing = Integer.parseInt(line[3]);
87  
88                  st.setInt(3, easting);
89                  st.setInt(4, northing);
90  
91                  // Convert the easting/northing to lat/long
92                  OSRef osRef = new OSRef(easting, northing);
93                  LatLng latLng = osRef.toLatLng();
94                  latLng.toWGS84();
95  
96                  st.setDouble(5, latLng.getLat());
97                  st.setDouble(6, latLng.getLng());
98  
99                  easting = Integer.parseInt(line[4]);
100                 northing = Integer.parseInt(line[5]);
101 
102                 st.setInt(7, easting);
103                 st.setInt(8, northing);
104 
105                 // Convert the easting/northing to lat/long
106                 osRef = new OSRef(easting, northing);
107                 latLng = osRef.toLatLng();
108                 latLng.toWGS84();
109 
110                 st.setDouble(11, latLng.getLat());
111                 st.setDouble(12, latLng.getLng());
112 
113                 easting = Integer.parseInt(line[6]);
114                 northing = Integer.parseInt(line[7]);
115 
116                 st.setInt(9, easting);
117                 st.setInt(10, northing);
118 
119                 // Convert the easting/northing to lat/long
120                 osRef = new OSRef(easting, northing);
121                 latLng = osRef.toLatLng();
122                 latLng.toWGS84();
123 
124                 st.setDouble(13, latLng.getLat());
125                 st.setDouble(14, latLng.getLng());
126 
127                 if (line[9].length() > 0) {
128                     st.setString(15, line[9]);
129                 }
130 
131                 if (line[10].length() > 0) {
132                     st.setString(16, line[10]);
133                 }
134 
135                 if (line[11].length() > 0) {
136                     st.setString(17, line[11]);
137                 }
138 
139                 if (line[12].length() > 0) {
140                     st.setString(18, line[12]);
141                 }
142 
143                 if (line[13].length() > 0) {
144                     st.setString(19, line[13]);
145                 }
146 
147                 if (line[14].length() > 0) {
148                     st.setString(20, line[14]);
149                 }
150 
151                 if (line[15].length() > 0) {
152                     st.setString(21, line[15]);
153                 }
154 
155                 try {
156 
157                     try {
158                         st.executeUpdate();
159                     } catch (SQLException sqlEx) {
160                         throw new SQLException("Inserting code point "
161                                 + lineToString(line) + ": " + sqlEx, sqlEx);
162                     }
163                 } finally {
164                     st.close();
165                 }
166             }
167         } finally {
168             reader.close();
169         }
170     }
171 
172     public static void populate(Connection conn, File dataDir)
173             throws SQLException, IOException {
174         logger.info("Dropping tables");
175 
176         // Drop and re-create the table
177         Statement st = conn.createStatement();
178         try {
179             st.execute("DROP TABLE Locator");
180         } catch (SQLException sqlEx) {
181             // Don't care - might be didn't exist to begin with
182         } finally {
183             st.close();
184         }
185 
186         st = conn.createStatement();
187         try {
188             logger.info("Creating tables");
189             st.execute("CREATE TABLE Locator(" + "name VARCHAR(255) NULL, "
190                     + "classification VARCHAR(255) NULL, "
191                     + "centreEasting INTEGER NOT NULL, "
192                     + "centreNorthing INTEGER NOT NULL, "
193                     + "centreLatitude DOUBLE NOT NULL, "
194                     + "centreLongitude DOUBLE NOT NULL, "
195                     + "minimumNorthing INTEGER NOT NULL, "
196                     + "maximumNorthing INTEGER NOT NULL, "
197                     + "minimumEasting INTEGER NOT NULL, "
198                     + "maximumEasting INTEGER NOT NULL, "
199                     + "minimumLatitude DOUBLE NOT NULL, "
200                     + "maximumLatitude DOUBLE NOT NULL, "
201                     + "minimumLongitude DOUBLE NOT NULL, "
202                     + "maximumLongitude DOUBLE NOT NULL, "
203                     + "settlement VARCHAR(255) NULL, "
204                     + "locality VARCHAR(255) NOT NULL, "
205                     + "county VARCHAR(255) NOT NULL, "
206                     + "localAuthority VARCHAR(255) NOT NULL, "
207                     + "tile10k CHAR(6) NOT NULL, "
208                     + "tile50k CHAR(6) NOT NULL, "
209                     + "source VARCHAR(255) NOT NULL);");
210         } finally {
211             st.close();
212         }
213 
214         // Get all data files in the directory
215         File[] files = dataDir.listFiles();
216 
217         logger.info("Found " + files.length + " locator files in directory "
218                 + dataDir.getAbsolutePath());
219         for (int num = 0; num < files.length; num++) {
220             // Pass all files through
221             populateLocator(conn, files[num]);
222         }
223     }
224 }