Postgres version 9.4.18, PostGIS Version 2.2.
Here are the tables I'm working with (and can unlikely make significant changes to the table structure):
Table ltg_data
(spans 1988 to 2018):
Column | Type | Modifiers
----------+--------------------------+-----------
intensity | integer | not null
time | timestamp with time zone | not null
lon | numeric(9,6) | not null
lat | numeric(8,6) | not null
ltg_geom | geometry(Point,4269) |
Indexes:
"ltg_data2_ltg_geom_idx" gist (ltg_geom)
"ltg_data2_time_idx" btree ("time")
Size of ltg_data
(~800M rows):
ltg=# select pg_relation_size('ltg_data');
pg_relation_size
------------------
149729288192
Table counties
:
Column | Type | Modifiers
-----------+-----------------------------+--------------------------------- -----------------------
gid | integer | not null default nextval('counties_gid_seq'::regclass)
objectid_1 | integer |
objectid | integer |
state | character varying(2) |
cwa | character varying(9) |
countyname | character varying(24) |
fips | character varying(5) |
time_zone | character varying(2) |
fe_area | character varying(2) |
lon | double precision |
lat | double precision |
the_geom | geometry(MultiPolygon,4269) |
Indexes:
"counties_pkey" PRIMARY KEY, btree (gid)
"counties_gix" gist (the_geom)
"county_cwa_idx" btree (cwa)
"countyname_cwa_idx" btree (countyname)
Desired result:
I want a time series with one row for every day of the year in format 'MM-DD' ignoring the year: 01-01, 01-02, 01-03, ..., 12-31. And the count of rows in table ltg_data
for each day of the year. I also eventually want the same thing for every hour of every day of the year ('MM-DD-HH').
A group by
statement should accomplish this, but I'm having a hard time joining the "big" table with the days generated with generate_series()
.
MM-DD | total_count
-------+------------
12-22 | 9
12-23 | 0
12-24 | 0
12-25 | 0
12-26 | 23
12-27 | 0
12-28 | 5
12-29 | 0
12-30 | 0
12-31 | 0
Some of my many attempted queries:
SELECT date_trunc('day', d),
count(a.lat) AS strikes
FROM generate_series('2017-01-01', '2018-12-31', interval '1 day') AS d
LEFT JOIN
(SELECT date_trunc('day', TIME) AS day_of_year,
ltg_data.lat
FROM ltg_data
JOIN counties ON ST_contains(counties.the_geom, ltg_data.ltg_geom)
WHERE cwa = 'MFR' ) AS a ON d = day_of_year
GROUP BY d
ORDER BY d ASC;
But this doesn't ignore year. I shouldn't be surprised because the "day" in date_trunc is still considering the year I guess.
2017-12-27 00:00:00-08 | 0
2017-12-28 00:00:00-08 | 0
2017-12-29 00:00:00-08 | 0
2017-12-30 00:00:00-08 | 0
2017-12-31 00:00:00-08 | 0
2018-01-01 00:00:00-08 | 0
2018-01-02 00:00:00-08 | 12
2018-01-03 00:00:00-08 | 0
And this query, in which I'm trying to convert the data from generate_series()
to text
in 'DD-MM' format to join to the ltg_data
table in text
format. Says the data types don't match. I've tried extract
as well, since that could provide "doy" and "hour", which would work, but I can't seem to match data types in that query either. It's hard to make that "generate_series" a double precision.
SELECT to_char(d, 'MM-DD') AS DAY,
count(a.lat) AS strikes
FROM
(SELECT generate_series('2017-01-01', '2018-12-31', interval '1 day') AS d)
AS f
LEFT JOIN
(SELECT to_char(TIME, 'MM-DD') AS day_of_year,
ltg_data.lat
FROM ltg_data
JOIN counties ON ST_contains(counties.the_geom, ltg_data.ltg_geom)
WHERE cwa = 'MFR' ) AS a ON f = day_of_year
GROUP BY d
ORDER BY d ASC;
Result:
ERROR: operator does not exist: record = text
LINE 4: ON f = day_of_year group by d order by d asc;
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
Conclusion: I'm aiming at getting daily and hourly total counts that span many years but group by 'MM-DD' and 'MM-DD-HH' (ignoring year), with the query results showing all days/hours even if they are zero.
Later I'll also try to find averages and percentiles for days and hours, so if you have any advice on that, I'm all ears. But my current problem is focused on just getting a complete result for totals.