-- HQI helio query interface 
-- ce sont des structures, les anglais ont le catalogie
-- mettre tt en lower
-- type de structure AR region active, FL filament , CH choronal hole, 
-- obs join de observation et observatory
-- on conserve les longueur d'onde
-- date obs et end marche pour les images
-- les jd sont pour date_obs
-- exp_time correspond a time_exp_min
-- carre X1Y1 ---- X3 Y3 BR_HG_LONG1_DEG pour le spatial
--       X0Y0 ---- X2 Y2
-- on prend min des x min des y, max des x max des y ce sont des coordonnées heliographique long latitude et ne pas prendre les bvaleurs null
-- snapshot c'est bien la prevue, virer le champ snapshot
-- faire script python qui donne dans annotation l'ucd et l'utype, par contre unité à la main
-- recuperer la description 
-- preparer a partir d'un tableur la construction de q.rd
-- avant le PNST en février
-- DATA_TYPE = dataproduct_type transfo IMAGE en im et DYNAMIC SPECTRUM en ...
-- le format de fichier dans FILE_FORMAT
-- instrument_host_name observat
-- instrument_name instrume
-- user guest passwd guest
-- tt est sur voparis-mysql5-paris
-- récupérer les champs freq et considéré que ce sera en MHz tout le temps

-- create a foreighn table in postgres from a mysql view or table
-- you must have postgres 9.1 minimum + foreighn data wraper  https://github.com/dpage/mysql_fdw/releases/tag/REL-1_0_1
-- under postgres sudo -u postgres psql -p 5435 -h localhost gavo  (just because i use port 5435)

-- pour lancer postgres
--  /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &


-- regarder table materialisé

CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (address '127.0.0.1', port '3306');
-- use a mysql user that have select right
CREATE USER MAPPING FOR PUBLIC
SERVER mysql_svr
OPTIONS (username 'visite', password 'visite');

DROP SCHEMA heliot3 cascade;
CREATE SCHEMA heliot3;

-- DROP TABLE "VIEW_AR_HQI";

