landsat-db.rb

# get the CSV from http://landsat.usgs.gov/metadata_service/bulk_metadata_files/LANDSAT_8.csv
require 'csv'

FILE   = File.expand_path("..", __FILE__) + "/initial/LANDSAT_8.csv"

SCHEMA = <<-SQL
  DROP TABLE "landsat8";
  CREATE TABLE "landsat8" (
    "sceneid" varchar(21) not null, 
    sensor varchar(8) not null, 
    "acquisitiondate" date not null, 
    "dateupdated" date not null, 
    "browseavailable" boolean not null, 
    "browseurl" varchar(89) not null, 
    path integer not null, 
    row integer not null, 
    "upperleftcornerlatitude" float not null, 
    "upperleftcornerlongitude" float not null, 
    "upperrightcornerlatitude" float not null, 
    "upperrightcornerlongitude" float not null, 
    "lowerleftcornerlatitude" float not null, 
    "lowerleftcornerlongitude" float not null, 
    "lowerrightcornerlatitude" float not null, 
    "lowerrightcornerlongitude" float not null, 
    "scenecenterlatitude" float not null, 
    "scenecenterlongitude" float not null,
    "cloudcover" integer not null, 
    "cloudcoverfull" float not null, 
    "full_ul_quad_cca" varchar(32), 
    "full_ur_quad_cca" varchar(32), 
    "full_ll_quad_cca" varchar(32), 
    "full_lr_quad_cca" varchar(32), 
    "dayornight" varchar(5) not null, 
    "flightpath" varchar(32), 
    "sunelevation" float not null, 
    "sunazimuth" float not null, 
    "receivingstation" varchar(3) not null, 
    "scenestarttime" varchar(25) not null, 
    "scenestoptime" varchar(25) not null, 
    "lookangle" varchar(32), 
    "imagequality1" integer not null, 
    "imagequality2" varchar(32), 
    "gainband1" varchar(32), 
    "gainband2" varchar(32), 
    "gainband3" varchar(32), 
    "gainband4" varchar(32), 
    "gainband5" varchar(32), 
    "gainband6h" varchar(32), 
    "gainband6l" varchar(32), 
    "gainband7" varchar(32), 
    "gainband8" varchar(32), 
    "gainchangeband1" varchar(32), 
    "gainchangeband2" varchar(32), 
    "gainchangeband3" varchar(32), 
    "gainchangeband4" varchar(32), 
    "gainchangeband5" varchar(32), 
    "gainchangeband6h" varchar(32), 
    "gainchangeband6l" varchar(32), 
    "gainchangeband7" varchar(32), 
    "gainchangeband8" varchar(32), 
    "satellitenumber" varchar(32), 
    "data_type_l1" varchar(4) not null, 
    "carturl" varchar(96) not null, 
    "date_acquired_gap_fill" varchar(32), 
    "data_type_l0rp" varchar(32), 
    "datum" varchar(32), 
    "elevation_source" varchar(32), 
    "ellipsoid" varchar(32), 
    "ephemeris_type" varchar(32), 
    "false_easting" varchar(32), 
    "false_northing" varchar(32), 
    "gap_fill" varchar(32), 
    "ground_control_points_model" varchar(32), 
    "ground_control_points_verify" varchar(32), 
    "geometric_rmse_model" varchar(32), 
    "geometric_rmse_model_x" float not null, 
    "geometric_rmse_model_y" float not null, 
    "geometric_rmse_verify" varchar(32), 
    "grid_cell_size_panchromatic" varchar(32), 
    "grid_cell_size_reflective" varchar(32), 
    "grid_cell_size_thermal" varchar(32), 
    "map_projection_l1" varchar(32), 
    "map_projection_l0ra" varchar(32), 
    "orientation" varchar(32), 
    "output_format" varchar(32), 
    "panchromatic_lines" varchar(32), 
    "panchromatic_samples" varchar(32), 
    "l1_available" varchar(32), 
    "reflective_lines" varchar(32), 
    "reflective_samples" varchar(32), 
    "resampling_option" varchar(32), 
    "scan_gap_interpolation" varchar(32), 
    "thermal_lines" varchar(32), 
    "thermal_samples" varchar(32), 
    "true_scale_lat" varchar(32), 
    "utm_zone" varchar(32), 
    "vertical_lon_from_pole" varchar(32), 
    "present_band_1" varchar(32), 
    "present_band_2" varchar(32), 
    "present_band_3" varchar(32), 
    "present_band_4" varchar(32), 
    "present_band_5" varchar(32), 
    "present_band_6" varchar(32), 
    "present_band_7" varchar(32), 
    "present_band_8" varchar(32), 
    "nadir_offnadir" varchar(8) not null
  );
  ALTER TABLE landsat8 ADD COLUMN id BIGSERIAL PRIMARY KEY;
  ALTER TABLE landsat8 ADD COLUMN "pathrow" varchar(6);
  ALTER TABLE landsat8 ADD COLUMN the_geom geometry(POLYGON,4326);
  CREATE INDEX the_geom_idx ON landsat8 USING gist(the_geom);
  UPDATE landsat8 SET pathrow = path::text || row::text;
  VACUUM ANALYZE landsat8;
SQL

class LandsatDB
  class << self
    def cmd(txt, quot_type = "single", flags = "")
      if quot_type == "single"
        `echo '#{txt}' | psql #{flags} landsat8`
      elsif quot_type == "double"
        `echo "#{txt}" | psql #{flags} landsat8`
      end
    end

    def create
      cmd(SCHEMA)
    end

    def load
      CSV.foreach(FILE, :headers => true) do |row|
        vals = "'" + row.headers.map {|q| row[q]}.join("','") + "'"
        txt = "INSERT INTO landsat8 (#{row.headers.join(",")}, the_geom) VALUES (#{vals}, ST_GeomFromText('POLYGON((
          #{row['upperLeftCornerLongitude']} #{row['upperLeftCornerLatitude']},
          #{row['upperRightCornerLongitude']} #{row['upperRightCornerLatitude']},
          #{row['lowerRightCornerLongitude']} #{row['lowerRightCornerLatitude']},
          #{row['lowerLeftCornerLongitude']} #{row['lowerLeftCornerLatitude']},
          #{row['upperLeftCornerLongitude']} #{row['upperLeftCornerLatitude']}
        ))', 4326));".gsub(/\n/," ").gsub(/\s+/," ")
        puts txt
        cmd(txt, "double")
      end
    end

    def query(query)
      result = cmd(query, "double", "-A -F , -X -t")
      result.split("\n").map {|q| q.split(",") }
    end
  end
end

if __FILE__ == $0
  cmd = ARGV[0]
  LandsatDB.send(cmd)
end