forked from toddwschneider/nyc-taxi-data
-
Notifications
You must be signed in to change notification settings - Fork 0
/
initialize_database.sh
executable file
·23 lines (17 loc) · 1.21 KB
/
initialize_database.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#!/bin/bash
createdb nyc-taxi-data
psql nyc-taxi-data -f setup_files/create_nyc_taxi_schema.sql
shp2pgsql -s 2263:4326 taxi_zones/taxi_zones.shp | psql -d nyc-taxi-data
psql nyc-taxi-data -c "CREATE INDEX ON taxi_zones USING gist (geom);"
psql nyc-taxi-data -c "CREATE INDEX ON taxi_zones (locationid);"
psql nyc-taxi-data -c "VACUUM ANALYZE taxi_zones;"
shp2pgsql -s 2263:4326 nyct2010_15b/nyct2010.shp | psql -d nyc-taxi-data
psql nyc-taxi-data -f setup_files/add_newark_airport.sql
psql nyc-taxi-data -c "CREATE INDEX ON nyct2010 USING gist (geom);"
psql nyc-taxi-data -c "CREATE INDEX ON nyct2010 (ntacode);"
psql nyc-taxi-data -c "VACUUM ANALYZE nyct2010;"
psql nyc-taxi-data -f setup_files/add_tract_to_zone_mapping.sql
cat data/fhv_bases.csv | psql nyc-taxi-data -c "COPY fhv_bases FROM stdin WITH CSV HEADER;"
weather_schema="station_id, station_name, date, average_wind_speed, precipitation, snowfall, snow_depth, max_temperature, min_temperature"
cat data/central_park_weather.csv | psql nyc-taxi-data -c "COPY central_park_weather_observations (${weather_schema}) FROM stdin WITH CSV HEADER;"
psql nyc-taxi-data -c "UPDATE central_park_weather_observations SET average_wind_speed = NULL WHERE average_wind_speed = -9999;"