0

I am curious to if there is an faster way to transfer data from a file, to my postgreSQL database. I am using java. The files are GRIB files, so basically weather type files. I have created a text file where I would go grab the name of the file Im looking for and use it to grab my data then send that data into my database. Can someone give me a hint to what I should to improve my speed?

public class JgribDEXcopy{
// Global variables
static GribFile gribfile;
static int numOFrecords;
static int numOFgds;
protected static int eventX;
protected static int eventY;
protected static int xIA = 543;
protected static int yIA = 451;
protected static int xIAend = 703;
protected static int yIAend = 591;
private static Connection connection = null;
private static PreparedStatement state = null;
static String st;
static int k, l, in = 0;
static File file = new File("E:/IFIC/2009.txt");

// Gets the number of records in the GRIB file
// Prints out that number for testing the correct amount of records
public static void read(GribFile n){
    numOFrecords = n.getRecordCount();
    //System.out.println("HELLO: " + numOFrecords + " & " + numOFgds + " Is the magic number");
}


 public static void main(String[] args) throws ClassNotFoundException, SQLException, NoSuchElementException, IOException{


     BufferedReader br = new BufferedReader(new FileReader(file));
     String line = "";
     while((line = br.readLine()) != null){
            int counter = 0;

     // Connecting to the database
     Class.forName("org.postgresql.Driver");
     connection = DriverManager.getConnection("jdbc:postgresql://database/database","username", "password");

     String nline = "E:/IFIC/IIHS_data/ST4." + line + ".01h.grb";

    // Reading of grib files must be inside a try-catch block
    try{
      // Create GribFile instance
      gribfile = new GribFile(nline);
      read(gribfile);

      // Dump verbose inventory for each record
      //gribfile.listRecords(System.out);
      // Dumps the grid/s into an array
      GribRecordGDS[] arr = gribfile.getGrids();
      float arrBDS;

      // Gets the Width and Height of the grid
      // Calculates the number of points in the grid
      eventX = arr[0].getGridNX();
      eventY = arr[0].getGridNY();
      numOFgds = eventX * eventY;
      //System.out.println(numOFgds);
      //System.out.println(eventX);
      //System.out.println(eventY);

      // Creates a writer that will print to a file
      //PrintWriter writer = new PrintWriter("ST4.2014030123.01h.txt", "UTF-8");
      //System.out.println((int) (gribfile.getRecord(1).getTime().getTime().getTime()/1000));

      // Filters for the area Iowa Flood Center deals with (which is basically Iowa area)
      // Takes the database connection and inserts the necessary data
      for(int i = 0; i <= eventX; i++){
          for(int j = 0; j <= eventY; j++){
              if(i >= xIA && i <= xIAend && j <= yIAend && j >= yIA){
                  arrBDS = gribfile.getRecord(1).getValue(i, j);
                  if(arrBDS != 0.000){
                      k = i;
                      l = j;
                      in = 1000 * (k+1) + (l+1);
                      //writer.println(i + "," + j + "," + arrBDS);
                      //System.out.println(in + "," + arrBDS);

//                        st = "INSERT INTO master_st4(unix_timestamp, hrap_xy, rain) values(?, ?, ?)";
//                        //System.out.println((int)(gribfile.getRecord(1).getTime().getTime().getTime()/1000));
//                        
//                        state = connection.prepareStatement(st);
//                        state.setInt(1, (int) (gribfile.getRecord(1).getTime().getTime().getTime()/1000));
//                        state.setInt(2, in);
//                        state.setDouble(3, arrBDS);                    
//                        state.executeUpdate();
                  }
                      // Keeps track of all the X, Y coordinates with zero rainfall
                      if(arrBDS == 0.0){
                      counter++;
                  }
              }
          }
      }
      System.out.println("There is " + counter + " number of zero's");
      //writer.close();
    }
    catch (FileNotFoundException noFileError){
      System.err.println("FileNotFoundException : " + noFileError);
    }
    catch (IOException ioError){
      System.err.println("IOException : " + ioError);
    }
    catch (NoValidGribException noGrib){
      System.err.println("NoValidGribException : " + noGrib);
    }
    catch (NotSupportedException noSupport){
      System.err.println("NotSupportedException : " + noSupport);
    }
  // closes database connection  
  connection.close();
  }
  br.close();
 }
}
2
  • PgAdmin is a administration tool for PostgreSQL databases Commented Jun 19, 2014 at 21:30
  • If you can convert GRIB file in other plain format like csv, tsv. You can use dbis to migrate without single line of code. Check detail dbisweb.wordpress.com Commented Jan 26, 2018 at 14:17

1 Answer 1

1

Bulk-load the data using the PgJDBC COPY API. Failing that, at least do multi-valued inserts grouped into transactions.

See:

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.