Skip to main content

Upload Logs to PSQL The Hard Way

The PostgreSQL DB the data will be stored in has additional columns to support the mapping feature in Grafana. Grafana requires coordinate colums to be individualized, however, the RPLATE logs have a single column for both lat and lon. I use Google Sheets to prepare the data before importing into the DB, and here is my process. Currently, it's a manual one until I can write some kind of script to deal with all this on the fly.

  1. Download the Trips and VMT CSV files, or copy/pasta them into a Google Sheet, one for each
  2. On the Trips sheet, add 2 columns each to the right of the Start Location and End Location columns
  3. Use the following formulas in their respective columns:
    1. Column F: =IF(E2="", "", INDEX(SPLIT(E2, ","), 1))
    2. Column G: =IF(E2="", "", INDEX(SPLIT(E2, ","), 2))
    3. Column I: =IF(H2="", "", INDEX(SPLIT(H2, ","), 1))
    4. Column J: =IF(H2="", "", INDEX(SPLIT(H2, ","), 2))
  4. The data from columns E and H now split, create 2 more columns to the left of those
  5. Select columns F & G, then Paste Values Only into columns H & I
  6. Select columns K & L, then Paste Values Only into columns M & N
  7. Delete the columns with the formulas (you don't want to import those into the DB as well as the start/end_location columns (combined lat, lon)
  8. Download the sheet as a CSV
  9. SSH into the server, upload the CSV to the /opt directory (or wherever you want it)
  10. Login to the DB as your DB user and do these things:
# LOGIN TO YOUR DB AS YOUR OWN USER!
sudo -u postgres psql
/* RUN THE COMMAND TO IMPORT THE CSV 
   BE SURE TO UPDATE YOUR FILE LOCATION, NAME AND THE COLUMN NAMES! */
COPY trips(date, vehicle_name, vin, plate_number, start_lat, start_lon, end_lat, end_lon, start_address, end_address, start_time, end_time, trip_time, average_speed, max_speed, total_mileage)
FROM '/opt/trips.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',', QUOTE '"');

I didn't have to alter any data for the VMT logs, so I just repeated the above query to import that CSV into the DB:

/* RUN THE COMMAND TO IMPORT THE CSV 
   BE SURE TO UPDATE YOUR FILE LOCATION, NAME AND THE COLUMN NAMES! */
COPY vmt(date, vehicle_name, vin, plate_number, total_trips, average_speed, max_speed, total_mileage)
FROM '/opt/vmt.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',', QUOTE '"');
Back to top