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.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
62 CSVReader reader = new CSVReader(new FileReader(dataFile), ':');
63 try {
64
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
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
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
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
177 Statement st = conn.createStatement();
178 try {
179 st.execute("DROP TABLE Locator");
180 } catch (SQLException sqlEx) {
181
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
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
221 populateLocator(conn, files[num]);
222 }
223 }
224 }