<commit_before>﻿/*
Setup simple feedin weather measurement point

__copyright__ 	= "Europa-Universität Flensburg, Centre for Sustainable Energy Systems"
__license__ 	= "GNU Affero General Public License Version 3 (AGPL-3.0)"
__url__ 	    = "https://github.com/openego/data_processing/blob/master/LICENSE"
__author__ 	    = "wolfbunke"
*/


-- DROP TABLE calc_renpass_gis.coastdat_spatial CASCADE;
CREATE TABLE calc_renpass_gis.coastdat_spatial 
(
  gid serial NOT NULL,
  geom geometry(Point,4326),
  CONSTRAINT spatial_pkey PRIMARY KEY (gid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE calc_renpass_gis.cosmoclmgrid
  OWNER TO oeuser;

-- Index: calc_renpass_gis.coastdat_spatial

-- DROP INDEX calc_renpass_gis.coastdat_spatial_geom_gist;
CREATE INDEX cosmoclmgrid_geom_gist
  ON calc_renpass_gis.coastdat_spatial
  USING gist
  (geom);

-- get data 
--INSERT INTO calc_renpass_gis.coastdat_spatial
--SELECT *
--FROM coastdat.spatial;


CREATE TABLE model_draft.ego_simple_feedin_full
(
  hour bigint NOT NULL,
  coastdat_id text NOT NULL,
  sub_id text NOT NULL,
  generation_type text NOT NULL,
  feedin text,
  CONSTRAINT ego_simple_feedin_test_pkey PRIMARY KEY (hour, coastdat_id, sub_id, generation_type)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE model_draft.ego_simple_feedin_full
  OWNER TO oeuser;

-- DROP TABLE model_draft.ego_weather_measurement_point;

CREATE TABLE model_draft.ego_weather_measurement_point
(
  name text NOT NULL,
  type_of_generation text NOT NULL,
  comment text,
  source text,
  scenario text NOT NULL,
  capacity_scale numeric,
  geom geometry(Point,4326),
  CONSTRAINT weather_measurement_point_pkey PRIMARY KEY (name, type_of_generation, scenario)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE model_draft.ego_weather_measurement_point
  OWNER TO oeuser;

-- DROP INDEX model_draft.ego_weather_measurement_point_geom_gist;
CREATE INDEX ego_weather_measurement_point_geom_gist
  ON model_draft.ego_weather_measurement_point
  USING gist
  (geom);




/*

﻿-- Count duplicates
SELECT 	hour,
	coastdat_id,
	sub_id,
	generation_type, 
	feedin,
	count(*) 
FROM model_draft.ego_simple_feedin_full
GROUP BY hour,coastdat_id,sub_id,generation_type,feedin
HAVING ( COUNT(*) > 1 );


SELECT
  coastdat_id,
  sub_id,
  generation_type,<commit_msg>chage dtypes of  ego_simple_feedin_full<commit_after>﻿/*
Setup simple feedin weather measurement point

__copyright__ 	= "Europa-Universität Flensburg, Centre for Sustainable Energy Systems"
__license__ 	= "GNU Affero General Public License Version 3 (AGPL-3.0)"
__url__ 	    = "https://github.com/openego/data_processing/blob/master/LICENSE"
__author__ 	    = "wolfbunke"
*/


-- DROP TABLE calc_renpass_gis.coastdat_spatial CASCADE;
CREATE TABLE calc_renpass_gis.coastdat_spatial 
(
  gid serial NOT NULL,
  geom geometry(Point,4326),
  CONSTRAINT spatial_pkey PRIMARY KEY (gid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE calc_renpass_gis.cosmoclmgrid
  OWNER TO oeuser;

-- Index: calc_renpass_gis.coastdat_spatial

-- DROP INDEX calc_renpass_gis.coastdat_spatial_geom_gist;
CREATE INDEX cosmoclmgrid_geom_gist
  ON calc_renpass_gis.coastdat_spatial
  USING gist
  (geom);

-- get data 
--INSERT INTO calc_renpass_gis.coastdat_spatial
--SELECT *
--FROM coastdat.spatial;

-- Table: model_draft.ego_simple_feedin_full
-- DROP TABLE model_draft.ego_simple_feedin_full;

CREATE TABLE model_draft.ego_simple_feedin_full
(
  hour text NOT NULL,
  coastdat_id text bigint,
  sub_id text bigint,
  generation_type text NOT NULL,
  feedin text,
  scenario text NOT NULL,
  CONSTRAINT ego_simple_feedin_full_pkey PRIMARY KEY (scenario, coastdat_id, sub_id, generation_type, hour)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE model_draft.ego_simple_feedin_full
  OWNER TO oeuser;

-- Index: model_draft.ego_simple_feedin_full_idx
-- DROP INDEX model_draft.ego_simple_feedin_full_idx;

CREATE INDEX ego_simple_feedin_full_idx
  ON model_draft.ego_simple_feedin_full
  USING btree
  (scenario COLLATE pg_catalog."default", sub_id COLLATE pg_catalog."default");

--
/*
ALTER TABLE  model_draft.ego_simple_feedin_full
  ALTER COLUMN coastdat_id TYPE bigint USING coastdat_id::bigint;
  
ALTER TABLE  model_draft.ego_simple_feedin_full
  ALTER COLUMN sub_id TYPE bigint USING sub_id::bigint;
  
ALTER TABLE  model_draft.ego_simple_feedin_full
  ALTER COLUMN feedin TYPE numeric(23,20) USING feedin::numeric(23,20);
  
*/


-- DROP TABLE model_draft.ego_weather_measurement_point;

CREATE TABLE model_draft.ego_weather_measurement_point
(
  name text NOT NULL,
  type_of_generation text NOT NULL,
  comment text,
  source text,
  scenario text NOT NULL,
  capacity_scale numeric,
  geom geometry(Point,4326),
  CONSTRAINT weather_measurement_point_pkey PRIMARY KEY (name, type_of_generation, scenario)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE model_draft.ego_weather_measurement_point
  OWNER TO oeuser;

-- DROP INDEX model_draft.ego_weather_measurement_point_geom_gist;
CREATE INDEX ego_weather_measurement_point_geom_gist
  ON model_draft.ego_weather_measurement_point
  USING gist
  (geom);

/*

﻿-- Count duplicates
SELECT 	hour,
	coastdat_id,
	sub_id,
	generation_type, 
	feedin,
	count(*) 
FROM model_draft.ego_simple_feedin_full
GROUP BY hour,coastdat_id,sub_id,generation_type,feedin
HAVING ( COUNT(*) > 1 );


SELECT
  coastdat_id,
  sub_id,
  generation_type,
{'fraction_non_alphanumeric': 0.07084829019459664, 'fraction_numerical': 0.006045720763272247, 'mean_word_length': 5.2209165687426555, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '530717', 'n_tokens_mistral': 2054, 'n_tokens_neox': 1957, 'n_words': 428}