pull_nhc_kmz_data.sql

-- Pull hurricane forecast data from NHC archive
DO $$
  DECLARE
    rec record;
    new_name 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+.TRACK.kmz') kmz FROM contents 
      WHERE content_by_row ~ '[A-z]\w+.TRACK.kmz"'  LOOP

      new_name := lower(split_part(rec.kmz[1],'.',1)); /*1*/
      RAISE NOTICE '%::%::%', rec.row_number, rec.kmz, new_name;
      EXECUTE format('CREATE SERVER %I FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource %L, format %L);',
	new_name,
        '/vsicurl/https://www.nhc.noaa.gov/storm_graphics/api/' || rec.kmz[1],
	'LIBKML'
	);

      EXECUTE format('IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "%s" INTO cyclonic;', new_name);
      IF rec.row_number = 1 THEN
	EXECUTE format('CREATE TABLE cyclonic.nhc_kmz_data AS SELECT * FROM cyclonic.forecast_track;');
      ELSE
	EXECUTE format('INSERT INTO cyclonic.nhc_kmz_data SELECT * FROM cyclonic.forecast_track;');
      END IF;

      EXECUTE format('DROP SERVER %I CASCADE;', new_name);    
      IF rec.row_number % 100 = 0 THEN
	COMMIT;
      END IF;
    END LOOP;

  END;
$$
-- 1: currently ogr_fdw isn't happy when given a server name that needs quoting