merge_ntl.sh

results=$(ls -p *2016.zip | grep -v /)

for r in $results; do
  ogr2ogr -f "geojson" ntl16.geojson "/vsizip/"$r -update -append -nln state  \
  -t_srs EPSG:4326 \
    -dialect "SQLite" -sql \
   "SELECT '${r:0:2}' AS state, G16PRERTru as pres_16_R, G16PREDCli as pres_16_D,
    Geometry FROM ${r%.*}"
  done;

# append NY

ogr2ogr -f "geojson" ntl16.geojson "projects/NY/NY_State_Merged.geojson" -update -append -nln state  \
  -dialect "SQLite" -sql \
 "SELECT 'ny' AS state, republican as pres_16_R, democratic as pres_16_D,
  Geometry FROM NY_State_Merged";

ogr2ogr -f "geojson" ntl16.geojson "/vsizip/va_2016.zip" -update -append -nln state  \
-t_srs EPSG:4326 \
  -dialect "SQLite" -sql \
 "SELECT 'va' AS state, G16PRERTru as pres_16_R, G16PREDCli as pres_16_D,
  Geometry FROM va_2016_president";
# md

ogr2ogr -f "geojson" ntl16.geojson "/vsizip/md_2016.zip" -update -append -nln state  \
-t_srs EPSG:4326 \
-dialect "SQLite" -sql \
"SELECT 'md' AS state, G16PRERTru as pres_16_R, G16PREDCli as pres_16_D,
    Geometry FROM md_2016_w_ushouse";

# append ohio

ogr2ogr -f "geojson" ntl16.geojson "oh_2016/OH_precincts.shp" -update -append -nln state  \
-t_srs EPSG:4326 \
  -dialect "SQLite" -sql \
 "SELECT 'oh' AS state, PRES16R as pres_16_R, PRES16D as pres_16_D,
  Geometry FROM OH_precincts";



# append PA

ogr2ogr -f "geojson" ntl16.geojson "pa_2016/PA_VTD_PLANS.shp" -update -append -nln state  \
-t_srs EPSG:4326 \
  -dialect "SQLite" -sql \
 "SELECT 'pa' AS state, T16PRESR as pres_16_R, T16PRESD as pres_16_D,
  Geometry FROM PA_VTD_PLANS";

# AL

ogr2ogr -f "geojson" ntl16.geojson "/vsizip/2008/AL_Shapefile.zip" -update -append -nln state  \
-t_srs EPSG:4326 \
  -dialect "SQLite" -sql \
 "SELECT 'al' AS state, USP_R_08 as pres_16_R, USP_D_08 as pres_16_D,
  Geometry FROM al_final";

# CT

ogr2ogr -f "geojson" ntl16.geojson "/vsizip/2008/CT_Shapefile.zip" -update -append -nln state  \
-t_srs EPSG:4326 \
  -dialect "SQLite" -sql \
 "SELECT 'ct' AS state, REPUBLICAN as pres_16_R, DEMOCRAT as pres_16_D,
  Geometry FROM ct_final";

# MS

ogr2ogr -f "geojson" ntl16.geojson "/vsizip/2008/MS_Shapefile.zip" -update -append -nln state  \
-t_srs EPSG:4326 \
  -dialect "SQLite" -sql \
 "SELECT 'ms' AS state, USP_R_08 as pres_16_R, USP_D_08 as pres_16_D,
  Geometry FROM ms_final"


# NJ


ogr2ogr -f "geojson" ntl16.geojson "/vsizip/NJ_Shapefile.zip" -update -append -nln state  \
-t_srs EPSG:4326 \
  -dialect "SQLite" -sql \
 "SELECT 'nj' AS state, USP_RV_08 as pres_16_R, USP_DV_08 as pres_16_D,
  Geometry FROM nj_final";

# SD

ogr2ogr -f "geojson" ntl16.geojson "/vsizip/SD_Shapefile.zip" -update -append -nln state  \
-t_srs EPSG:4326 \
  -dialect "SQLite" -sql \
 "SELECT 'sd' AS state, USP_R_08 as pres_16_R, USP_D_08 as pres_16_D,
  Geometry FROM sd_final";



  ogr2ogr -f "geojson" ntl16.geojson "/vsizip/ID_Shapefile.zip" -update -append -nln state  \
  -t_srs EPSG:4326 \
    -dialect "SQLite" -sql \
   "SELECT 'id' AS state, USP_R_08 as pres_16_R, USP_D_08 as pres_16_D,
    Geometry FROM id_final";