search

opensandiego / sd-sweepaware

City of San Diego streetsweeping SMS alerts
http://opensandiego.org
10 stars 11 forks source link

Add Data for District 1 #4

Closed milafrerichs closed 9 years ago

milafrerichs commented 9 years ago

What does this PR do?

Adds csv data and GeoJSON data for sweeping schedules in district 1. I've done it for District 1 and now it's easy to do it for the other districts. I will create another directory/or repo with how to do it.

Screenshot

showing monthly schedules for the streets (every month, odd month, even month) bildschirmfoto 2015-06-21 um 14 20 05

Data Columns

Name Explanation Example
StreetName the name of the street 08TH ST
BlockRange1 house numbers start 450
BlockRange2 house numbers end 619
Limits limit column from dataset CITY BOUNDARY - CONDESA DR
Posted is a time set Posted/Not Posted
Schedule 2 raw not posted data Both Sides Odd Month 4th Wed
Both Sides are both sites swept? Y
Month which month (odd, even, every) Odd
Day which day of the month 4th Wed
Time if schedule is posted which time 7am - 11am
ES schedule for ES (east side?) 1st & 3rd Thu
WS schedule for west side 2nd & 4th Thu
SS schedule for south side
NS schedule for north side

Steps for cleaning/creating data

  1. Extract Tables/CSV from PDF with Tabula
  2. Split Schedule column with OpenRefine (see refine.json )
  3. Download Road Data from SANDAG
  4. Upload CSV and Road Data to POSTGIS
  5. Create new table by matching road names and street numbers with the ones from SANDAG
  6. create shp file from POSTGIS data
  7. create GeoJSON from shp file
cgiovando commented 9 years ago

Thanks @milafrerichs - this looks great! Now go tell @jerryHall who spent nine hours to extract data manually from each PDF ;-)

One question: does it make sense to keep data organized by district, or shall we just have one single database for the city?

We also need to define the app architecture and workflow more clearly. At the last meeting we discussed using citygram-like services for doing both the sweepaware and open-dsd notifications. Does that make sense?