ISPNeutral.dat

# NEM ISP template scenario
set all_tech :=
[alltech];
# Set of generator technologies available in each zone
set gen_tech_in_zones :=
[gentech];

# Set of generator technologies that can be retired by the model
set retire_gen_tech_in_zones :=
[retiretech];

# Set of emitting/fuel generator technologies
set fuel_gen_tech_in_zones :=
[fueltech];

# Set of emitting/fuel generator technologies
set commit_gen_tech_in_zones :=
[committech];

# Set of renewable energy generator technologies
set re_gen_tech_in_zones :=
[regentech];

# Set of dispatchable generators
set disp_gen_tech_in_zones :=
[dispgentech];

# Set of dispatchable generators
set re_disp_gen_tech_in_zones :=
[redispgentech];

#Set of storage technologies in each zones
set stor_tech_in_zones :=
[stortech];

# Set of hybrid technologies available in each zone
set hyb_tech_in_zones :=
[hybtech];

#Retrieve fuel costs for defined technologies
load "opencem.ckvu5hxg6w5z.ap-southeast-1.rds.amazonaws.com" database=opencem_input
user=select password=select_password using=pymysql
query="SELECT cap.ntndp_zone_id AS zones, cap.technology_type_id AS all_tech, avg(fuel.price) as cost_fuel
FROM fuel_price fuel
INNER JOIN capacity cap ON cap.id=fuel.capacity_id
WHERE fuel.year=XXXX AND fuel.fuel_scenario_id =3
AND (cap.ntndp_zone_id,cap.technology_type_id) in
[fueltechdb]
group by zones,all_tech;" :[zones,all_tech] cost_fuel;

#Retrieve fuel heat rates for default technologies
load "opencem.ckvu5hxg6w5z.ap-southeast-1.rds.amazonaws.com" database=opencem_input
user=select password=select_password using=pymysql
query="SELECT cap.ntndp_zone_id AS zones, cap.technology_type_id AS all_tech, avg(heat.heat_rate) as fuel_heat_rate
FROM heat_rates heat
INNER JOIN capacity cap ON cap.id=heat.capacity_id
AND (cap.ntndp_zone_id,cap.technology_type_id) in
[fueltechdb]
group by zones,all_tech;" :[zones,all_tech] fuel_heat_rate;

#Ordered set of timestams to retrieve traces for dispatch calculations
load "opencem.ckvu5hxg6w5z.ap-southeast-1.rds.amazonaws.com" database=opencem_input
user=select password=select_password using=pymysql
format=set
query="SELECT DISTINCT cast(timestamp AS CHAR)
FROM demand_and_rooftop_traces
WHERE timestamp [timerange]
AND MINUTE(timestamp)=0;" :t;

# Build costs for gentech
load "opencem.ckvu5hxg6w5z.ap-southeast-1.rds.amazonaws.com" database=opencem_input
user=select password=select_password using=pymysql
query="
SELECT ntndp_zone_id AS zones,technology_type_id AS all_tech,
1000*capex AS cost_gen_build
FROM capex
WHERE year=XXXX
AND (demand_scenario_id,ntndp_zone_id, technology_type_id) in (
  SELECT MAX(demand_scenario_id), ntndp_zone_id, technology_type_id
  FROM capex
  WHERE demand_scenario_id IN (3,1)
  AND (ntndp_zone_id, technology_type_id) IN
  [gentechdb]
  GROUP BY ntndp_zone_id,technology_type_id
  );": [zones,all_tech] cost_gen_build;

# Build costs for stortech
load "opencem.ckvu5hxg6w5z.ap-southeast-1.rds.amazonaws.com" database=opencem_input
user=select password=select_password using=pymysql
query="
SELECT ntndp_zone_id AS zones,technology_type_id AS all_tech,
1000*capex AS cost_stor_build
FROM capex
WHERE year=XXXX
AND (demand_scenario_id,ntndp_zone_id, technology_type_id) in (
  SELECT MAX(demand_scenario_id), ntndp_zone_id, technology_type_id
  FROM capex
  WHERE demand_scenario_id IN (3,1)
  AND (ntndp_zone_id, technology_type_id) IN
  [stortechdb]
  GROUP BY ntndp_zone_id,technology_type_id
  );": [zones,all_tech] cost_stor_build;

# Build costs for hybtech
load "opencem.ckvu5hxg6w5z.ap-southeast-1.rds.amazonaws.com" database=opencem_input
user=select password=select_password using=pymysql
query="
SELECT ntndp_zone_id AS zones,technology_type_id AS all_tech,
1000*capex AS cost_hyb_build
FROM capex
WHERE year=XXXX
AND (demand_scenario_id,ntndp_zone_id, technology_type_id) in (
  SELECT MAX(demand_scenario_id), ntndp_zone_id, technology_type_id
  FROM capex
  WHERE demand_scenario_id IN (3,1)
  AND (ntndp_zone_id, technology_type_id) IN
  [hybtechdb]
  GROUP BY ntndp_zone_id,technology_type_id
  );": [zones,all_tech] cost_hyb_build;

# FOM VOM costs for gentech
load "opencem.ckvu5hxg6w5z.ap-southeast-1.rds.amazonaws.com" database=opencem_input
user=select password=select_password using=pymysql
query="
SELECT technology_type_id AS all_tech, 1000*fom AS cost_gen_fom, vom AS cost_gen_vom
FROM opex WHERE (source_id, technology_type_id) in
  (SELECT MAX(source_id),technology_type_id
   FROM opex where technology_type_id in ([gentechlist]) GROUP BY technology_type_id);": [all_tech] cost_gen_fom cost_gen_vom;

# FOM VOM costs for stortech
load "opencem.ckvu5hxg6w5z.ap-southeast-1.rds.amazonaws.com" database=opencem_input
user=select password=select_password using=pymysql
query="SELECT technology_type_id AS all_tech, 1000*fom AS cost_stor_fom, vom AS cost_stor_vom
FROM opex WHERE (source_id, technology_type_id) in
  (SELECT MAX(source_id),technology_type_id
   FROM opex where technology_type_id in ([stortechlist]) GROUP BY technology_type_id);": [all_tech] cost_stor_fom cost_stor_vom;

# FOM VOM costs for hybtech
load "opencem.ckvu5hxg6w5z.ap-southeast-1.rds.amazonaws.com" database=opencem_input
user=select password=select_password using=pymysql
query="SELECT technology_type_id AS all_tech, 1000*fom AS cost_hyb_fom, vom AS cost_hyb_vom
FROM opex WHERE (source_id, technology_type_id) in
  (SELECT MAX(source_id),technology_type_id
   FROM opex where technology_type_id in ([hybtechlist]) GROUP BY technology_type_id);": [all_tech] cost_hyb_fom cost_hyb_vom;

#Starting capacity (either cfrom capacity table or carry forward from previous)
[carryforwardcap]

# Exogenously commissioned capacity
load "opencem.ckvu5hxg6w5z.ap-southeast-1.rds.amazonaws.com" database=opencem_input
user=select password=select_password using=pymysql
query="select ntndp_zone_id as zones, technology_type_id as all_tech, sum(reg_cap) as gen_cap_exo
from capacity
where (ntndp_zone_id,technology_type_id) in
[gentechdb] and commissioning_year >WWWW and commissioning_year<=XXXX
group by zones,all_tech;" : [zones,all_tech] gen_cap_exo;

load "opencem.ckvu5hxg6w5z.ap-southeast-1.rds.amazonaws.com" database=opencem_input
user=select password=select_password using=pymysql
query="select ntndp_zone_id as zones, technology_type_id as all_tech, sum(reg_cap) as stor_cap_exo
from capacity
where (ntndp_zone_id,technology_type_id) in
[stortechdb] and commissioning_year >WWWW and commissioning_year<=XXXX
group by zones,all_tech;" : [zones,all_tech] stor_cap_exo;

load "opencem.ckvu5hxg6w5z.ap-southeast-1.rds.amazonaws.com" database=opencem_input
user=select password=select_password using=pymysql
query="select ntndp_zone_id as zones, technology_type_id as all_tech, sum(reg_cap) as hyb_cap_exo
from capacity
where (ntndp_zone_id,technology_type_id) in
[hybtechdb] and commissioning_year >WWWW and commissioning_year<=XXXX
group by zones,all_tech;" : [zones,all_tech] hyb_cap_exo;


# Exogenously retired capacity
load "opencem.ckvu5hxg6w5z.ap-southeast-1.rds.amazonaws.com" database=opencem_input
user=select password=select_password using=pymysql
query="select ntndp_zone_id as zones, technology_type_id as all_tech, sum(reg_cap) as ret_gen_cap_exo
from capacity
where (ntndp_zone_id,technology_type_id) in
[retiretechdb] and retirement_year >WWWW and retirement_year<=XXXX
group by zones,all_tech;" : [zones,all_tech] ret_gen_cap_exo;

#capacity factors for technologies with traces
# TODO FIXME  check that the MAX statement correctly retrieves the desired scenario
load "opencem.ckvu5hxg6w5z.ap-southeast-1.rds.amazonaws.com" database=opencem_input
user=select password=select_password using=pymysql
query="
SELECT t1.ntndp_zone_id AS zones, t1.technology_type_id AS all_tech, cast(t1.timestamp AS CHAR) AS t, avg(mw) AS gen_cap_factor, t1.source_id
FROM wind_and_solar_traces t1
INNER JOIN (SELECT max(source_id) as source_id, ntndp_zone_id, technology_type_id, timestamp
            FROM wind_and_solar_traces
            WHERE timestamp [timerange]
                AND MINUTE(timestamp)=0
                AND (ntndp_zone_id, technology_type_id) IN [gentechdb]
            GROUP BY ntndp_zone_id, technology_type_id, timestamp) as t2
    on t1.ntndp_zone_id = t2.ntndp_zone_id
       AND t1.technology_type_id = t2.technology_type_id
       AND t1.timestamp = t2.timestamp
       AND t1.source_id = t2.source_id
GROUP BY t1.ntndp_zone_id, t1.technology_type_id, t1.timestamp;" : [zones,all_tech,t] gen_cap_factor;

# TODO  check that the MAX statement correctly retrieves the desired scenario
load "opencem.ckvu5hxg6w5z.ap-southeast-1.rds.amazonaws.com" database=opencem_input
user=select password=select_password using=pymysql
query="
SELECT t1.ntndp_zone_id AS zones, t1.technology_type_id AS all_tech, cast(t1.timestamp AS CHAR) AS t, avg(mw) AS hyb_cap_factor, t1.source_id
FROM wind_and_solar_traces t1
INNER JOIN (SELECT max(source_id) as source_id, ntndp_zone_id, technology_type_id, timestamp
            FROM wind_and_solar_traces
            WHERE timestamp [timerange]
                AND MINUTE(timestamp)=0
                AND (ntndp_zone_id, technology_type_id) IN [hybtechdb]
            GROUP BY ntndp_zone_id, technology_type_id, timestamp) as t2
    on t1.ntndp_zone_id = t2.ntndp_zone_id
       AND t1.technology_type_id = t2.technology_type_id
       AND t1.timestamp = t2.timestamp
       AND t1.source_id = t2.source_id
GROUP BY t1.ntndp_zone_id, t1.technology_type_id, t1.timestamp;" : [zones,all_tech,t] hyb_cap_factor;

#Demand for for all regions for the given time interval
# TODO  check that the MAX statement correctly retrieves the desired scenario
# TODO tidy up operational demand query
load "opencem.ckvu5hxg6w5z.ap-southeast-1.rds.amazonaws.com" database=opencem_input
user=select password=select_password using=pymysql
query="SELECT cast(poe.timestamp AS CHAR) AS t, poe.region_id AS regions, (poe.poe10 -0*roof.rooftop_solar) AS region_net_demand
FROM demand_and_rooftop_traces poe
INNER JOIN (SELECT timestamp, region_id, rooftop_solar FROM demand_and_rooftop_traces
WHERE timestamp [timerange]
AND MINUTE(timestamp)=0
AND demand_scenario_id =3) roof
ON poe.timestamp = roof.timestamp AND poe.region_id = roof.region_id
AND poe.demand_scenario_id =3
AND poe.region_id in (SELECT id FROM region WHERE text_id in ('NSW','SA','QLD','VIC','TAS'))
order BY regions,t;": [regions,t] region_net_demand;