Center for Disease Control (2020). 2020 Massachusetts CDC Social Vulnerability Index (tracts) [gis dataset]. CDC. https://www.atsdr.cdc.gov/place-health/php/svi/svi-data-documentation-download.html. last updated: 0001-01-01 (update frequency: Never). Social vulnerability refers to the potential negative effects on communities caused by external stresses on human health. Such stresses include natural or human-caused disasters, or disease outbreaks. Reducing social vulnerability can decrease both human suffering and economic loss. The CDC/ATSDR Social Vulnerability Index (CDC/ATSDR SVI) uses 16 U.S. census variables to help local officials identify communities that may need support before, during, or after disasters. Key words: Massachusetts, CDC, Vulnerability, Social Vulnerability Index, Census Tracts, Public-Safety Open Geospatial Consortium Standards: dynamic vector tiles | | WMS (coming soon) | WFS (coming soon) Analytical Functions: gdsc_get_attributes(tablename=ma_2020_svi_tract,lat=25.718,lng=-80.279,identifier=location,normal='') ETL documentation

ETL for ma_2020_svi_tract

######### # GDSC ETL is performed in two steps: shell script to ETL data into postgres # and then additional transformations with SQL in postGIS (if needed). #### ######### # Step 1 - bash script (pseudo code) #### # create directory structure and move into it mkdir -p /data/ma_2020_svi_tract/{download,etl} && cd /data/ma_2020_svi_tract # set update flag based on last update and update frequency do_update=0 if date() < last_update + update_frequency else do_update = 1 # download if update flag is set: if do_update == 1 then wget -O download/ma_2020_svi_tract.zip 'https://svi.cdc.gov/Documents/Data/2020/db/states/Massachusetts.zip' # unzip to download directory if update flag is set if do_update == 1 then unzip -d download download/ma_2020_svi_tract.zip && rm download/ma_2020_svi_tract.zip # load into postGIS with: ogr2ogr -lco GEOMETRY_NAME=geom -f PostgreSQL PG:"" download/SVI2020_MASSACHUSETTS_tract.gdb -nlt multipolygon -nln ma_2020_svi_tract ######### # Step 2 - SQL script (pseudo code): #### # remove duplicate points and make geometries valid: UPDATE ma_2020_svi_tract SET geom=ST_MakeValid(ST_RemoveRepeatedPoints(geom)); # add local geometry column and reproject existing geometries into local EPSG: SELECT AddGeometryColumn ( 'ma_2020_svi_tract', 'geom_local', 26986, 'multipolygon', 2 ); UPDATE ma_2020_svi_tract SET geom_local=ST_MakeValid(ST_RemoveRepeatedPoints(ST_Transform(ST_Multi(geom),26986))); CREATE INDEX ma_2020_svi_tract_geom_local_idx ON ma_2020_svi_tract USING GIST (geom_local); NOTIFY pgrst, 'reload schema'; # Move into corrrect directory and create derivative directory in data package on osgeo cd /data/ma_2020_svi_tract/ mkdir -p derived # Move into correct directory cd /data/ma_2020_svi_tract/ # Create pg_dump of table SQL in derivate directory on postgis. pg_dump -h None -t ma_2020_svi_tract > derived/ma_2020_svi_tract.sql # Create downloadable tarfile of SQL in derivative directory on postgis rm -f derived/ma_2020_svi_tract.sql.tar.gz tar -czf derived/ma_2020_svi_tract.sql.tar.gz meta_dcat_ma_2020_svi_tract.json -C derived ma_2020_svi_tract.sql