private Table populateTable(Table t, Graph g) {
for (final Iterator it = g.nodes(); it.hasNext(); ) {
final Node n = (Node) it.next();
t.addRow();
for (int i = 0; i < n.getColumnCount(); i++) {
t.set(t.getRowCount() - 1, i, n.get(i));
}
t.set(t.getRowCount() - 1, UNIQUE_INDEX_COLUMN_NAME, new Integer(t.getRowCount()));
}
return t;
}
/** Initialize mapping between prefuse table rows and the rows reported by this model. */
private void initRowMap() {
m_rowmap = new int[m_table.getRowCount()];
IntIterator rows = m_table.rows();
for (int i = 0; rows.hasNext(); ++i) {
m_rowmap[i] = rows.nextInt();
}
}
public void testLoadFromMySQLDatabase() {
String host = "localhost";
String database = "friendster";
String user = "******";
String password = "";
String keyField = "uid";
String query1 =
"SELECT profiles.* FROM profiles, graph WHERE "
+ "(graph.uid1 = 186297 AND profiles.uid = graph.uid2)";
String query2 =
"SELECT profiles.* FROM profiles, graph WHERE "
+ "(graph.uid1 = 21721 AND profiles.uid = graph.uid2)";
// String query = "SELECT gender, age, COUNT(*) FROM profiles GROUP BY gender,age";
s_logger.info(TestConfig.memUse());
Table t = null;
try {
DatabaseDataSource db = ConnectionFactory.getMySQLConnection(host, database, user, password);
s_logger.info(TestConfig.memUse());
t = db.getData(t, query1, keyField);
db.loadData(t, query2, keyField);
} catch (Exception e) {
e.printStackTrace();
fail("Error connecting to database");
}
// text-dump
StringBuffer sbuf = new StringBuffer('\n');
sbuf.append("--[Table: ")
.append(t.getRowCount())
.append(" rows, ")
.append(t.getColumnCount())
.append(" cols]--\n");
for (int c = 0, idx = -1; c < t.getColumnCount(); ++c) {
String name = t.getColumnType(c).getName();
if ((idx = name.lastIndexOf('.')) >= 0) name = name.substring(idx + 1);
sbuf.append(c).append("\t").append(name).append("\t").append(t.getColumnName(c)).append('\n');
}
sbuf.append('\n');
sbuf.append(TestConfig.memUse()).append('\n');
s_logger.info(sbuf.toString());
m_table = t;
}
/** @see javax.swing.table.TableModel#getRowCount() */
public int getRowCount() {
return m_table.getRowCount();
}
/*
* Input data from the "Place_Column_Name" is obtained from the original table & Lookups
* are made to appropriate maps. After processing all rows, the new output table is
* returned having original data and 2 new columns for Latitude & Longitude.
*/
public static Table compute(
String locationColumnName, Table originalTable, LogService logger, Geocoder geocoder) {
/*
* Create Blank new output table using the schema from the original table.
*/
Table outputTable = originalTable.getSchema().instantiate();
String outputTableLatitudeColumnName =
TableUtilities.formNonConflictingNewColumnName(
originalTable.getSchema(), LATITUDE_COLUMN_NAME_SUGGESTIONS);
String outputTableLongitudeColumnName =
TableUtilities.formNonConflictingNewColumnName(
originalTable.getSchema(), LONGITUDE_COLUMN_NAME_SUGGESTIONS);
outputTable.addColumn(outputTableLatitudeColumnName, Double.class);
outputTable.addColumn(outputTableLongitudeColumnName, Double.class);
logger.log(
LogService.LOG_INFO,
String.format(
"Latitude & Longitude values added to %s & %s, respectively.",
outputTableLatitudeColumnName, outputTableLongitudeColumnName));
int locationColumnNumber = originalTable.getColumnNumber(locationColumnName);
int latitudeColumnNumber = outputTable.getColumnNumber(outputTableLatitudeColumnName);
int longitudeColumnNumber = outputTable.getColumnNumber(outputTableLongitudeColumnName);
Map<String, Geolocation> geocodedAddressToGeoLocation = new HashMap<String, Geolocation>();
FrequencyMap<String> failedFrequency = new FrequencyMap<String>(true);
Iterator<?> locationColumnIterator = originalTable.iterator();
while (locationColumnIterator.hasNext()) {
int currentRowNumber = Integer.parseInt(locationColumnIterator.next().toString());
/* Start geocoding */
Geolocation geolocation = DEFAULT_NO_LOCATION_VALUE;
String currentLocation = "";
Object currentLocationObject = originalTable.get(currentRowNumber, locationColumnNumber);
if (currentLocationObject != null) {
currentLocation = currentLocationObject.toString();
String currentLocationUppercase = currentLocation.toUpperCase();
/* Avoid re-geocoding the same place */
if (geocodedAddressToGeoLocation.containsKey(currentLocationUppercase)) {
geolocation = geocodedAddressToGeoLocation.get(currentLocationUppercase);
if (geolocation == DEFAULT_NO_LOCATION_VALUE) {
failedFrequency.add(currentLocation);
}
} else {
try {
geolocation = geocoder.geocodingFullForm(currentLocationUppercase);
} catch (GeoCoderException e) {
try {
/* Try lookup in the abbreviation */
geolocation = geocoder.geocodingAbbreviation(currentLocationUppercase);
} catch (GeoCoderException e1) {
/* No result is found */
failedFrequency.add(currentLocation);
}
}
/* Add to geocoded map */
geocodedAddressToGeoLocation.put(currentLocationUppercase, geolocation);
}
} else {
failedFrequency.add(currentLocation);
}
/*
* Add the new row to the new table
* by copying the original row & then adding 2 new columns to it.
*/
outputTable.addRow();
TableUtilities.copyTableRow(currentRowNumber, currentRowNumber, outputTable, originalTable);
outputTable.set(currentRowNumber, latitudeColumnNumber, geolocation.getLatitude());
outputTable.set(currentRowNumber, longitudeColumnNumber, geolocation.getLongitude());
}
/* Warning user about failure */
if (!failedFrequency.isEmpty()) {
printWarningMessage(logger, locationColumnName, failedFrequency);
}
/* Show statistic information */
int totalRow = originalTable.getRowCount();
NumberFormat numberFormat = NumberFormat.getInstance();
logger.log(
LogService.LOG_INFO,
String.format(
"Successfully geocoded %s out of %s locations to geographic coordinates",
numberFormat.format(totalRow - failedFrequency.sum()), numberFormat.format(totalRow)));
return outputTable;
}