🚓 UCPD Hyde Park Crime Data Explorer

This project is a full-stack tool for exploring University of Chicago Police Department (UCPD) crime data in Hyde Park. It features a MySQL database, a Flask REST API, and an interactive Streamlit dashboard for data analysis and visualization.

---

📁 Project Structure

• hydePark_crime.sql – Main database schema (tables for crime, weather, locations, etc.)
• dump.sql – Data import for the database
• config.py – Database connection configuration
• db.py – Database connection logic
• app.py – Flask API server entry point
• routes.py – All API endpoints (/query/1 to /query/16, plus insert/count routes)
• query_service.py – SQL query logic for all endpoints
• streamlit_app.py – Streamlit dashboard (GUI)
• requirements.txt – Python dependencies

---

🚀 Getting Started

1. Set up the MySQL database

mysql -u root -p
SOURCE path/to/hydePark_crime.sql;
SOURCE path/to/dump.sql;

2. Configure database connection

Edit config.py with your MySQL credentials:

DB_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'your_mysql_user',
    'password': 'your_mysql_password',
    'database': 'UCPD_HydePark_CrimeDB'
}

3. Install Python dependencies

pip install -r requirements.txt

4. Start the backend API

python app.py

• The API will be available at http://localhost:5000/

5. Launch the Streamlit dashboard

streamlit run streamlit_app.py

• The dashboard will open in your browser (default: http://localhost:8501/).

---

🗄️ Database Schema

The main tables are:

• CommunityArea: Community metadata
• WeatherSnapshot: Weather data by date
• PoliceJurisdiction: Police district/beat/ward info
• Location: Crime locations
• CrimeType: Crime classification
• Crime: Main crime records (with year, date, location, etc.)
• Crime_Classified: Many-to-many relationship between crimes and types

See hydePark_crime.sql for full schema details.

---

🔌 API Endpoints

All endpoints accept POST requests with JSON bodies (unless otherwise noted).

Endpoint	Description	Parameters (JSON)
/query/1	Total crimes per year or specific year	year (optional)
/query/2	Top N crime types in a year	year, top_n, fields
/query/3	Top N arrest blocks by year	year, top_n
/query/4	Top N domestic crime locations	year, top_n
/query/5	Frequent crime types + descriptions	year, top_n, fields
/query/6	Crime descriptions grouped by year	year, fields
/query/7	Crime counts by average temperature	year
/query/8	Top N blocks with most crimes	year, top_n, fields
/query/9	Crimes per jurisdiction by year	year
/query/10	Crimes by weekday	year, fields
/query/11	Top night crime types (6PM–6AM)
/query/12	Jurisdictions with most winter night crimes
/query/13	Assault trends across communities
/query/14	Snowy days with highest crime counts
/query/15	Crimes by temperature range
/query/16	Violent crimes on hot vs. cold days

Additional endpoints for inserting and counting records:

• /insert/community (POST)
• /insert/weather (POST)
• /count/community (GET)
• /count/weather (GET)

---

🖥️ Streamlit Dashboard

• Interactive GUI for running all queries and visualizing results
• Select queries, input parameters, and view results as tables and charts
• Troubleshooting tips and help available in the sidebar

Insert Data

The Streamlit GUI now supports inserting data into the database for:

• WeatherSnapshot: Insert a new WeatherSnapshot record with fields such as Station, Station Name, Date, Precipitation, Snowfall, Snow Depth, Average Temperature, Max Temperature, and Min Temperature.

To use the insert functionality:

1. Navigate to the "Insert Data" tab in the Streamlit GUI.
2. Fill in the required fields in the form for WeatherSnapshot.
3. Click the "Insert" button to submit the data to the backend.

---

🛠️ Development Notes

• All backend logic is in query_service.py
• API routes are defined in routes.py and registered in app.py
• Database connection is managed via db.py and config.py
• The dashboard communicates with the backend via HTTP requests

---

🧑‍💻 Authors & Contact

For questions or contributions, please contact the project team or open an issue.