@rhysallister
rapidly rotating storm systems characterized by a low-pressure center,
a closed low-level atmospheric circulation,
strong winds, and a spiral arrangement of thunderstorms that produce heavy rain and/or squalls.
They derive energy mainly from warm ocean waters and
are therefore typically strongest when over or near water,
and weaken quite rapidly over land.
It is a set of software subsystems or components needed to create a complete platform such that no additional software is needed to support applications.
These are all PostgreSQL extensions written primarily by the same person
These are all PostgreSQL extensions written primarily by the same person
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
Code
CREATE EXTENSION postgis;
CREATE EXTENSION postgis;
CREATE EXTENSION ogr_fdw;
CREATE EXTENSION postgis;
CREATE EXTENSION ogr_fdw;
CREATE EXTENSION http;
Understanding the Assignment
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
BEGIN
END;
$body$
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
tbl_array text[];
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
FOR rec IN
WITH response AS (
SELECT content FROM http_get('https://www.nhc.noaa.gov/gis/archive_forecast_results.php')),
contents as (
SELECT DISTINCT regexp_split_to_table(content,'\n') content_by_row FROM response )
SELECT row_number() OVER (), regexp_match(content_by_row,'[A-z]\w+.zip') zip FROM contents
WHERE content_by_row ~ '[A-z]\w+.zip"'
LOOP
RAISE NOTICE '%::%', rec.row_number, rec.zip[1];
END LOOP;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
NOTICE: 13060::al082021_5day_015.zip
NOTICE: 13061::al142012_5day_059.zip
NOTICE: 13062::al062014_5day_033.zip
NOTICE: 13063::ep172008_5day_003.zip
NOTICE: 13064::ep162020_5day_010.zip
NOTICE: 13065::ep192015_5day_009.zip
NOTICE: 13066::ep022010_5day_002A.zip
NOTICE: 13067::al092020_5day_008.zip
NOTICE: 13068::ep142021_5day_015A.zip
NOTICE: 13069::ep072016_5day_005.zip
NOTICE: 13070::ep142021_5day_004A.zip
NOTICE: 13071::ep012014_5day_020.zip
NOTICE: 13072::ep172016_5day_009A.zip
NOTICE: 13073::al102017_5day_001A.zip
NOTICE: 13074::al142011_5day_034.zip
NOTICE: 13075::ep152014_5day_023.zip
NOTICE: 13076::ep112014_5day_034.zip
NOTICE: 13077::al142012_5day_025.zip
NOTICE: 13078::al092012_5day_003.zip
NOTICE: 13079::al072014_5day_004A.zip
NOTICE: 13080::ep162008_5day_012A.zip
NOTICE: 13081::al092018_5day_012.zip
NOTICE: 13082::ep102017_5day_019.zip
NOTICE: 13083::al162016_5day_017.zip
NOTICE: 13084::ep092012_5day_019.zip
NOTICE: 13085::ep072011_5day_008.zip
NOTICE: 13086::al062021_5day_028A.zip
NOTICE: 13087::al072017_5day_012A.zip
NOTICE: 13088::al082020_5day_015A.zip
NOTICE: 13089::al082019_5day_009.zip
NOTICE: 13090::ep162020_5day_017.zip
NOTICE: 13091::ep032017_5day_002.zip
NOTICE: 13092::ep162017_5day_003.zip
NOTICE: 13093::al112013_5day_002.zip
NOTICE: 13094::al122019_5day_006.zip
DO
Time: 3047.914 ms (00:03.048)
cyclonic@localhost:5432/cyclonic|1|# 🐘
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
tbl_array text[];
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
FOR rec IN
WITH response AS (
SELECT content FROM http_get('https://www.nhc.noaa.gov/gis/archive_forecast_results.php')),
contents as (
SELECT DISTINCT regexp_split_to_table(content,'\n') content_by_row FROM response )
SELECT row_number() OVER (), regexp_match(content_by_row,'[A-z]\w+.zip') zip FROM contents
WHERE content_by_row ~ '[A-z]\w+.zip"' LOOP
RAISE NOTICE '%::%', rec.row_number, rec.zip[1];
END LOOP;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
tbl_array text[];
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
FOR rec IN
WITH response AS (
SELECT content FROM http_get('https://www.nhc.noaa.gov/gis/archive_forecast_results.php')),
contents as (
SELECT DISTINCT regexp_split_to_table(content,'\n') content_by_row FROM response )
SELECT row_number() OVER (), regexp_match(content_by_row,'[A-z]\w+.zip') zip FROM contents
WHERE content_by_row ~ '[A-z]\w+.zip"' LOOP
RAISE NOTICE '%::%', rec.row_number, rec.zip[1];
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip[1],
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip[1],
'ESRI Shapefile'
);
END LOOP;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
tbl_array text[];
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
FOR rec IN
WITH response AS (
SELECT content FROM http_get('https://www.nhc.noaa.gov/gis/archive_forecast_results.php')),
contents as (
SELECT DISTINCT regexp_split_to_table(content,'\n') content_by_row FROM response )
SELECT row_number() OVER (), regexp_match(content_by_row,'[A-z]\w+.zip') zip FROM contents
WHERE content_by_row ~ '[A-z]\w+.zip"' LOOP
RAISE NOTICE '%::%', rec.row_number, rec.zip[1];
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip[1],
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip[1],
'ESRI Shapefile'
);
EXECUTE format($$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic
OPTIONS (launder_table_names 'false', launder_column_names 'false');$$,
rec.zip[1]);
END LOOP;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
...
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
FOR rec IN
WITH response AS (
SELECT content FROM http_get('https://www.nhc.noaa.gov/gis/archive_forecast_results.php')),
contents as (
SELECT DISTINCT regexp_split_to_table(content,'\n') content_by_row FROM response )
SELECT row_number() OVER (), regexp_match(content_by_row,'[A-z]\w+.zip') zip FROM contents
WHERE content_by_row ~ '[A-z]\w+.zip"' LOOP
RAISE NOTICE '%::%', rec.row_number, rec.zip[1];
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip[1],
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip[1],
'ESRI Shapefile'
);
tbl_array = regexp_split_to_array(rec.zip[1],'\_|\.');
tbl_name = format('%s-%s_5day_pts', tbl_array[1], tbl_array[3]);
EXECUTE format($$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic
OPTIONS (launder_table_names 'false', launder_column_names 'false');$$,
rec.zip[1]);
COMMIT;
END LOOP;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
...
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
FOR rec IN
WITH response AS (
SELECT content FROM http_get('https://www.nhc.noaa.gov/gis/archive_forecast_results.php')),
contents as (
SELECT DISTINCT regexp_split_to_table(content,'\n') content_by_row FROM response )
SELECT row_number() OVER (), regexp_match(content_by_row,'[A-z]\w+.zip') zip FROM contents
WHERE content_by_row ~ '[A-z]\w+.zip"' LOOP
RAISE NOTICE '%::%', rec.row_number, rec.zip[1];
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip[1],
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip[1],
'ESRI Shapefile'
);
tbl_array = regexp_split_to_array(rec.zip[1],'\_|\.');
tbl_name = format('%s-%s_5day_pts', tbl_array[1], tbl_array[3]);
EXECUTE format($$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic
OPTIONS (launder_table_names 'false', launder_column_names 'false');$$,
rec.zip[1]);
COMMIT;
IF rec.row_number = 1 THEN
EXECUTE format('CREATE TABLE cyclonic.nhc_data AS SELECT * FROM cyclonic.%I;', tbl_name);
ELSE
EXECUTE format('INSERT INTO cyclonic.nhc_data SELECT * FROM cyclonic.%I;', tbl_name);
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip[1]);
END LOOP;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
drop cascades to foreign table "al182017-002A_5day_pgn"
drop cascades to foreign table "al182017-002A_5day_pts"
drop cascades to foreign table "al182017-002A_ww_wwlin"
psql:c1.sql:49: NOTICE: 4::al082018_5day_032.zip
psql:c1.sql:49: NOTICE: Number of tables to be created 4
psql:c1.sql:49: NOTICE: drop cascades to 4 other objects
DETAIL: drop cascades to foreign table "al082018-032_5day_lin"
drop cascades to foreign table "al082018-032_5day_pgn"
drop cascades to foreign table "al082018-032_5day_pts"
drop cascades to foreign table "al082018-032_ww_wwlin"
psql:c1.sql:49: NOTICE: 5::al092016_5day_033.zip
psql:c1.sql:49: NOTICE: Number of tables to be created 4
psql:c1.sql:49: ERROR: column "LON" is of type double precision but expression is of type character varying
LINE 1: INSERT INTO cyclonic.nhc_data SELECT * FROM cyclonic."al0920...
^
HINT: You will need to rewrite or cast the expression.
QUERY: INSERT INTO cyclonic.nhc_data SELECT * FROM cyclonic."al092016-033_5day_pts";
CONTEXT: PL/pgSQL function inline_code_block line 35 at EXECUTE
Time: 3680.987 ms (00:03.681)
cyclonic@localhost:5432/cyclonic|1|# 🐘
The schema of the .shp files change over time.
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
tbl_array text[];
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
FOR rec IN
WITH response AS (
SELECT content FROM http_get('https://www.nhc.noaa.gov/gis/archive_forecast_results.php')),
contents as (
SELECT DISTINCT regexp_split_to_table(content,'\n') content_by_row FROM response )
SELECT row_number() OVER (), regexp_match(content_by_row,'[A-z]\w+.zip') zip FROM contents
WHERE content_by_row ~ '[A-z]\w+.zip"' LOOP
--new_name := lower(split_part(rec.zip[1],'.',1)); /*1*/
RAISE NOTICE '%::%', rec.row_number, rec.zip[1];
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip[1],
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip[1],
'ESRI Shapefile'
);
tbl_array = regexp_split_to_array(rec.zip[1],'\_|\.');
tbl_name = format('%s-%s_5day_pts', tbl_array[1], tbl_array[3]);
EXECUTE format($$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic
OPTIONS (launder_table_names 'false', launder_column_names 'false');$$,
rec.zip[1]);
COMMIT;
IF rec.row_number = 1 THEN
EXECUTE format('CREATE TABLE cyclonic.nhc_data AS SELECT * FROM cyclonic.%I;', tbl_name);
ELSE
EXECUTE format('INSERT INTO cyclonic.nhc_data SELECT * FROM cyclonic.%I;', tbl_name);
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip[1]);
IF rec.row_number % 100 = 0 THEN
COMMIT;
END IF;
END LOOP;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
tbl_array text[];
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
FOR rec IN
WITH response AS (
SELECT content FROM http_get('https://www.nhc.noaa.gov/gis/archive_forecast_results.php')),
contents as (
SELECT DISTINCT regexp_split_to_table(content,'\n') content_by_row FROM response )
SELECT row_number() OVER (), regexp_match(content_by_row,'[A-z]\w+.zip') zip FROM contents
WHERE content_by_row ~ '[A-z]\w+.zip"' LOOP
--new_name := lower(split_part(rec.zip[1],'.',1)); /*1*/
RAISE NOTICE '%::%', rec.row_number, rec.zip[1];
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip[1],
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip[1],
'ESRI Shapefile'
);
tbl_array = regexp_split_to_array(rec.zip[1],'\_|\.');
tbl_name = format('%s-%s_5day_pts', tbl_array[1], tbl_array[3]);
EXECUTE format($$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic
OPTIONS (launder_table_names 'false', launder_column_names 'false');$$,
rec.zip[1]);
COMMIT;
IF rec.row_number = 1 THEN
EXECUTE format('CREATE TABLE cyclonic.nhc_data AS WITH _ AS (SELECT * FROM cyclonic.%I)
SELECT to_jsonb(_.*) bulk FROM _ ', tbl_name);
ELSE
EXECUTE format('INSERT INTO cyclonic.nhc_data WITH _ AS (SELECT * FROM cyclonic.%I)
SELECT to_jsonb(_.*) bulk FROM _;', tbl_name);
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip[1]);
IF rec.row_number % 100 = 0 THEN
COMMIT;
END IF;
END LOOP;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
cyclonic@localhost:5432/cyclonic|1|# 🐘\i c2.sql
psql:c2.sql:54: NOTICE: 1::al052019_5day_036.zip
psql:c2.sql:54: NOTICE: Number of tables to be created 4
psql:c2.sql:54: NOTICE: 2::al182019_5day_001.zip
psql:c2.sql:54: NOTICE: Number of tables to be created 3
psql:c2.sql:54: NOTICE: 3::al182017_5day_002A.zip
psql:c2.sql:54: NOTICE: Number of tables to be created 4
psql:c2.sql:54: NOTICE: 4::al082018_5day_032.zip
psql:c2.sql:54: NOTICE: Number of tables to be created 4
psql:c2.sql:54: NOTICE: 5::al092016_5day_033.zip
psql:c2.sql:54: NOTICE: Number of tables to be created 4
psql:c2.sql:54: NOTICE: 6::ep082014_5day_012.zip
psql:c2.sql:54: NOTICE: Number of tables to be created 3
psql:c2.sql:54: NOTICE: 7::al062021_5day_012A.zip
psql:c2.sql:54: NOTICE: Number of tables to be created 4
psql:c2.sql:54: NOTICE: 8::ep062016_5day_006.zip
psql:c2.sql:54: NOTICE: Number of tables to be created 3
psql:c2.sql:54: NOTICE: 9::al062015_5day_015.zip
psql:c2.sql:54: NOTICE: Number of tables to be created 3
psql:c2.sql:54: NOTICE: 10::ep022014_5day_001.zip
psql:c2.sql:54: NOTICE: Number of tables to be created 4
psql:c2.sql:54: NOTICE: 11::al082012_5day_005.zip
psql:c2.sql:54: NOTICE: Number of tables to be created 3
psql:c2.sql:54: ERROR: relation "cyclonic.al082012-005_5day_pts" does not exist
LINE 1: ...T INTO cyclonic.nhc_data WITH _ AS (SELECT * FROM cyclonic."...
^
QUERY: INSERT INTO cyclonic.nhc_data WITH _ AS (SELECT * FROM cyclonic."al082012-005_5day_pts")
SELECT to_jsonb(_.*) bulk FROM _;
CONTEXT: PL/pgSQL function inline_code_block line 39 at EXECUTE
Time: 6851.762 ms (00:06.852)
cyclonic@localhost:5432/cyclonic|1|# 🐘
The file names in the zip files have changed
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
tbl_array text[];
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
FOR rec IN
WITH response AS (
SELECT content FROM http_get('https://www.nhc.noaa.gov/gis/archive_forecast_results.php')),
contents as (
SELECT DISTINCT regexp_split_to_table(content,'\n') content_by_row FROM response )
SELECT row_number() OVER (), regexp_match(content_by_row,'[A-z]\w+.zip') zip FROM contents
WHERE content_by_row ~ '[A-z]\w+.zip"' LOOP
--new_name := lower(split_part(rec.zip[1],'.',1)); /*1*/
RAISE NOTICE '%::%', rec.row_number, rec.zip[1];
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip[1],
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip[1],
'ESRI Shapefile'
);
tbl_array = regexp_split_to_array(rec.zip[1],'\_|\.');
tbl_name = format('%s-%s_5day_pts', tbl_array[1], tbl_array[3]);
EXECUTE format($$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic
OPTIONS (launder_table_names 'false', launder_column_names 'false');$$,
rec.zip[1]);
COMMIT;
IF rec.row_number = 1 THEN
EXECUTE format('CREATE TABLE cyclonic.nhc_data AS WITH _ AS (SELECT * FROM cyclonic.%I)
SELECT to_jsonb(_.*) bulk FROM _ ', tbl_name);
ELSE
EXECUTE format('INSERT INTO cyclonic.nhc_data WITH _ AS (SELECT * FROM cyclonic.%I)
SELECT to_jsonb(_.*) bulk FROM _;', tbl_name);
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip[1]);
IF rec.row_number % 100 = 0 THEN
COMMIT;
END IF;
END LOOP;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
tbl_array text[];
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
FOR rec IN
WITH response AS (
SELECT content FROM http_get('https://www.nhc.noaa.gov/gis/archive_forecast_results.php')),
contents as (
SELECT DISTINCT regexp_split_to_table(content,'\n') content_by_row FROM response )
SELECT row_number() OVER (), regexp_match(content_by_row,'[A-z]\w+.zip') zip FROM contents
WHERE content_by_row ~ '[A-z]\w+.zip"' LOOP
--new_name := lower(split_part(rec.zip[1],'.',1)); /*1*/
RAISE NOTICE '%::%', rec.row_number, rec.zip[1];
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip[1],
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip[1],
'ESRI Shapefile'
);
EXECUTE format($$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic
OPTIONS (launder_table_names 'false', launder_column_names 'false');$$,
rec.zip[1]);
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
COMMIT;
IF rec.row_number = 1 THEN
EXECUTE format('CREATE TABLE cyclonic.nhc_data AS WITH _ AS (SELECT * FROM cyclonic.%I)
SELECT to_jsonb(_.*) bulk FROM _ ', tbl_name);
ELSE
EXECUTE format('INSERT INTO cyclonic.nhc_data WITH _ AS (SELECT * FROM cyclonic.%I)
SELECT to_jsonb(_.*) bulk FROM _;', tbl_name);
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip[1]);
IF rec.row_number % 100 = 0 THEN
COMMIT;
END IF;
END LOOP;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
DETAIL: drop cascades to foreign table "ep022015-021A_5day_lin"
drop cascades to foreign table "ep022015-021A_5day_pgn"
drop cascades to foreign table "ep022015-021A_5day_pts"
drop cascades to foreign table "ep022015-021A_ww_wwlin"
psql:c3.sql:56: NOTICE: 4670::al122021_5day_024.zip
psql:c3.sql:56: NOTICE: Number of tables to be created 3
psql:c3.sql:56: NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to foreign table "al122021-024_5day_lin"
drop cascades to foreign table "al122021-024_5day_pgn"
drop cascades to foreign table "al122021-024_5day_pts"
psql:c3.sql:56: NOTICE: 4671::ep122021_5day_008.zip
psql:c3.sql:56: NOTICE: Number of tables to be created 3
psql:c3.sql:56: NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to foreign table "ep122021-008_5day_lin"
drop cascades to foreign table "ep122021-008_5day_pgn"
drop cascades to foreign table "ep122021-008_5day_pts"
psql:c3.sql:56: NOTICE: 4672::al142012_5day_051.zip
psql:c3.sql:56: NOTICE: Number of tables to be created 3
psql:c3.sql:56: NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to foreign table "al142012.051_5day_lin"
drop cascades to foreign table "al142012.051_5day_pgn"
drop cascades to foreign table "al142012.051_5day_pts"
psql:c3.sql:56: NOTICE: 4673::ep082012_5day_002.zip
psql:c3.sql:56: NOTICE: Number of tables to be created 3
psql:c3.sql:56: NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to foreign table "ep082012.002_5day_lin"
drop cascades to foreign table "ep082012.002_5day_pgn"
drop cascades to foreign table "ep082012.002_5day_pts"
psql:c3.sql:56: NOTICE: 4674::ep212020_5day_003.zip
psql:c3.sql:56: ERROR: unable to connect to data source "/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/ep212020_5day_003.zip"
CONTEXT: SQL statement "CREATE SERVER "ep212020_5day_003.zip" FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource '/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/ep212020_5day_003.zip', format 'ESRI Shapefile');"
PL/pgSQL function inline_code_block line 20 at EXECUTE
Time: 1400743.863 ms (23:20.744)
cyclonic@localhost:5432/cyclonic|1|# 🐘
Random errors may pop up...
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
psql:c6.sql:58: NOTICE: ep252018_5day_011.zip
psql:c6.sql:58: NOTICE: Number of tables to be created 4
psql:c6.sql:58: NOTICE: drop cascades to 4 other objects
DETAIL: drop cascades to foreign table cyclonic2."ep252018-011_5day_lin"
drop cascades to foreign table cyclonic2."ep252018-011_5day_pgn"
drop cascades to foreign table cyclonic2."ep252018-011_5day_pts"
drop cascades to foreign table cyclonic2."ep252018-011_ww_wwlin"
psql:c6.sql:58: NOTICE: ep252018_5day_012A.zip
psql:c6.sql:58: NOTICE: Number of tables to be created 4
psql:c6.sql:58: NOTICE: drop cascades to 4 other objects
DETAIL: drop cascades to foreign table cyclonic2."ep252018-012A_5day_lin"
drop cascades to foreign table cyclonic2."ep252018-012A_5day_pgn"
drop cascades to foreign table cyclonic2."ep252018-012A_5day_pts"
drop cascades to foreign table cyclonic2."ep252018-012A_ww_wwlin"
psql:c6.sql:58: NOTICE: ep252018_5day_012.zip
psql:c6.sql:58: NOTICE: Number of tables to be created 4
psql:c6.sql:58: NOTICE: drop cascades to 4 other objects
DETAIL: drop cascades to foreign table cyclonic2."ep252018-012_5day_lin"
drop cascades to foreign table cyclonic2."ep252018-012_5day_pgn"
drop cascades to foreign table cyclonic2."ep252018-012_5day_pts"
drop cascades to foreign table cyclonic2."ep252018-012_ww_wwlin"
psql:c6.sql:58: NOTICE: ep252018_5day_013.zip
psql:c6.sql:58: NOTICE: Number of tables to be created 3
psql:c6.sql:58: NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to foreign table cyclonic2."ep252018-013_5day_lin"
drop cascades to foreign table cyclonic2."ep252018-013_5day_pgn"
drop cascades to foreign table cyclonic2."ep252018-013_5day_pts"
psql:c6.sql:58: NOTICE: ep252018_5day_014.zip
psql:c6.sql:58: NOTICE: Number of tables to be created 3
psql:c6.sql:58: NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to foreign table cyclonic2."ep252018-014_5day_lin"
drop cascades to foreign table cyclonic2."ep252018-014_5day_pgn"
drop cascades to foreign table cyclonic2."ep252018-014_5day_pts"
DO
Time: 7256124.756 ms (02:00:56.125)
cyclonic@localhost:5432/cyclonic|1|# 🐘
DO
Time: 7256124.756 ms (02:00:56.125)
cyclonic@localhost:5432/cyclonic|1|# 🐘
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
with _ as (
SELECT * FROM cyclonic.nhc_data
)
SELECT * FROM _;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
FROM cyclonic.nhc_data
)
SELECT * FROM _
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
FROM cyclonic.nhc_data
)
SELECT * FROM _
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time,
FROM cyclonic.nhc_data
)
SELECT * FROM _
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time,
geom::geography
FROM cyclonic.nhc_data
WHERE bulk->>'TIMEZONE' <> 'CVT'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time,
geom::geography
FROM cyclonic.nhc_data
WHERE bulk->>'TIMEZONE' <> 'CVT'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time, geom::geography
FROM cyclonic.nhc_data
WHERE bulk->>'TIMEZONE' <> 'CVT'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time, geom::geography
FROM cyclonic.nhc_data
WHERE "STORMNUM" = 18 AND yr = 2021 AND bulk->>'BASIN' = 'AL'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time, geom::geography
FROM cyclonic.nhc_data
WHERE "STORMNUM" = 18 AND yr = 2021 AND bulk->>'BASIN' = 'AL'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _,
__ AS (
SELECT
*
FROM _ a
)
SELECT * FROM __;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time, geom::geography
FROM cyclonic.nhc_data
WHERE "STORMNUM" = 18 AND yr = 2021 AND bulk->>'BASIN' = 'AL'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _,
__ AS (
SELECT
yr, "STORMNUM", basin, a.fc_time, a.tau ppt,a."ADVISNUM", b."ADVISNUM", (st_distance(a.geom, b.geom)/1000)::int dist
FROM _ a
)
SELECT * FROM __;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time, geom::geography
FROM cyclonic.nhc_data
WHERE "STORMNUM" = 18 AND yr = 2021 AND bulk->>'BASIN' = 'AL'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _,
__ AS (
SELECT
yr, "STORMNUM", basin, a.fc_time, a.tau ppt,a."ADVISNUM", b."ADVISNUM", (st_distance(a.geom, b.geom)/1000)::int dist
FROM _ a
JOIN _ b USING (fc_time, yr, "STORMNUM", basin)
WHERE b.tau = 0 AND a.tau <> b.tau
ORDER BY 1,2)
SELECT * FROM __;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time, geom::geography
FROM cyclonic.nhc_data
WHERE "STORMNUM" = 18 AND yr = 2021 AND bulk->>'BASIN' = 'AL'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _,
__ AS (
SELECT
yr, "STORMNUM", basin, a.fc_time, a.tau ppt,a."ADVISNUM", b."ADVISNUM", (st_distance(a.geom, b.geom)/1000)::int dist
FROM _ a
JOIN _ b USING (fc_time, yr, "STORMNUM", basin)
WHERE b.tau = 0 AND a.tau <> b.tau
ORDER BY 1,2)
SELECT * FROM __;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time, geom::geography
FROM cyclonic.nhc_data
WHERE "STORMNUM" = 18 AND yr = 2021 AND bulk->>'BASIN' = 'AL'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _,
__ AS (
SELECT
yr, "STORMNUM", basin, a.fc_time, a.tau ppt,a."ADVISNUM", b."ADVISNUM", (st_distance(a.geom, b.geom)/1000)::int dist
FROM _ a
JOIN _ b USING (fc_time, yr, "STORMNUM", basin)
WHERE b.tau = 0 AND a.tau <> b.tau
ORDER BY 1,2)
SELECT * FROM __;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time, geom::geography
FROM cyclonic.nhc_data
WHERE "STORMNUM" = 18 AND yr = 2021 AND bulk->>'BASIN' = 'AL'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _,
__ AS (
SELECT
yr, "STORMNUM", basin, a.fc_time, a.tau ppt,a."ADVISNUM", b."ADVISNUM", (st_distance(a.geom, b.geom)/1000)::int dist
FROM _ a
JOIN _ b USING (fc_time, yr, "STORMNUM", basin)
WHERE b.tau = 0 AND a.tau <> b.tau
ORDER BY 1,2)
SELECT * FROM __;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time, geom::geography
FROM cyclonic.nhc_data
WHERE "STORMNUM" = 18 AND yr = 2021 AND bulk->>'BASIN' = 'AL'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _,
__ AS (
SELECT
yr, "STORMNUM", basin, a.fc_time, a.tau ppt,a."ADVISNUM", b."ADVISNUM", (st_distance(a.geom, b.geom)/1000)::int dist
FROM _ a
JOIN _ b USING (fc_time, yr, "STORMNUM", basin)
WHERE b.tau = 0 AND a.tau <> b.tau
ORDER BY 1,2)
SELECT * FROM __;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time, geom::geography
FROM cyclonic.nhc_data
WHERE "STORMNUM" = 18 AND yr = 2021 AND bulk->>'BASIN' = 'AL'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _,
__ AS (
SELECT
yr, "STORMNUM", basin, a.fc_time, a.tau ppt,a."ADVISNUM", b."ADVISNUM", (st_distance(a.geom, b.geom)/1000)::int dist
FROM _ a
JOIN _ b USING (fc_time, yr, "STORMNUM", basin)
WHERE b.tau = 0 AND a.tau <> b.tau
ORDER BY 1,2)
SELECT * FROM __;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time, geom::geography
FROM cyclonic.nhc_data
WHERE "STORMNUM" = 18 AND yr = 2021 AND bulk->>'BASIN' = 'AL'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _,
__ AS (
SELECT
yr, "STORMNUM", basin, a.fc_time, a.tau ppt,a."ADVISNUM", b."ADVISNUM", (st_distance(a.geom, b.geom)/1000)::int dist
FROM _ a
JOIN _ b USING (fc_time, yr, "STORMNUM", basin)
WHERE b.tau = 0 AND a.tau <> b.tau
ORDER BY 1,2)
SELECT * FROM __;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time, geom::geography
FROM cyclonic.nhc_data
WHERE "STORMNUM" = 18 AND yr = 2021 AND bulk->>'BASIN' = 'AL'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _,
__ AS (
SELECT
yr, "STORMNUM", basin, a.fc_time, a.tau ppt,a."ADVISNUM", b."ADVISNUM", (st_distance(a.geom, b.geom)/1000)::int dist
FROM _ a
JOIN _ b USING (fc_time, yr, "STORMNUM", basin)
WHERE b.tau = 0 AND a.tau <> b.tau
ORDER BY 1,2)
SELECT ppt, avg(dist)::int FROM __
GROUP BY ppt;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time, geom::geography
FROM cyclonic.nhc_data
WHERE "STORMNUM" = 18 AND yr = 2021 AND bulk->>'BASIN' = 'AL'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _,
__ AS (
SELECT
yr, "STORMNUM", basin, a.fc_time, a.tau ppt,a."ADVISNUM", b."ADVISNUM", (st_distance(a.geom, b.geom)/1000)::int dist
FROM _ a
JOIN _ b USING (fc_time, yr, "STORMNUM", basin)
WHERE b.tau = 0 AND a.tau <> b.tau
ORDER BY 1,2)
SELECT ppt, avg(dist)::int FROM __
GROUP BY ppt;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time, geom::geography
FROM cyclonic.nhc_data
WHERE bulk->>'TIMEZONE' <> 'CVT'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _,
__ AS (
SELECT
yr, "STORMNUM", basin, a.fc_time, a.tau ppt,a."ADVISNUM", b."ADVISNUM", (st_distance(a.geom, b.geom)/1000)::int dist
FROM _ a
JOIN _ b USING (fc_time, yr, "STORMNUM", basin)
WHERE b.tau = 0 AND a.tau <> b.tau
ORDER BY 1,2)
SELECT ppt, avg(dist)::int FROM __
GROUP BY ppt;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
-- Pull hurricane forecast data from NHC archive
DO $body$
DECLARE
rec record;
tbl_name text;
yr int;
url text DEFAULT 'https://www.nhc.noaa.gov/gis/archive_forecast_results.php';
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
CREATE TABLE IF NOT EXISTS cyclonic.file_status (filename text primary key, msg text, good bool);
FOR rec IN SELECT (regexp_matches((http_get(url)).content, '(?<=/)(\w+.zip)','g'))[1] zip ORDER BY 1 LOOP
RAISE NOTICE '%', rec.zip;
BEGIN
EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
rec.zip,
'/vsizip/vsicurl/https://www.nhc.noaa.gov/gis/forecast/archive/' || rec.zip,
'ESRI Shapefile'
);
EXECUTE format(
$$IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic2 OPTIONS (
launder_table_names 'false', launder_column_names 'false');$$,
rec.zip
);
yr = substr(rec.zip,5,4); -- The year is not in the table
SELECT split_part(ftoptions[1], '=', 2) FROM pg_catalog.pg_foreign_table
WHERE ftoptions[1] ~ 'pts' INTO tbl_name;
IF (SELECT True FROM pg_catalog.pg_class WHERE relname = 'nhc_data') THEN
EXECUTE format('INSERT INTO cyclonic2.nhc_data2 WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _;', tbl_name)
USING rec.zip, yr;
ELSE
EXECUTE format('CREATE TABLE cyclonic2.nhc_data2 AS WITH _ AS (SELECT * FROM cyclonic2.%I)
SELECT $1 filename, $2 yr, "STORMNUM", "ADVISNUM", "STORMTYPE", geom, to_jsonb(_.*) bulk FROM _ ', tbl_name)
USING rec.zip, yr;
END IF;
EXECUTE format('DROP SERVER %I CASCADE;', rec.zip);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '% failed. reason: %', rec.zip, SQLERRM;
INSERT INTO cyclonic.file_status VALUES (rec.zip, SQLERRM, NULL);
COMMIT;
CONTINUE;
END;
COMMIT;
END LOOP;
COPY (
WITH _ AS (
SELECT yr, "ADVISNUM", "STORMNUM", bulk->>'STORMNAME' nom, (bulk->>'TAU')::int tau, bulk->>'BASIN' basin,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3)
WHEN char_length(bulk->>'ADVDATE') = 11 THEN to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI')
ELSE null END advisory_time,
CASE WHEN char_length(bulk->>'ADVDATE') = 26 THEN
to_timestamp('0'||(bulk->>'ADVDATE'), 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 27 THEN
to_timestamp(bulk->>'ADVDATE', 'HHMI AM "AST" DY MON DD YYYY')::timestamp
AT TIME ZONE split_part(bulk->>'ADVDATE',' ',3) + (bulk->>'TAU'||' hours')::interval
WHEN char_length(bulk->>'ADVDATE') = 11 THEN
to_timestamp(bulk->>'ADVDATE','YYMMDD/HH24MI') + (bulk->>'TAU'||' hours')::interval
ELSE null END fc_time, geom::geography
FROM cyclonic.nhc_data
WHERE bulk->>'TIMEZONE' <> 'CVT'
ORDER BY bulk->>'BASIN', yr, "STORMNUM",
CASE
WHEN "ADVISNUM" ~* 'A' THEN replace("ADVISNUM",'A','.1')::float
WHEN "ADVISNUM" ~* 'B' THEN replace("ADVISNUM",'B','.2')::float
ELSE "ADVISNUM"::float
END, (bulk->>'TAU')::int)
SELECT * FROM _,
__ AS (
SELECT
yr, "STORMNUM", basin, a.fc_time, a.tau ppt,a."ADVISNUM", b."ADVISNUM", (st_distance(a.geom, b.geom)/1000)::int dist
FROM _ a
JOIN _ b USING (fc_time, yr, "STORMNUM", basin)
WHERE b.tau = 0 AND a.tau <> b.tau
ORDER BY 1,2)
SELECT ppt, avg(dist)::int FROM __
GROUP BY ppt
) TO '/arbitrarydata/hurricane_accuracy_in_one_call_to_the_db.csv;
END;
$body$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting
COPY 10
Time: 916.568 ms
cyclonic@localhost:5432/cyclonic|1|# 🐘\q
rhys@liguanea:~/dev/cyclonic$
🐘