CREATE FOREIGN TABLE heliot3.view_t3_hqi
(
  "INSTITUT" character varying(150) NOT NULL,
  "CODE" character varying(100) NOT NULL,
  "VERSION" character varying(50) NOT NULL,
  "FEATURE_NAME" character varying(100) NOT NULL,
  "ENC_MET" character varying(50),
  "PERSON" character varying(150),
  "CONTACT" character varying(150) NOT NULL,
  "REFERENCE" character varying(150),
  "OBSERVAT" character varying(255) NOT NULL,
  "INSTRUME" character varying(150) NOT NULL,
  "TELESCOP" character varying(150),
  "OBSINST_KEY" character varying(150),
  "UNITS" character varying(100) NOT NULL,
  "WAVEMIN" real,
  "WAVEMAX" real,
  "WAVENAME" character varying(50),
  "WAVEUNIT" character varying(10),
  "FREQMIN" real,
  "FREQMAX" real,
  "SPECTRAL_DOMAIN" character varying(100),
  "OBS_TYPE" character varying(50),
  "OBS_CAT" character varying(50),
  "DATE_OBS" timestamp without time zone NOT NULL,
  "DATE_END" timestamp without time zone NOT NULL,
  "JDINT" integer,
  "JDFRAC" double precision,
  "C_ROTATION" integer,
  "FILENAME" character varying(100) NOT NULL,
  "URL" text,
  "CDELT1" double precision NOT NULL,
  "CDELT2" double precision NOT NULL,
  "NAXIS1" integer,
  "NAXIS2" integer,
  "CENTER_X" double precision,
  "CENTER_Y" double precision,
  "FILE_FORMAT" character varying(20),
  "DATA_TYPE" character varying(50),
  "QCLK_URL" text,
  "QCLK_FNAME" character varying(200),
  "FEAT_MAX_INT" float,
  "FEAT_MEAN_INT" float,
  "MULTIPLE" float,
  "FEAT_FILENAME" character varying(150) NOT NULL,
  "SNAPSHOT" character varying(400),
  "CC_X_PIX" integer NOT NULL,
  "CC_Y_PIX" integer NOT NULL,
  "CC_X_UTC" double precision NOT NULL,
  "CC_Y_MHZ" double precision NOT NULL,
  "CC" text NOT NULL,
  "CC_LENGTH" integer,
  "SKE_CC_X_PIX" integer NOT NULL,
  "SKE_CC_Y_PIX" integer NOT NULL,
  "SKE_CC_X_UTC" double precision NOT NULL,
  "SKE_CC_Y_UTC" double precision NOT NULL,
  "SKE_CC" text NOT NULL,
  "SKE_CC_LENGTH" integer NOT NULL,
  "BR_X0_PIX" integer,
  "BR_Y0_PIX" integer,
  "BR_X1_PIX" integer,
  "BR_Y1_PIX" integer,
  "BR_X2_PIX" integer,
  "BR_Y2_PIX" integer,
  "BR_X3_PIX" integer,
  "BR_Y3_PIX" integer,
  "BR_X0_UTC" double precision NOT NULL,
  "BR_Y0_MHZ" double precision NOT NULL,
  "BR_X1_UTC" double precision NOT NULL,
  "BR_Y1_MHZ" double precision NOT NULL,
  "BR_X2_UTC" double precision NOT NULL,
  "BR_Y2_MHZ" double precision NOT NULL,
  "BR_X3_UTC" double precision NOT NULL,
  "BR_Y3_MHZ" double precision NOT NULL,
  "TIME_START" timestamp without time zone NOT NULL,
  "TIME_END" timestamp without time zone NOT NULL,
  "DRIFT_START" real NOT NULL,
  "DRIFT_END" real NOT NULL,
  "FIT_A0" real NOT NULL,
  "FIT_A1" real NOT NULL,
  "LVL_TRUST" integer,
)
SERVER mysql_svr
options (query 'select 
INSTITUT,
CODE,
VERSION,
FEATURE_NAME,
ENC_MET,
PERSON,
CONTACT,
REFERENCE,
OBSERVAT,
INSTRUME,
TELESCOP,
OBSINST_KEY,
UNITS,
WAVEMIN,
WAVEMAX,
WAVENAME,
WAVEUNIT,
FREQMIN,
FREQMAX,
SPECTRAL_DOMAIN,
OBS_TYPE,
OBS_CAT,
DATE_OBS,
DATE_END,
JDINT,
JDFRAC,
C_ROTATION,
FILENAME,
URL,
CDELT1,
CDELT2,
NAXIS1,
NAXIS2,
CENTER_X,
CENTER_Y,
FILE_FORMAT,
DATA_TYPE,
QCLK_URL,
QCLK_FNAME,
FEAT_MAX_INT,
FEAT_MEAN_INT,
MULTIPLE,
FEAT_FILENAME,
SNAPSHOT,
CC_X_PIX,
CC_Y_PIX,
CC_X_PIX,
CC_Y_PIX,
CC_X_UTC,
CC_Y_MHZ,
CC,
CC_LENGTH,
SKE_CC_X_PIX,
SKE_CC_Y_PIX,
SKE_CC_X_UTC,
SKE_CC_Y_UTC,
SKE_CC,
SKE_CC_LENGTH,
BR_X0_PIX,
BR_Y0_PIX,
BR_X1_PIX,
BR_Y1_PIX,
BR_X2_PIX,
BR_Y2_PIX,
BR_X3_PIX,
BR_Y3_PIX,
BR_X0_ARCSEC,
BR_Y0_ARCSEC,
BR_X1_ARCSEC,
BR_Y1_ARCSEC,
BR_X2_ARCSEC,
BR_Y2_ARCSEC,
BR_X3_ARCSEC,
BR_Y3_ARCSEC,
BR_X0_UTC,
BR_Y0_MHZ,
BR_X1_UTC,
BR_Y1_MHZ,
BR_X2_UTC,
BR_Y2_MHZ,
BR_X3_UTC,
BR_Y3_MHZ,
TIME_START,
TIME_END,
DRIFT_START,
DRIFT_END,
FIT_A0,
FIT_A1,
LVL_TRUST
 from helio.VIEW_T3_HQI LIMIT 10');

-- sequence pour l'increment de index
drop sequence epn_core_seq;
create sequence epn_core_seq;



-- drop view helio.epn_core;
drop sequence epn_core_seq2;
create sequence epn_core_seq2;
CREATE MATERIALIZED VIEW heliot3.epn_core AS SELECT
	TEXT 'granule'								AS resource_type,
	TEXT '1' 								AS dataset_id,
--	ROW_NUMBER() OVER (ORDER BY resource_type) AS index2,
	nextval('epn_core_seq'::regclass)  					AS index,
--	generate_series(1, select count(*) from view_ar_hqi) 			AS index,
	CASE "DATA_TYPE"
		WHEN 'IMAGE'  THEN CAST('im' AS TEXT) 
		WHEN 'SPECTRUM'  THEN CAST('ds' AS TEXT)
		WHEN 'DYNAMIC SPECTRUM'  THEN CAST('ds' AS TEXT) 
		ELSE CAST(NULL AS TEXT) END
										AS  dataproduct_type,
	TEXT 'sun'								AS target_name,
	TEXT 'star'								AS target_class,
	cast(to_char("DATE_OBS"::timestamp at time zone 'UTC','J') as double precision) + EXTRACT(EPOCH FROM "DATE_OBS"::time)/86400 -0.5 AS time_min,
	cast(to_char("DATE_END"::timestamp at time zone 'UTC','J') as double precision) + EXTRACT(EPOCH FROM "DATE_END"::time)/86400 -0.5 AS time_max,
--	CAST(to_char(to_timestamp(a."DATE_OBS", 'YYYY-MM-DD HH:MI:SS')::date,'J') AS double precision) AS time_min,
--	CAST(to_char(to_timestamp(a."DATE_END", 'YYYY-MM-DD HH:MI:SS')::date,'J') AS double precision) AS time_max,
	CAST(NULL AS DOUBLE PRECISION)						AS time_sampling_step_min,
	CAST(NULL AS DOUBLE PRECISION)						AS time_sampling_step_max,
	"EXP_TIME"								AS time_exp_min,
	"EXP_TIME"								AS time_exp_max,
	CAST((299792458E9/"WAVEMAX") AS DOUBLE PRECISION)			AS spectral_range_min,
	CAST((299792458E9/"WAVEMIN") AS DOUBLE PRECISION)			AS spectral_range_max,
	CAST(NULL AS DOUBLE PRECISION)						AS spectral_sampling_step_min, 
	CAST(NULL AS DOUBLE PRECISION)						AS spectral_sampling_step_max,
	CAST(NULL AS DOUBLE PRECISION)						AS spectral_resolution_min,
	CAST(NULL AS DOUBLE PRECISION)						AS spectral_resolution_max,
	CAST(NULL AS DOUBLE PRECISION)			AS c1min,
	CAST(NULL AS DOUBLE PRECISION)			AS c1max,
	CAST(NULL AS DOUBLE PRECISION)			AS c2min,
	CAST(NULL AS DOUBLE PRECISION)			AS c2max,
	CAST(NULL AS DOUBLE PRECISION)						AS c3min,
	CAST(NULL AS DOUBLE PRECISION)						AS c3max,
	CAST(NULL AS DOUBLE PRECISION)						AS c1_resol_min,
	CAST(NULL AS DOUBLE PRECISION)						AS c1_resol_max,
	CAST(NULL AS DOUBLE PRECISION)						AS c2_resol_min,
	CAST(NULL AS DOUBLE PRECISION)						AS c2_resol_max,
	CAST(NULL AS DOUBLE PRECISION)						AS c3_resol_min,
	CAST(NULL AS DOUBLE PRECISION)						AS c3_resol_max,
	TEXT 'body'								AS spatial_frame_type,
	CAST(NULL AS DOUBLE PRECISION) as incidence_min,
	CAST(NULL AS DOUBLE PRECISION) as incidence_max,
	CAST(NULL AS DOUBLE PRECISION) as emergence_min,
	CAST(NULL AS DOUBLE PRECISION) as emergence_max,
	CAST(NULL AS DOUBLE PRECISION) as phase_min,
	CAST(NULL AS DOUBLE PRECISION) as phase_max,
	"OBSERVAT"								AS instrument_host_name,
	"INSTRUME"								AS instrument_name,
	TEXT 'phys.polarization.circular, phys.veloc.orbital;pos.heliocentric, spect.dopplerVeloc, spect.line.width, arith.rate;phys.mass.loss'							AS measurement_type,
	"URL" 									AS access_url,
	TEXT 'fits'								AS access_format,
	INTEGER '11'								AS access_estsize,
	integer '3'								AS processing_level,
	"INSTITUT"								AS publisher,
	"REFERENCE"								AS reference,
	TEXT 'Helio active region'					 	AS service_title,
	TEXT 'comet'								AS target_region,
	"SNAPSHOT"								AS preview_url,
	"INSTITUT"	AS	INSTITUT,
	"CODE"	AS	CODE,
	"VERSION"	AS	VERSION,
	"FEATURE_NAME"	AS	FEATURE_NAME,
	"ENC_MET"	AS	ENC_MET,
	"PERSON"	AS	PERSON,
	"CONTACT"	AS	CONTACT,
	"OBSERVAT"	AS	OBSERVAT,
	"INSTRUME"	AS	INSTRUME,
	"TELESCOP"	AS	TELESCOP,
	"OBSINST_KEY"	AS	OBSINST_KEY,
	"UNITS"	AS	UNITS,
	"WAVEMIN"		AS	WAVEMIN,
	"WAVEMAX"	AS	WAVEMAX,
	"WAVENAME"	AS	WAVENAME,
	"WAVEUNIT"	AS	WAVEUNIT,
	"SPECTRAL_DOMAIN"	AS	SPECTRAL_DOMAIN,
"OBS_TYPE"	AS	OBS_TYPE,
"OBS_CAT"	AS	OBS_CAT,
"DATE_OBS"	AS	DATE_OBS,
"DATE_END"	AS	DATE_END,
"JDINT"	AS	JDINT,
"JDFRAC"	AS	JDFRAC,
"C_ROTATION"	AS	C_ROTATION,
"FILENAME"	AS	FILENAME,
"URL"	AS	URL,
"CDELT1"	AS	CDELT1,
"CDELT2"	AS	CDELT2,
"NAXIS1"	AS	NAXIS1,
"NAXIS2"	AS	NAXIS2,
"CENTER_X"	AS	CENTER_X,
"CENTER_Y"	AS	CENTER_Y,
"R_SUN"	AS	R_SUN,
"BITPIX"	AS	BITPIX,
"BSCALE"	AS	BSCALE,
"BZERO"	AS	BZERO,
"EXP_TIME"	AS	EXP_TIME,
"FILE_FORMAT"	AS	FILE_FORMAT,
"DATA_TYPE"	AS	DATA_TYPE,
"QCLK_URL"	AS	QCLK_URL,
"QCLK_FNAME"	AS	QCLK_FNAME,
"ID_AR"	AS	ID_AR,
"NOAA_NUMBER"	AS	NOAA_NUMBER,
"FEAT_X_PIX"	AS	FEAT_X_PIX,
"FEAT_Y_PIX"	AS	FEAT_Y_PIX,
"FEAT_X_ARCSEC"	AS	FEAT_X_ARCSEC,
"FEAT_Y_ARCSEC"	AS	FEAT_Y_ARCSEC,
"FEAT_HG_LONG_DEG"	AS	FEAT_HG_LONG_DEG,
"FEAT_HG_LAT_DEG"	AS	FEAT_HG_LAT_DEG,
"FEAT_CARR_LONG_DEG"	AS	FEAT_CARR_LONG_DEG,
"FEAT_CARR_LAT_DEG"	AS	FEAT_CARR_LAT_DEG,
"BR_X0_PIX"	AS	BR_X0_PIX,
"BR_Y0_PIX"	AS	BR_Y0_PIX,
"BR_X1_PIX"	AS	BR_X1_PIX,
"BR_Y1_PIX"	AS	BR_Y1_PIX,
"BR_X2_PIX"	AS	BR_X2_PIX,
"BR_Y2_PIX"	AS	BR_Y2_PIX,
"BR_X3_PIX"	AS	BR_X3_PIX,
"BR_Y3_PIX"	AS	BR_Y3_PIX,
"BR_X0_ARCSEC"	AS	BR_X0_ARCSEC,
"BR_Y0_ARCSEC"	AS	BR_Y0_ARCSEC,
"BR_X1_ARCSEC"	AS	BR_X1_ARCSEC,
"BR_Y1_ARCSEC"	AS	BR_Y1_ARCSEC,
"BR_X2_ARCSEC"	AS	BR_X2_ARCSEC,
"BR_Y2_ARCSEC"	AS	BR_Y2_ARCSEC,
"BR_X3_ARCSEC"	AS	BR_X3_ARCSEC,
"BR_Y3_ARCSEC"	AS	BR_Y3_ARCSEC,
"BR_HG_LONG0_DEG"	AS	BR_HG_LONG0_DEG,
"BR_HG_LAT0_DEG"	AS	BR_HG_LAT0_DEG,
"BR_HG_LONG1_DEG"	AS	BR_HG_LONG1_DEG,
"BR_HG_LAT1_DEG"	AS	BR_HG_LAT1_DEG,
"BR_HG_LONG2_DEG"	AS	BR_HG_LONG2_DEG,
"BR_HG_LAT2_DEG"	AS	BR_HG_LAT2_DEG,
"BR_HG_LONG3_DEG"	AS	BR_HG_LONG3_DEG,
"BR_HG_LAT3_DEG"	AS	BR_HG_LAT3_DEG,
"BR_CARR_LONG0_DEG"	AS	BR_CARR_LONG0_DEG,
"BR_CARR_LAT0_DEG"	AS	BR_CARR_LAT0_DEG,
"BR_CARR_LONG1_DEG"	AS	BR_CARR_LONG1_DEG,
"BR_CARR_LAT1_DEG"	AS	BR_CARR_LAT1_DEG,
"BR_CARR_LONG2_DEG"	AS	BR_CARR_LONG2_DEG,
"BR_CARR_LAT2_DEG"	AS	BR_CARR_LAT2_DEG,
"BR_CARR_LONG3_DEG"	AS	BR_CARR_LONG3_DEG,
"BR_CARR_LAT3_DEG"	AS	BR_CARR_LAT3_DEG,
"FEAT_AREA_PIX"	AS	FEAT_AREA_PIX,
"FEAT_AREA_DEG2"	AS	FEAT_AREA_DEG2,
"FEAT_AREA_MM2"	AS	FEAT_AREA_MM2,
"FEAT_MAX_INT"	AS	FEAT_MAX_INT,
"FEAT_MIN_INT"	AS	FEAT_MIN_INT,
"FEAT_MEAN_INT"	AS	FEAT_MEAN_INT,
"FEAT_MEAN2QSUN"	AS	FEAT_MEAN2QSUN,
"FEAT_MIN_BZ"	AS	FEAT_MIN_BZ,
"FEAT_MAX_BZ"	AS	FEAT_MAX_BZ,
"FEAT_MEAN_BZ"	AS	FEAT_MEAN_BZ,
"FEAT_MEAN_GRAD"	AS	FEAT_MEAN_GRAD,
"FEAT_MEDIAN_GRAD"	AS	FEAT_MEDIAN_GRAD,
"FEAT_MAX_GRAD"	AS	FEAT_MAX_GRAD,
"FEAT_LENGTH_NL"	AS	FEAT_LENGTH_NL,
"FEAT_LENGTH_SG"	AS	FEAT_LENGTH_SG,
"CC_X_PIX"	AS	CC_X_PIX,
"CC_Y_PIX"	AS	CC_Y_PIX,
"CC_X_ARCSEC"	AS	CC_X_ARCSEC,
"CC_Y_ARCSEC"	AS	CC_Y_ARCSEC,
"CC"	AS	CC,
"CC_LENGTH"	AS	CC_LENGTH,
"SNAPSHOT"	AS	SNAPSHOT,
"TRACK_ID"	AS	TRACK_ID,
"PHENOM"	AS	PHENOM,
"REF_FEAT"	AS	REF_FEAT,
"TRACK_LVL_TRUST"	AS	TRACK_LVL_TRUST
	from helio.view_ar_hqi
	UNION ALL
	SELECT
	TEXT 'dataset'					AS resource_type,
	TEXT '1'					AS dataset_id,
	INTEGER '-1'   					AS index,
--	generate_series(1, select count(*) from view_ar_hqi) 			AS index,
	array_to_string(array(select distinct CASE "DATA_TYPE" 
		WHEN 'IMAGE'  THEN CAST('im' AS TEXT) 
		WHEN 'SPECTRUM'  THEN CAST('ds' AS TEXT)
		WHEN 'DYNAMIC SPECTRUM'  THEN CAST('ds' AS TEXT) 
		ELSE CAST(NULL AS TEXT) END
	       	from helio.view_ar_hqi),' ')
										AS  dataproduct_type,
	TEXT 'sun'								AS target_name,
	TEXT 'star'								AS target_class,
    min(CAST(to_char("DATE_OBS"::timestamp at time zone 'UTC','J') as double precision) + EXTRACT(EPOCH FROM "DATE_OBS"::time)/86400 -0.5) AS time_min,
    max(cast(to_char( "DATE_END"::timestamp at time zone 'UTC','J') as double precision) + EXTRACT(EPOCH FROM "DATE_END"::time)/86400 -0.5) AS time_max,
--	CAST(to_char(to_timestamp(a."DATE_OBS", 'YYYY-MM-DD HH:MI:SS')::date,'J') AS double precision) AS time_min,
--	CAST(to_char(to_timestamp(a."DATE_END", 'YYYY-MM-DD HH:MI:SS')::date,'J') AS double precision) AS time_max,
	CAST(NULL AS DOUBLE PRECISION)						AS time_sampling_step_min,
	CAST(NULL AS DOUBLE PRECISION)						AS time_sampling_step_max,
	min("EXP_TIME")								AS time_exp_min,
	max("EXP_TIME")								AS time_exp_max,
	min(CAST((299792458E9/"WAVEMAX") AS DOUBLE PRECISION))			AS spectral_range_min,
	max(CAST((299792458E9/"WAVEMIN") AS DOUBLE PRECISION))			AS spectral_range_max,
	CAST(NULL AS DOUBLE PRECISION)						AS spectral_sampling_step_min, 
	CAST(NULL AS DOUBLE PRECISION)						AS spectral_sampling_step_max,
	CAST(NULL AS DOUBLE PRECISION)						AS spectral_resolution_min,
	CAST(NULL AS DOUBLE PRECISION)						AS spectral_resolution_max,
	min(LEAST(CAST("BR_HG_LONG0_DEG" AS DOUBLE PRECISION),CAST("BR_HG_LONG1_DEG" AS DOUBLE PRECISION))) 				AS c1min,
	max(GREATEST(CAST("BR_HG_LAT0_DEG" AS DOUBLE PRECISION),CAST("BR_HG_LAT1_DEG" AS DOUBLE PRECISION)))				AS c1max,
	min(LEAST(CAST("BR_HG_LONG2_DEG" AS DOUBLE PRECISION),CAST("BR_HG_LONG3_DEG" AS DOUBLE PRECISION)))				AS c2min,
	max(GREATEST(CAST("BR_HG_LAT2_DEG" AS DOUBLE PRECISION),CAST("BR_HG_LAT3_DEG" AS DOUBLE PRECISION)))				AS c2max,
	CAST(NULL AS DOUBLE PRECISION)						AS c3min,
	CAST(NULL AS DOUBLE PRECISION)						AS c3max,
	CAST(NULL AS DOUBLE PRECISION)						AS c1_resol_min,
	CAST(NULL AS DOUBLE PRECISION)						AS c1_resol_max,
	CAST(NULL AS DOUBLE PRECISION)						AS c2_resol_min,
	CAST(NULL AS DOUBLE PRECISION)						AS c2_resol_max,
	CAST(NULL AS DOUBLE PRECISION)						AS c3_resol_min,
	CAST(NULL AS DOUBLE PRECISION)						AS c3_resol_max,
	TEXT 'body'								AS spatial_frame_type,
	CAST(NULL AS DOUBLE PRECISION) as incidence_min,
	CAST(NULL AS DOUBLE PRECISION) as incidence_max,
	CAST(NULL AS DOUBLE PRECISION) as emergence_min,
	CAST(NULL AS DOUBLE PRECISION) as emergence_max,
	CAST(NULL AS DOUBLE PRECISION) as phase_min,
	CAST(NULL AS DOUBLE PRECISION) as phase_max,
	array_to_string(array(select distinct "OBSERVAT"  from helio.view_ar_hqi),' ')								AS instrument_host_name,
	array_to_string(array(select distinct "INSTRUME"  from helio.view_ar_hqi),' ')								AS instrument_name,
	TEXT 'phys.polarization.circular, phys.veloc.orbital;pos.heliocentric, spect.dopplerVeloc, spect.line.width, arith.rate;phys.mass.loss'							AS measurement_type,
	CAST(NULL AS TEXT)	access_url,
	TEXT 'image/fits'								AS access_format,
	INTEGER '11'								AS access_estsize,
	integer '3'								AS processing_level,
	array_to_string(array(select distinct "INSTITUT"  from helio.view_ar_hqi),' ')			AS publisher,
	array_to_string(array(select distinct "REFERENCE" from helio.view_ar_hqi),' ')			AS reference,
	TEXT 'Helio active region'					 	AS service_title,
	TEXT 'sun'								AS target_region,
	CAST(NULL AS TEXT) AS preview_url,
	CAST(NULL AS TEXT)	AS	INSTITUT,
	CAST(NULL AS TEXT)	AS	CODE,
	CAST(NULL AS TEXT)	AS	VERSION,
	CAST(NULL AS TEXT)	AS	FEATURE_NAME,
	CAST(NULL AS TEXT)	AS	ENC_MET,
	CAST(NULL AS TEXT)	AS	PERSON,
	CAST(NULL AS TEXT)	AS	CONTACT,
	CAST(NULL AS TEXT)	AS	OBSERVAT,
	CAST(NULL AS TEXT)	AS	INSTRUME,
	CAST(NULL AS TEXT)	AS	TELESCOP,
	CAST(NULL AS TEXT)	AS	OBSINST_KEY,
	CAST(NULL AS TEXT)	AS	UNITS,
	CAST(NULL AS REAL)	AS	WAVEMIN,
	CAST(NULL AS REAL)	AS	WAVEMAX,
	CAST(NULL AS TEXT)	AS	WAVENAME,
	CAST(NULL AS TEXT)	AS	WAVEUNIT,
	CAST(NULL AS TEXT)	AS	SPECTRAL_DOMAIN,
CAST(NULL AS TEXT)	AS	OBS_TYPE,
CAST(NULL AS TEXT)	AS	OBS_CAT,
CAST(NULL AS timestamp without time zone)	AS	DATE_OBS,
CAST(NULL AS timestamp without time zone)	AS	DATE_END,
CAST(NULL AS INTEGER)	AS	JDINT,
CAST(NULL AS DOUBLE PRECISION)	AS	JDFRAC,
CAST(NULL AS INTEGER)	AS	C_ROTATION,
CAST(NULL AS TEXT)	AS	FILENAME,
CAST(NULL AS TEXT)	AS	URL,
CAST(NULL AS DOUBLE PRECISION)	AS	CDELT1,
CAST(NULL AS DOUBLE PRECISION)	AS	CDELT2,
CAST(NULL AS INTEGER)	AS	NAXIS1,
CAST(NULL AS INTEGER)	AS	NAXIS2,
CAST(NULL AS DOUBLE PRECISION) 	AS	CENTER_X,
CAST(NULL AS DOUBLE PRECISION) 	AS	CENTER_Y,
CAST(NULL AS DOUBLE PRECISION) 	AS	R_SUN,
CAST(NULL AS INTEGER)	AS	BITPIX,
CAST(NULL AS DOUBLE PRECISION)	AS	BSCALE,
CAST(NULL AS DOUBLE PRECISION)	AS	BZERO,
CAST(NULL AS REAL)	AS	EXP_TIME,
CAST(NULL AS TEXT)	AS	FILE_FORMAT,
CAST(NULL AS TEXT)	AS	DATA_TYPE,
CAST(NULL AS TEXT)	AS	QCLK_URL,
CAST(NULL AS TEXT)	AS	QCLK_FNAME,
CAST(NULL AS INTEGER)	AS	ID_AR,
CAST(NULL AS INTEGER)	AS	NOAA_NUMBER,
CAST(NULL AS INTEGER)	AS	FEAT_X_PIX,
CAST(NULL AS INTEGER)	AS	FEAT_Y_PIX,
CAST(NULL AS DOUBLE PRECISION)	AS	FEAT_X_ARCSEC,
CAST(NULL AS DOUBLE PRECISION)	AS	FEAT_Y_ARCSEC,
CAST(NULL AS REAL)	AS	FEAT_HG_LONG_DEG,
CAST(NULL AS REAL)	AS	FEAT_HG_LAT_DEG,
CAST(NULL AS REAL)	AS	FEAT_CARR_LONG_DEG,
CAST(NULL AS REAL)	AS	FEAT_CARR_LAT_DEG,
CAST(NULL AS INTEGER)	AS	BR_X0_PIX,
CAST(NULL AS INTEGER)	AS	BR_Y0_PIX,
CAST(NULL AS INTEGER)	AS	BR_X1_PIX,
CAST(NULL AS INTEGER)	AS	BR_Y1_PIX,
CAST(NULL AS INTEGER)	AS	BR_X2_PIX,
CAST(NULL AS INTEGER)	AS	BR_Y2_PIX,
CAST(NULL AS INTEGER)	AS	BR_X3_PIX,
CAST(NULL AS INTEGER)	AS	BR_Y3_PIX,
CAST(NULL AS DOUBLE PRECISION)	AS	BR_X0_ARCSEC,
CAST(NULL AS DOUBLE PRECISION)	AS	BR_Y0_ARCSEC,
CAST(NULL AS DOUBLE PRECISION)	AS	BR_X1_ARCSEC,
CAST(NULL AS DOUBLE PRECISION)	AS	BR_Y1_ARCSEC,
CAST(NULL AS DOUBLE PRECISION)	AS	BR_X2_ARCSEC,
CAST(NULL AS DOUBLE PRECISION)	AS	BR_Y2_ARCSEC,
CAST(NULL AS DOUBLE PRECISION)	AS	BR_X3_ARCSEC,
CAST(NULL AS DOUBLE PRECISION)	AS	BR_Y3_ARCSEC,
CAST(NULL AS REAL)	AS	BR_HG_LONG0_DEG,
CAST(NULL AS REAL)	AS	BR_HG_LAT0_DEG,
CAST(NULL AS REAL)	AS	BR_HG_LONG1_DEG,
CAST(NULL AS REAL)	AS	BR_HG_LAT1_DEG,
CAST(NULL AS REAL)	AS	BR_HG_LONG2_DEG,
CAST(NULL AS REAL)	AS	BR_HG_LAT2_DEG,
CAST(NULL AS REAL)	AS	BR_HG_LONG3_DEG,
CAST(NULL AS REAL)	AS	BR_HG_LAT3_DEG,
CAST(NULL AS REAL)	AS	BR_CARR_LONG0_DEG,
CAST(NULL AS REAL)	AS	BR_CARR_LAT0_DEG,
CAST(NULL AS REAL)	AS	BR_CARR_LONG1_DEG,
CAST(NULL AS REAL)	AS	BR_CARR_LAT1_DEG,
CAST(NULL AS REAL)	AS	BR_CARR_LONG2_DEG,
CAST(NULL AS REAL)	AS	BR_CARR_LAT2_DEG,
CAST(NULL AS REAL)	AS	BR_CARR_LONG3_DEG,
CAST(NULL AS REAL)	AS	BR_CARR_LAT3_DEG,
CAST(NULL AS INTEGER)	AS	FEAT_AREA_PIX,
CAST(NULL AS REAL)	AS	FEAT_AREA_DEG2,
CAST(NULL AS REAL)	AS	FEAT_AREA_MM2,
CAST(NULL AS REAL)	AS	FEAT_MAX_INT,
CAST(NULL AS REAL)	AS	FEAT_MIN_INT,
CAST(NULL AS REAL)	AS	FEAT_MEAN_INT,
CAST(NULL AS DOUBLE PRECISION)	AS	FEAT_MEAN2QSUN,
CAST(NULL AS REAL)	AS	FEAT_MIN_BZ,
CAST(NULL AS REAL)	AS	FEAT_MAX_BZ,
CAST(NULL AS REAL)	AS	FEAT_MEAN_BZ,
CAST(NULL AS REAL)	AS	FEAT_MEAN_GRAD,
CAST(NULL AS REAL)	AS	FEAT_MEDIAN_GRAD,
CAST(NULL AS REAL)	AS	FEAT_MAX_GRAD,
CAST(NULL AS REAL)	AS	FEAT_LENGTH_NL,
CAST(NULL AS REAL)	AS	FEAT_LENGTH_SG,
CAST(NULL AS INTEGER)	AS	CC_X_PIX,
CAST(NULL AS INTEGER)	AS	CC_Y_PIX,
CAST(NULL AS DOUBLE PRECISION)	AS	CC_X_ARCSEC,
CAST(NULL AS DOUBLE PRECISION)	AS	CC_Y_ARCSEC,
CAST(NULL AS TEXT)	AS	CC,
CAST(NULL AS INTEGER)	AS	CC_LENGTH,
CAST(NULL AS TEXT)	AS	SNAPSHOT,
CAST(NULL AS INTEGER)	AS	TRACK_ID,
CAST(NULL AS INTEGER)	AS	PHENOM,
CAST(NULL AS INTEGER)	AS	REF_FEAT,
CAST(NULL AS INTEGER)	AS	TRACK_LVL_TRUST
from helio.view_ar_hqi;

-- on passe de 1mn à 8s avec materialized view
-- drop table l'ancienne
-- alter table helio.epn_core2 rename to epn_core3;
-- alter view helio.epn_core alter column index set default 0;


 GRANT ALL PRIVILEGES ON SCHEMA helio TO gavo WITH GRANT OPTION;
 GRANT ALL PRIVILEGES ON SCHEMA helio TO gavoadmin WITH GRANT OPTION;
 GRANT ALL PRIVILEGES ON helio.epn_core to gavoadmin WITH GRANT OPTION;
 GRANT ALL PRIVILEGES ON helio.epn_core to gavo WITH GRANT OPTION;


-- ALTER TABLE "VIEW_AR_HQI"
--  OWNER TO pierre;
