Instructions for getting Hurricane Sandy and US County shapefiles into a PostGIS database to identify affected counties

create.sh

#!/bin/bash

createdb gis_db
psql -d gis_db -c "CREATE EXTENSION postgis"

import.sh

#!/bin/bash

# Add US County data to Postgis w/ GIST index
curl https://www2.census.gov/geo/tiger/TIGER2019/COUNTY/tl_2019_us_county.zip --output tl_2019_us_county.zip
unzip tl_2019_us_county.zip
shp2pgsql tl_2019_us_county/tl_2019_us_county.shp tl_2019_us_county | psql -d gis_db
psql -d gis_db -c "CREATE INDEX tl_2019_us_county_geom_gix ON tl_2019_us_county USING GIST (geom)"

# Add Hurricane Sandy Radii data to Postgis w/ GIST index
curl https://www.nhc.noaa.gov/gis/best_track/al182012_best_track.zip --output al182012_best_track.zip
unzip al182012_best_track.zip
shp2pgsql al182012_best_track/al182012_radii.shp al182012_radii| psql -d gis_db
psql -d gis_db -c "CREATE INDEX al182012_radii_geom_gix ON al182012_radii USING GIST (geom)"

intersection-query.sql

SELECT row_to_json(fc) AS json_out FROM (
    SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
    FROM (
        SELECT 'Feature' As type,
               ST_AsGeoJSON(c.geom)::json As geometry,
               ROW_TO_JSON((
                SELECT l FROM (
                    SELECT r.radii,
                           r.synoptime,
                           r.basin,
                           r.stormnum,
                           r.ne,
                           r.se,
                           r.sw,
                           r.nw,
                           r.gid AS id,
                           c.geoid AS fips
                ) As l)) As properties
        FROM tl_2019_us_county as c, al182012_radii as r
        WHERE ST_Intersects(c.geom, ST_MakeValid(r.geom))
    ) f
) fc