Adjust data cleaning script to prune data points outside of LA Neighborhood Districts

ryanfchase commented 7 months ago

Dependency

[ ] obtain 2024 or most recent Boundaries JSON file

Overview

We need to remove 311 Data service requests on our map that do not fall within the boundaries of a Neighborhood Council since they are inaccessible and mostly confuse the users

Action Items

[x] write python script that takes as input a CSV with a subset of 311 dataset (see example dataset below) that identifies 311 requests that do not fall within a Neighborhood Council boundary (provide proof of concept)
[x] report on this ticket how many 2024 service requests fall outside NC boundaries
[ ] incorporate the functionality into our daily hugging face cron job @ updateHfDataset.py
Resources/Instructions

Screenshot of requests outside NC boundaries

![image](https://github.com/hackforla/311-data/assets/6414668/4311704d-814d-461b-8abc-2963f5c4ed93)

Last Updated Date 10/23/2024:

Initial data shape: (1165772, 34)
Filtered data shape: (1145408, 43)
Outside boundary data shape: (23138, 43)

Useful Links

DBeaver: https://dbeaver.io/
DBeaver + DuckDb: https://duckdb.org/docs/guides/sql_editors/dbeaver.html
Example dataset (2022 311 data): https://data.lacity.org/City-Infrastructure-Service-Requests/MyLA311-Service-Request-Data-2022/i5ke-k6by/about_data
JSON of Neighborhood Council boundaries (from our repo): https://raw.githubusercontent.com/hackforla/311-data/main/data/nc-boundary-2019-modified.json
DuckDb + Python: https://duckdb.org/docs/api/python/overview.html
Duckdb Spatial Extension: https://duckdb.org/docs/extensions/spatial

ryanfchase commented 7 months ago

Adjusting this ticket, most likely @mru-hub will pick this up once there is enough instructions to get started

ryanfchase commented 6 months ago

Note: I'm realizing that we may need to do work to prune old data. Adding a check into our cleaning logic will simply stop new data (e.g. requests falling outside NC boundaries) from being added -- we'll still need to handle old data that has the same problem.

Follow up ticket: Make sure we are cleaning 2023 and prior data with the same logic

ryanfchase commented 6 months ago

Note for Ryan: provide an example resource of checking if a Lat/Long is within a provided boundary in Duckdb

ryanfchase commented 6 months ago

This ticket is ready to be picked up

ryanfchase commented 5 months ago

@mru-hub's update from this previous week is on the PR: https://github.com/hackforla/311-data/pull/1736#issuecomment-2164259492

ryanfchase commented 5 months ago

Latest PR: https://github.com/hackforla/311-data/pull/1744

ryanfchase commented 2 months ago

Update: added dependency for needing 2024 NC boundary json file

hackforla / 311-data