RDS Database

Build Database

Initialize Database

Create Database with Postgis

psql --host seattle-emergency.cwydhyrq6asz.us-west-2.rds.amazonaws.com --port 5432 --username codefellows --password --dbname postgres
CREATE DATABASE seattle;
CREATE EXTENSION postgis;
\q

Rebuild Table

Create Incidents Table

git checkout develop
cd <something>/project/data
psql --host seattle-emergency.cwydhyrq6asz.us-west-2.rds.amazonaws.com --port 5432 --username codefellows --password --dbname seattle
DROP TABLE incidents;
/q
psql --host seattle-emergency.cwydhyrq6asz.us-west-2.rds.amazonaws.com --port 5432 --username codefellows --password --dbname seattle -a -f create_incidents_table.sql 

Add Incident Data

git checkout develop
cd <something>/seattle/
fab deploy
ssh -i ~/.ssh/seattle_emergency.pem [email protected]
cd ~/seattle_emergency/data/
psql --host seattle-emergency.cwydhyrq6asz.us-west-2.rds.amazonaws.com --port 5432 --username codefellows --password --dbname seattle
\copy incidents(units,date_time,incident_type,address,incident_number,latitude,longitude,major_category,minor_category) FROM 'datav2_incident_cat.csv' DELIMITERS ',' CSV QUOTE AS E'"';
\q

Create Point Geometry

cd <something>/project/data
psql --host seattle-emergency.cwydhyrq6asz.us-west-2.rds.amazonaws.com --port 5432 --username codefellows --password --dbname seattle -a -f create_incidents_point_geom.sql
\q

Create Neighborhood Table and Add Data

git checkout develop
cd <something>/project/data/
shp2pgsql -I -s 4326 ZillowNeighborhoods-WA/ZillowNeighborhoods-WA.shp  neighborhoods_wa | psql --host seattle-emergency.cwydhyrq6asz.us-west-2.rds.amazonaws.com --port 5432 --username codefellows --password --dbname seattle