View Javadoc

1   /*
2    * Trip Tracker, a real-time position tracking system for the Internet.
3    * Copyright (C) 2006  Team Trip Tracker
4    *
5    * This program is free software; you can redistribute it and/or modify it
6    * under the terms of the GNU General Public License as published by the
7    * Free Software Foundation; either version 2 of the License, or (at your
8    * option) any later version.
9    *
10   * This program is distributed in the hope that it will be useful, but
11   * WITHOUT ANY WARRANTY; without even the implied warranty of
12   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13   * General Public License for more details.
14   *
15   * You should have received a copy of the GNU General Public License along
16   * with this program; if not, write to the Free Software Foundation, Inc.,
17   * 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
18   */
19  
20  package triptracker.testing;
21  
22  import java.sql.Connection;
23  import java.sql.DriverManager;
24  import java.sql.ResultSet;
25  import java.sql.SQLException;
26  import java.sql.Statement;
27  import java.util.ArrayList;
28  import java.util.List;
29  
30  import triptracker.core.Coordinate;
31  
32  /***
33   * Converts from wierd sexagesimal coordinate format to decimal format and fixes
34   * the old faulty conversion from NMEA sexagesimal to decimal.
35   */
36  public class DBConversion {
37  	public static void main(String[] args) {
38  //		convNmeaToDec();
39  		convertNewF(55);
40  		markNewF(55);
41  	}
42  
43  	/***
44  	 * Connect to the MySQL database.
45  	 * 
46  	 * @return database Connection object
47  	 */
48  	private static Connection connect() {
49  		Connection link = null;
50  		
51  		try {
52  			// Logging on to the MySQL database server.
53  			Class.forName("com.mysql.jdbc.Driver");
54  			String url = "jdbc:mysql://127.0.0.1:3306/trip";
55  			link = DriverManager.getConnection(url, "trip", "track");
56  		} catch (ClassNotFoundException e) {
57  			e.printStackTrace();
58  		} catch (SQLException e) {
59  			e.printStackTrace();
60  		}
61  		return link;
62  	}
63  	
64  
65  	/***
66  	 * Simply mark all coordinates of a given route with the 'newf' marker.
67  	 */
68  	private static void markNewF(final int routeId) {
69  		Connection link = connect();
70  
71  		long time = System.currentTimeMillis();
72  		
73  		try {
74  			Statement stmt = link.createStatement();
75  			
76  			// Update coordinates with 'newf' = 1.
77  			String setQuery = "UPDATE trip_coordinate2 "
78  				+ "SET newf = '1' "
79  				+ "WHERE rId = '" + routeId + "'";
80  			
81  			int rows = stmt.executeUpdate(setQuery);
82  			System.out.println("Update affected " + rows + " row(s).");
83  			
84  			stmt.close();
85  			link.close();
86  		} catch (SQLException e) {
87  			e.printStackTrace();
88  		}
89  		
90  		long curTime = System.currentTimeMillis();
91  		System.out.println("Duration: " + (curTime - time) + " ms");
92  	}
93  
94  	/***
95  	 * Converts coordinates for a given route from old NMEA sexagesimal format
96  	 * to new decimal format.
97  	 */
98  	public static void convertNewF(final int routeId) {
99  		Connection link = connect();
100 		int cId;
101 		double lat, lng;
102 		List<Coordinate> coords = new ArrayList<Coordinate>();
103 
104 		long time = System.currentTimeMillis();
105 		
106 		try {
107 			Statement stmt = link.createStatement();
108 
109 			String getQuery = "SELECT cId, latitude, longitude "
110 				+ "FROM trip_coordinate2 "
111 				+ "WHERE rId = '" + routeId + "' AND newf = '0'";
112 			
113 			ResultSet rs = stmt.executeQuery(getQuery);
114 			
115 			// Add all coords we want to change to list.
116 			while (rs.next()) {
117 				cId = rs.getInt(1);
118 				lat = rs.getDouble(2);
119 				lng = rs.getDouble(3);
120 				
121 				// FIXME Create conversion from old erroneous decimal to new
122 				// correct decimal format.
123 				
124 				// Convert to new decimal format.
125 				lat = Coordinate.nmeaToDec(lat);
126 				lng = Coordinate.nmeaToDec(lng);
127 				
128 				// Add the coord to the conversion queue.
129 				coords.add(new Coordinate(cId, lat, lng));
130 			}
131 			
132 			System.out.println("Updating " + coords.size() + " records...");
133 			
134 			// Run update on the queue.
135 			for (Coordinate coord : coords) {
136 				String setQuery = "UPDATE trip_coordinate2 "
137 					+ "SET latitude = '" + coord.getY()
138 					+ "', longitude = '" + coord.getX()
139 					+ "', newf = '1' "
140 					+ "WHERE cId = '" + coord.getCoordId() + "'";
141 				
142 				stmt.executeUpdate(setQuery);
143 			}
144 			
145 			stmt.close();
146 			link.close();
147 		} catch (SQLException e) {
148 			e.printStackTrace();
149 		}
150 		
151 		long curTime = System.currentTimeMillis();
152 		System.out.println("Duration: " + (curTime - time) + " ms");
153 	}
154 
155 	/***
156 	 * Converts from old NMEA sexagesimal format to new decimal format.
157 	 */
158 	public static void convNmeaToDec() {
159 		Connection link = connect();
160 		int cId;
161 		double lat, lng;
162 		boolean convert = false;
163 		List<Coordinate> coords = new ArrayList<Coordinate>();
164 
165 		long time = System.currentTimeMillis();
166 		
167 		try {
168 			Statement stmt = link.createStatement();
169 
170 			String getQuery = "SELECT cId, latitude, longitude "
171 				+ "FROM trip_coordinate";
172 			
173 			ResultSet rs = stmt.executeQuery(getQuery);
174 			
175 			// Add all coords we want to change to list.
176 			while (rs.next()) {
177 				convert = false;
178 				cId = rs.getInt(1);
179 				lat = rs.getDouble(2);
180 				lng = rs.getDouble(3);
181 				
182 				// Reverse direction on old coords.
183 				if (lat < -5000) {
184 					convert = true;
185 					lat = -lat;
186 				}
187 				
188 				if (lng < -500) { 
189 					convert = true;
190 					lng = -lng;
191 				}
192 				
193 				// Convert to new decimal format.
194 				if (lat > 5000) {
195 					convert = true;
196 					lat = Coordinate.nmeaToDec(lat);
197 				}
198 				
199 				if (lng > 500) {
200 					convert = true;
201 					lng = Coordinate.nmeaToDec(lng);
202 				}
203 				
204 				// Add the coord to the conversion queue.
205 				if (convert) {
206 					coords.add(new Coordinate(cId, lat, lng));
207 				}
208 			}
209 			
210 			System.out.println("Updating " + coords.size() + " records...");
211 			
212 			// Run update on the queue.
213 			for (Coordinate coord : coords) {
214 				String setQuery = "UPDATE trip_coordinate2 "
215 					+ "SET latitude = '" + coord.getY()
216 					+ "', longitude = '" + coord.getX()
217 					+ "', newf = '1' "
218 					+ "WHERE cId = '" + coord.getCoordId() + "'";
219 				
220 				stmt.executeUpdate(setQuery);
221 			}
222 			
223 			stmt.close();
224 			link.close();
225 		} catch (SQLException e) {
226 			e.printStackTrace();
227 		}
228 		
229 		long curTime = System.currentTimeMillis();
230 		System.out.println("Duration: " + (curTime - time) + " ms");
231 	}
232 
233 }