landsat8_schema.sql

-- copy eight from '/Users/ch/Desktop/meta/LANDSAT_8.csv' delimiter ',' CSV header;

create type sensor_t as enum ('OLI_TIRS', 'OLI', 'TIRS');
create type daynight_t as enum ('DAY', 'NIGHT');
create type datatype_t as enum ('L1GT', 'L1T', 'PR');
create type nadir_t as enum ('NADIR', 'OFFNADIR');

create table eight (
	sceneID text primary key,
	sensor sensor_t,
	acquisitionDate date,
	dateUpdated date,
	browseAvailable boolean,
	browseURL text,
	path int, -- note "path" is reserved
	row int,
	upperLeftCornerLatitude double precision,
	upperLeftCornerLongitude double precision,
	upperRightCornerLatitude double precision,
	upperRightCornerLongitude double precision,
	lowerLeftCornerLatitude double precision,
	lowerLeftCornerLongitude double precision,
	lowerRightCornerLatitude double precision,
	lowerRightCornerLongitude double precision,
	sceneCenterLatitude double precision,
	sceneCenterLongitude double precision,
	cloudCover double precision,
	cloudCoverFull double precision,
	FULL_UL_QUAD_CCA double precision,
	FULL_UR_QUAD_CCA double precision,
	FULL_LL_QUAD_CCA double precision,
	FULL_LR_QUAD_CCA double precision,
	dayOrNight daynight_t,
	flightPath text, -- not sure what this is
	sunElevation double precision,
	sunAzimuth double precision,
	receivingStation text,
	sceneStartTime timestamp,
	sceneStopTime timestamp,
	lookAngle double precision,
	imageQuality1 int,
	imageQuality2 int,
	gainBand1 double precision,
	gainBand2 double precision,
	gainBand3 double precision,
	gainBand4 double precision,
	gainBand5 double precision,
	gainBand6H double precision,
	gainBand6L double precision,
	gainBand7 double precision,
	gainBand8 double precision,
	gainChangeBand1 double precision,
	gainChangeBand2 double precision,
	gainChangeBand3 double precision,
	gainChangeBand4 double precision,
	gainChangeBand5 double precision,
	gainChangeBand6H double precision,
	gainChangeBand6L double precision,
	gainChangeBand7 double precision,
	gainChangeBand8 double precision,
	satelliteNumber int,
	DATA_TYPE_L1 datatype_t,
	cartURL text,
	DATE_ACQUIRED_GAP_FILL text,
	DATA_TYPE_L0RP text,
	DATUM text,
	ELEVATION_SOURCE text,
	ELLIPSOID text,
	EPHEMERIS_TYPE text,
	FALSE_EASTING double precision,
	FALSE_NORTHING double precision,
	GAP_FILL text,
	GROUND_CONTROL_POINTS_MODEL text,
	GROUND_CONTROL_POINTS_VERIFY text,
	GEOMETRIC_RMSE_MODEL text,
	GEOMETRIC_RMSE_MODEL_X double precision,
	GEOMETRIC_RMSE_MODEL_Y double precision,
	GEOMETRIC_RMSE_VERIFY double precision,
	GRID_CELL_SIZE_PANCHROMATIC double precision,
	GRID_CELL_SIZE_REFLECTIVE double precision,
	GRID_CELL_SIZE_THERMAL double precision,
	MAP_PROJECTION_L1 text,
	MAP_PROJECTION_L0RA text,
	ORIENTATION text,
	OUTPUT_FORMAT text,
	PANCHROMATIC_LINES int,
	PANCHROMATIC_SAMPLES int,
	L1_AVAILABLE text,
	REFLECTIVE_LINES int,
	REFLECTIVE_SAMPLES int,
	RESAMPLING_OPTION text,
	SCAN_GAP_INTERPOLATION text,
	THERMAL_LINES int,
	THERMAL_SAMPLES int,
	TRUE_SCALE_LAT double precision,
	UTM_ZONE text,
	VERTICAL_LON_FROM_POLE double precision,
	PRESENT_BAND_1 boolean,
	PRESENT_BAND_2 boolean,
	PRESENT_BAND_3 boolean,
	PRESENT_BAND_4 boolean,
	PRESENT_BAND_5 boolean,
	PRESENT_BAND_6 boolean,
	PRESENT_BAND_7 boolean,
	PRESENT_BAND_8 boolean,
	NADIR_OFFNADIR nadir_t
);

create index sceneid_idx on eight(sceneID);
create index path_idx on eight(path);
create index row_idx on eight(row);
create index sclat_idx on eight(sceneCenterLatitude);
create index sclon_idx on eight(sceneCenterLongitude);