This repository contains two .sh files that will allow you to create both
a SQLite and PostgreSQL database from files from the City of Chicago's open data portal.
# install necessary packages
# note: takes about 2 minutes
sh install_packages.sh
# download chicago data sets
# note: takes about 3 minutes
sh create_chicago_database.shThe create_chicago_database.sh script creates both a write_data/chicago.db and postgresql:///chicago database to be used with either SQLite or PostgreSQL.
Here is more information regarding the different tables that make up the chicago database:
| Table Name | Description | Documentation |
|---|---|---|
census_tracts_2010 |
2010 census tracts boundaries in Chicago, IL. | https://data.cityofchicago.org/Facilities-Geographic-Boundaries/Boundaries-Census-Tracts-2010/5jrd-6zik |
community_areas |
Current 77 Chicago community areas (CCAs). Note: these 77 CCAs are well-defined, static, and do not overlap. Census data are tied to the CCAs, and they serve as the basis for a variety of urban planning initiatives on both the local and regional levels. | https://data.cityofchicago.org/Facilities-Geographic-Boundaries/Boundaries-Community-Areas-current-/cauq-8yn6 |
cps_dropout_rate_2011_2019 |
The five-year cohort dropout rate follows a group of students who enter Chicago Public Schools (CPS) high schools as freshmen and calculates the percent of these students who drop out within five years after their freshman year. This table contains the dropout rates for each school year from 2011 to 2019. Note: unfortunately, some schools have been closed since 2011. To verify if a school is closed, please check the status_as_of_2019 column. |
CPS Data and Source |
cps_sy1819_cca |
School profile information for all schools in the Chicago Public School district for the school year 2018-2019. | https://data.cityofchicago.org/Education/Chicago-Public-Schools-School-Profile-Information-/kh4r-387c |
crimes_2019 |
This dataset reflects reported incidents of crime (with the exception of murders where data exists for each victim) for the year 2019. | https://data.cityofchicago.org/Public-Safety/Crimes-2019/w98m-zvie |
food_inspections |
This information is derived from inspections of restaurants and other food establishments in Chicago from January 1, 2010 to the present. Inspections are performed by staff from the Chicago Department of Public Health’s Food Protection Program using a standardized procedure. The results of the inspection are inputted into a database, then reviewed and approved by a State of Illinois Licensed Environmental Health Practitioner (LEHP). | https://data.cityofchicago.org/Health-Human-Services/Food-Inspections/4ijn-s7e5/data |
il_wac_s000_jt00_2017 |
Workplace Area Characteristic data for IL in 2017 that counts the total number of jobs for workers in all jobs by Census Block. | LEHD Data & IL 2017 WAC Data |
il_xwalk |
Geographic crosswalk data used to help aggregate census blocks up to census tracts, zip codes, counties, and states. | LEHD Data & IL 2017 Geographic Crosswalk Data |
-
In the
cps_dropout_rate_2011_2019table, count how many records appear for eachschool_year. Note: it is helpful to include theschool_yearcolumn and to order the results by it as well. -
Identify the schools and their community area whose dropout rate in school year 2019 is greater than or equal to 25 percent.
-
Identify the top 10 community areas that have the highest number of crimes in 2019.
-
Identify the top 10 community areas that have the highest number of jobs in 2017.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent