def climate_station_parameter_range_to(row):
default = current.messages["NONE"]
if hasattr(row, "climate_station_parameter"):
row = row.climate_station_parameter
try:
parameter_id = row.parameter_id
station_id = row.station_id
except AttributeError:
return default
table = current.s3db.table("climate_sample_table_%s" % parameter_id)
if not table:
return default
date = table.time_period.max()
row = db(table.place_id == station_id).select(date).first()
if row:
date = row[date]
import ClimateDataPortal
year, month = ClimateDataPortal.month_number_to_year_month(date)
return "%s-%s" % (month, year)
else:
return default
def climate_station_parameter_range_to(self):
default = current.messages["NONE"]
if hasattr(row, "climate_station_parameter"):
row = row.climate_station_parameter
try:
parameter_id = row.parameter_id
station_id = row.station_id
except AttributeError:
return default
table = current.s3db.table("climate_sample_table_%s" % parameter_id)
if not table:
return default
date = table.time_period.max()
row = db(table.place_id == station_id).select(date).first()
if row:
date = row[date]
import ClimateDataPortal
year, month = ClimateDataPortal.month_number_to_year_month(date)
return "%s-%s" % (month, year)
else:
return default
def range_to(self):
query = ("SELECT MAX(time_period) "
"from climate_sample_table_%(parameter_id)i "
"WHERE place_id = %(station_id)i;") % dict(
parameter_id=self.climate_station_parameter.parameter_id,
station_id=self.climate_station_parameter.station_id,
)
date = current.db.executesql(query)[0][0]
if date is not None:
import ClimateDataPortal
year, month = ClimateDataPortal.month_number_to_year_month(date)
return "%s-%s" % (month, year)
else:
return current.messages.NONE
def range_to(self):
query = (
"SELECT MAX(time_period) "
"from climate_sample_table_%(parameter_id)i "
"WHERE place_id = %(station_id)i;"
) % dict(
parameter_id = self.climate_station_parameter.parameter_id,
station_id = self.climate_station_parameter.station_id,
)
date = current.db.executesql(query)[0][0]
if date is not None:
import ClimateDataPortal
year, month = ClimateDataPortal.month_number_to_year_month(date)
return "%s-%s" % (month, year)
else:
return current.messages.NONE
def aggregate(sample_table, Aggregation, db):
aggregation_function = Aggregation.SQL_function
aggregate_table = sample_table + "_monthly_" + aggregation_function
sample_table_name = sample_table
value_type = "real"
create_table_sql = """
DROP TABLE IF EXISTS %(aggregate_table)s;
CREATE TABLE %(aggregate_table)s (
place_id integer NOT NULL,
"month" smallint NOT NULL,
"value" real NOT NULL,
CONSTRAINT %(aggregate_table)s_primary_key
PRIMARY KEY (place_id, month),
CONSTRAINT %(aggregate_table)s_place_id_fkey
FOREIGN KEY (place_id)
REFERENCES climate_place (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
);
""" % locals()
db.executesql(create_table_sql)
# takes ~ 30 secs
year_dot_num = ClimateDataPortal.year_month_to_month_number(0, 1)
start_date_iso = ClimateDataPortal.start_date.isoformat()
table_name = sample_table
aggregation_func = Aggregation.SQL_function
insert_sql = """
INSERT INTO %(aggregate_table)s (month, place_id, value)
SELECT
(
%(year_dot_num)i +
(EXTRACT(year FROM "subquery"."date") * 12) +
(EXTRACT(month FROM "subquery"."date") - 1)
) as month,
"subquery"."place_id" as place_id,
COALESCE (%(aggregation_func)s("subquery"."value"), 0) as value
FROM (
SELECT
(date '%(start_date_iso)s' + time_period) as "date",
value,
place_id
FROM %(table_name)s
) as "subquery"
GROUP BY month, place_id
;
""" % locals()
#print insert_sql
db.executesql(insert_sql)
"""
update "climate_sample_table_12_monthly_stddev"
SET count = count.value
FROM
"climate_sample_table_12_monthly_count" as "count"
WHERE
"count"."month" = "climate_sample_table_12_monthly_stddev"."month" and
"count"."place_id" = "climate_sample_table_12_monthly_stddev"."place_id"
;
update "climate_sample_table_12_monthly_stddev"
SET mean = mean.value
FROM
"climate_sample_table_12_monthly_avg" as "mean"
WHERE
"mean"."month" = "climate_sample_table_12_monthly_stddev"."month" and
"mean"."place_id" = "climate_sample_table_12_monthly_stddev"."place_id"
;
select
"stddev"."place_id",
sqrt(
(
SUM(("stddev"."count" - 1) * ("stddev".value ^ 2))
+ SUM("stddev"."count" * ("stddev"."mean" ^ 2))
- SUM("stddev"."count") * (
(
SUM("stddev"."count" * "stddev"."mean") / SUM("stddev"."count")
) ^ 2
)
) / (SUM("stddev"."count") - 1)
)
FROM
"climate_sample_table_12_monthly_stddev" as "stddev"
GROUP BY
"stddev"."place_id"
;
"""
db.commit()
) / (SUM("stddev"."count") - 1)
)
FROM
"climate_sample_table_12_monthly_stddev" as "stddev"
GROUP BY
"stddev"."place_id"
;
"""
db.commit()
from ClimateDataPortal.DSL import aggregations
for sample_table in db(db.climate_sample_table_spec).select():
for Aggregation in aggregations:
#print sample_table.name, Aggregation.__name__
aggregate(ClimateDataPortal.sample_table_id(sample_table.id),
Aggregation, db)
def combine_stddev(x, y, ddof=1):
# ddof = 1 matches postgres stddev
mu_x_u_y = (1.0 / (x.count + y.count)) * ((x.count * x.mean) +
(y.count * y.mean))
return math.sqrt(
(1.0 / (x.count + y.count - ddof)) *
(((x.count - ddof) * (x.stddev**2)) + ((x.count * (x.mean**2))) +
((y.count - ddof) * (y.stddev**2)) + ((y.count * (y.mean**2))) -
((x.count + y.count) * (mu_x_u_y**2))))
def combine_stddev3(x, y, z, ddof=1):
def aggregate(sample_table, Aggregation, db):
aggregation_function = Aggregation.SQL_function
aggregate_table = sample_table+"_monthly_"+aggregation_function
sample_table_name = sample_table
value_type = "real"
create_table_sql = """
DROP TABLE IF EXISTS %(aggregate_table)s;
CREATE TABLE %(aggregate_table)s (
place_id integer NOT NULL,
"month" smallint NOT NULL,
"value" real NOT NULL,
CONSTRAINT %(aggregate_table)s_primary_key
PRIMARY KEY (place_id, month),
CONSTRAINT %(aggregate_table)s_place_id_fkey
FOREIGN KEY (place_id)
REFERENCES climate_place (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
);
""" % locals()
db.executesql(create_table_sql)
# takes ~ 30 secs
year_dot_num = ClimateDataPortal.year_month_to_month_number(0, 1)
start_date_iso = ClimateDataPortal.start_date.isoformat()
table_name = sample_table
aggregation_func = Aggregation.SQL_function
insert_sql = """
INSERT INTO %(aggregate_table)s (month, place_id, value)
SELECT
(
%(year_dot_num)i +
(EXTRACT(year FROM "subquery"."date") * 12) +
(EXTRACT(month FROM "subquery"."date") - 1)
) as month,
"subquery"."place_id" as place_id,
COALESCE (%(aggregation_func)s("subquery"."value"), 0) as value
FROM (
SELECT
(date '%(start_date_iso)s' + time_period) as "date",
value,
place_id
FROM %(table_name)s
) as "subquery"
GROUP BY month, place_id
;
""" % locals()
#print insert_sql
db.executesql(insert_sql)
"""
update "climate_sample_table_12_monthly_stddev"
SET count = count.value
FROM
"climate_sample_table_12_monthly_count" as "count"
WHERE
"count"."month" = "climate_sample_table_12_monthly_stddev"."month" and
"count"."place_id" = "climate_sample_table_12_monthly_stddev"."place_id"
;
update "climate_sample_table_12_monthly_stddev"
SET mean = mean.value
FROM
"climate_sample_table_12_monthly_avg" as "mean"
WHERE
"mean"."month" = "climate_sample_table_12_monthly_stddev"."month" and
"mean"."place_id" = "climate_sample_table_12_monthly_stddev"."place_id"
;
select
"stddev"."place_id",
sqrt(
(
SUM(("stddev"."count" - 1) * ("stddev".value ^ 2))
+ SUM("stddev"."count" * ("stddev"."mean" ^ 2))
- SUM("stddev"."count") * (
(
SUM("stddev"."count" * "stddev"."mean") / SUM("stddev"."count")
) ^ 2
)
) / (SUM("stddev"."count") - 1)
)
FROM
"climate_sample_table_12_monthly_stddev" as "stddev"
GROUP BY
"stddev"."place_id"
;
"""
db.commit()
) / (SUM("stddev"."count") - 1)
)
FROM
"climate_sample_table_12_monthly_stddev" as "stddev"
GROUP BY
"stddev"."place_id"
;
"""
db.commit()
from ClimateDataPortal.DSL import aggregations
for sample_table in db(db.climate_sample_table_spec).select():
for Aggregation in aggregations:
#print sample_table.name, Aggregation.__name__
aggregate(
ClimateDataPortal.sample_table_id(sample_table.id),
Aggregation,
db
)
def combine_stddev(x, y, ddof = 1):
# ddof = 1 matches postgres stddev
mu_x_u_y = (1.0 / (x.count + y.count)) * ((x.count * x.mean) + (y.count * y.mean))
return math.sqrt(
(1.0 / (x.count + y.count - ddof)) * (
((x.count - ddof)*(x.stddev ** 2)) +
((x.count * (x.mean ** 2))) +
((y.count - ddof)*(y.stddev ** 2)) +
