How to read and update rows of a Google Fusion Table using JAVA code

Question

(Edit: This is a question that I had and I answer it in the hopes of helping someone else who had a similar question.)

I am trying to clean up geographic data in my Google Fusion Table and would like to write a Java program to read in select Fusion Table rows, modify columns in each row and write out the modified rows back to the original fusion table.

I have found Christian Junk's example code in the Google API Client Libraries documentation: "fusiontables-cmdline-sample" that shows how to: Authorize access to a users fusion tables, list tables, create a table, insert data into a table, show rows, delete a table.

How do I modify this example to make updates to selected rows in a table? (see answer with code below)

[edit]: I didn't find any good solutions on the Net. I have written the solution in Java and will answer in the answers in the hope that it can help someone else how is trying to do this. I am a novice Java programmer so the code reflects that. I also needed to get nearby big cities based on a gps location and used GeoNames api (citiesJSON) creating a bounding box to do that. This solution uses JSON to access items returned from REST calls.

Answer 1

I have written a Java program that does the row data modification described in the question. It uses Christian Junk's example noted in the question and also calls GeoNames citiesJSON webservice as described in the question (sending bounding box coordinates in the parameters). I'm a novice in Java so the code is what it is. I do a lot of commenting in order to reuse code, like SQL queries, later.

You can find my solution on Github at: FusionTableModifyJava The primary module of interest is: FusionTableSample.java Here are the functions that do the getRows and updateRows. Everything else can be seen at github (by Microsoft$):

private static void getRows(String tableId) throws IOException {
    View.header("Updating Rows From Table");
    /*Sql sql = fusiontables.query().sql("SELECT RowID, 'Area Name', Notes, Number FROM " + tableId +
            " Where Manager = '' AND 'Review 1' CONTAINS IGNORING CASE '.fs.' Order by Number ASC LIMIT 3000");*/
    /*Sql sql = fusiontables.query().sql("SELECT RowID, 'Area Name', Notes, Number FROM " + tableId +
            " Where 'Area Name' CONTAINS IGNORING CASE 'Tioga George' Order by Number ASC LIMIT 3000");*/
    /*Sql sql = fusiontables.query().sql("SELECT RowID, 'Area Name', Notes, Number FROM " + tableId +
            " Where 'Area Name' ='' Order by Number DESC LIMIT 2000");*/
    /*Sql sql = fusiontables.query().sql("SELECT RowID, 'Area Name', Notes, Number FROM " + tableId +
            " Where 'Area Name' CONTAINS 'X01' Order by Number DESC LIMIT 1"); */
    /*AND 'City (nearest)' DOES NOT CONTAIN IGNORING CASE 'Mexico'*/

    /*Sql sql = fusiontables.query().sql("SELECT RowID, 'Area Name', Notes, Number, Location FROM " + tableId +
            " Where State = '' Order by Number DESC LIMIT 100");*/
    /*Sql sql = fusiontables.query().sql("SELECT RowID, 'Area Name', Notes, Number, Location FROM " + tableId +
            " Where State = 'BCS' Order by Number DESC LIMIT 100");*/
    Sql sql = fusiontables.query().sql("SELECT RowID, 'Area Name', Notes, Number, Location, State, Codes FROM " + tableId +
            " Where State = 'ID' AND 'City (nearest)' = '' Order by Number DESC LIMIT 100");

    try {
        Sqlresponse response = sql.execute();
        // System.out.println(response.toPrettyString());

        mylist = response.getRows();

    } catch (IllegalArgumentException e) {
        // For google-api-services-fusiontables-v1-rev1-1.7.2-beta this exception will always
        // been thrown.
        // Please see issue 545: JSON response could not be deserialized to Sqlresponse.class
        // http://code.google.com/p/google-api-java-client/issues/detail?id=545
    }
}

private static void updateRows(String tableId) throws IOException {
    // IOException needed  ParseException
    count = 1;
    mylist.forEach((myRow) -> {
        try {
            // modify fields in table...
            //newAreaName = kt.firstpart(myRow.get(NOTES).toString()); //get Notes first sentence
            //newAreaName = newAreaName.replace("'", "''");
            //newAreaName += " X01";
            //String state = getStateFrmLoc(myRow.get(LOCATION).toString());
            //String state = "MX-BCS";
            float km;
            if ( "AK,MT,NV".contains(myRow.get(STATE).toString()) ) {
                km = 180f; // 111.85 miles
            } else {
                km = 80.5f;  // 50 miles
            }

            BigCity big = new BigCity(myRow.get(LOCATION).toString(), km);
            String cityState = big.cityName +", "+big.state;

            if (big.population < 10000f) {
                System.out.println("Skip for low population :"+myRow.get(NUMBER));
            } else {

                sqlupdate = "UPDATE " + tableId + " " +
                        "SET 'City (nearest)' = '" + cityState + "' " +
                        ",'Codes' = '" + myRow.get(CODES).toString() + ",#U1' " +
                        "WHERE ROWID = " + myRow.get(ROW_ID);
                System.out.println("[" + count + "]" + myRow.get(NUMBER) + ": " + sqlupdate);

                // do the update...
                if (!mtest) {  // if testing then don't update
                    sql_doupdate(sqlupdate);
                }
                count++;
                if ((count % 30) == 0) {
                    System.out.println("waiting 60 seconds");
                    TimeUnit.SECONDS.sleep(60); //Fusion Tables allows 30 updates then must wait 1 minute.
                }
            }
            } catch(Exception e){
                System.out.println(e.getMessage());
        }

    });

}