Hurricane Prediction Accuracy Analysis Using the PRAM Stack

Hurricane Prediction Accuracy Analysis Using the PRAM Stack

@rhysallister

Hurricanes

PRAM Stack

Code

& Why predicting their path is important

A deep dive. SQL Ahead. Beware.

What the dickens is this?

Hurricanes

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.

Hurricanes

Strong Winds

Coastal Flooding

Torrential Rain

Infrastructure Damage

| tl:dr

Infrastructure Damage

Hurricanes

Hurricanes

Typhoons

Typhoons

Typhoons

Cyclones

Cyclones

Prediction & Forecasting

Hurricane Season

JUN

1

NOV

30

Prediction & Forecasting

Hurricane Season

JUN

1

NOV

30

Prediction & Forecasting

Hurricane Season

JUN

1

NOV

30

Prediction & Forecasting

Hurricane Season

JUN

1

NOV

30

Prediction & Forecasting

Prediction & Forecasting

Prediction & Forecasting

Prediction & Forecasting

Prediction & Forecasting

Prediction & Forecasting

Prediction & Forecasting

It should be evident why knowing where a hurricane will potentially go is important. Preparedness is the best bet for averting disaster.

PRAM Stack

What is a Stack?

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.

LAMP:

Linux|

Apache|

MySQL|

PHP

Perl

Python

MEAN:

MongoDB|

Express.js|

Angular|

Node.js

T-REx:

TerminusDB|

React.js|

Express.js

JAM:

Javascript|

APIs|

Markup

PRAM:

PostGIS|

ogr_fdw|

pgsql_http

PRAM:

PostGIS|

ogr_fdw|

http

These are all PostgreSQL extensions written primarily by the same person

PRAM:

PostGIS|

ogr_fdw|

http

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$ 
🐘

Hurricane Predication Accuracy Analysis Using the PRAM Stack

By rhysallister

Made with Slides.com

Hurricane Predication Accuracy Analysis Using the PRAM Stack

  • 396

More from rhysallister