Instructions for getting Hurricane Sandy and US County shapefiles into a PostGIS database to identify affected counties
#!/bin/bash
createdb gis_db
psql -d gis_db -c "CREATE EXTENSION postgis"
#!/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)"
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