Embedded Files
Raw Data:
311 Service Requests data from 2020 to present
(Complaint type, Descriptor, and Locations)
311 Service Requests from 2010 to Present | NYC Open Data
Street Construction Permits (Location and Streets, Permit Issued Date)
Street Construction Permits (2013-2021) | NYC Open Data
Street Construction Permits (2022-Present) | NYC Open Data
DOT Street Pavement Rating (Geometry Model, Street Information (on, from, to), Rating)
Street Pavement Rating | NYC Open Data
SCOUT Data (Extracted from 311 Service Requests, same metrics used)
Disadvantaged Communities Layer (NYS Disadvantaged Community Map)
Disadvantaged Communities Layer | NYSERDA
Comptroller’s Notice of Claims database
Clean Data: Google Drive
README:
After obtaining the datasets from the three data sources, we transformed the data in order to merge the datasets together. Several different steps were performed to clean, transform the data and prepare it for analysis, including: Removing duplicate data and unnecessary columns, and handling Missing Values.We used Jupyter Notebook to analyze datasets. We picked useful and excluded useless columns based on the requirement of the project and concatenated rows based on the time intervals and removed some noisy null values, which resulted in suitable datasets for visualization. However, we also figured out that it cost time and physical memory to upload and load big datasets with the Python library.From the OpenData website, we found that we could easily extract data by choosing specified columns and time intervals and the technique sitting in the back was an SQL-like manipulation mechanism, but it was a bit tricky to have a big picture of the whole dataset.In future projects, we would like to try other database management systems, such as MySQL or PostgreSQL to manipulate and clean datasets, since it is a little arduous to handle super big datasets with 10 GB using Python and Jupyter Notebook and it costs huge computer memory to load dataset with Python.Detailed instruction for Tableau (Selected):
We extracted 311 data, Street Pavement Rating data, street excavation permits from Street Construction data, Notice of Claims, Motor Vehicle Crashes data, and SCOUT data from the NYC OpenData website, see as Data (Note: SCOUT data was extracted from 311 data)Make connections between each dataset. For Street Pavement Rating data and Street Construction data, both datasets were built by the New York City Department of Transportation (DOT). They have the same column called OFTCode. Those codes can link two datasets together. Since SCOUT data is from 311 data, all columns in SCOUT are the same as 311, which was easier to connect all columns.
Use the Tableau map function to visualize data. The methodology of the MVP is to mark disruption locations on the map within the given time based on the pavement street map. The bottom layer is the pavement map, which contains pavement rating, street location, and excavation information. Pavement rating includes three levels with three colors: Good-orange, Fair-blue, and Poor-red. Ratings based on a scale from 1 to 10: Good is from 8 to 10, Fair is from 4 to 7, and Poor is from 1 to 3. Each street has location information: On Street, From Street, and To Street. Also, Each street shows information on the excavation type for excavation data, such as gas repair.
The methods to build 311, SCOUT, and crash layers are the same: using latitude and longitude to pinpoint the specific locations on the map. Dragging latitude and longitude data on each layer, using filters to create a time slider and disruption types option for each layer, and putting in disruption address data. Creating the time sliders was to see the change in disruption locations, and we set the sliders in terms of month/year. The disruption types option helps users check different types of disruption. For example, when clicking on the water leak option, users can see where a water leak is at that given time. When using time sliders, users can see the change in water leak locations.
Detailed instruction for ArcGIS (Selected):
We ‘Bookmarked’ Queens using the latest version of “Map Viewer” - as “Map Viewer Classic” hindered some of the upcoming features.
We added the layer “County Subdivisions” using the ‘Living Atlas’ repository, which enabled a layer of various county subdivisions across the country. We added a filter wherein the ‘Name’ is ‘Queens Borough’, which highlights our area of focus.
Next, we started adding the filtered datasets by clicking on ‘Add’ > ‘Add Layer From File’ > [Selecting the appropriate datasets].
Once the datasets got uploaded, we used ArcGIS credits to ‘GeoCode’ the data based on a variety of attributes, ranging from the basic ‘Latitude’ and ‘Longitude’, to ‘Street Name’, ‘To Street’, ‘From Street’, ‘House Number’, ‘Borough’, ‘Neighbourhood’ and so on. The more complex attributes require geocoding, which is a premium feature requiring credits.
Upon selecting geocoded attributes, ArcGIS proceeds to ‘Add a layer’ on top of our base map, which we further filtered to show only points of interest, such as -
Street Pavement Rating : [Style > Red - POOR ] ; [Filter > ‘Rating Layer’ is ‘POOR’] ; [Effect > ‘Bloom + Gray’]
FOIL 2018 to 2022 : [Style > Red - Sewer Overflow, Blue - Watermain Break] ; [Filter > ‘Borough’ is ‘Queens’] ; [Effect > ‘Bloom’]
311 Data : [Style > Red - Noise, Blue - Water System, Turquoise - Appliances, Purple - Water Conservation, Orange - Electrical, Yellow - Indoor Sewage, Pink - Street Light Condition, Brown - IA & D] ; [Effect > ‘Brightness & Contrast’]
Motor Vehicle Crashes : [Style > Location (Single Symbol)] ; [Effect > ‘Bloom’]
One can view these layers on the map by toggling the ‘Show/Hide layer’ button. Once this view is ready on the map, we went ahead with creating a ‘WebApp’ by using the ‘Web Mapping Application’ under the ‘ArcGIS WebApp Builder’ feature. This is an essential step to create a dashboard, especially since we want a timeline slider alongside a layer toggler in a cleaner & un-editable format. We selected the ‘Plateau Theme’, for its simple and straightforward aesthetic. Next, we went under the ‘Widgets’ tab and added the following widgets -
Legend : Shows Legends for Layers, updates automatically
Layer List : Gives the user an option to choose which layers will be shown on the list. Also enables zoom functions, transparency, setting visibility range, enabling popups, descriptions etc.
Time Slider : Using ‘Map Default’ time format, the time slider widget adds a movable ‘Time Slider’ - wherein we can see how the map changes on a monthly bases. The time window within the slider can be adjust.
Fig. 1 Widgets in Dashboard
NOTE : One key aspect in order to utilize the time slider is to go into each layer’s settings and enabling time. To enable time on hosted feature layers, the hosted feature layer owner or an administrator can follow these steps:
On the My Content tab of the content page, open the item page for a hosted feature layer with temporal data.
On the Overview tab, scroll to the Layers section and click the name of the layer to open its details page.
On the layer's Overview tab, click Edit under Time Settings.
The Time Settings window appears.
Check the Enable time check box.
Choose specific events in time or time ranges with a start and end time to record the time data.
Choose the time field or fields in the data.
Click OK.
One can see the default view of the web app in Fig. 2, which showcases key points of interest in the borough of Queens with a legend on the right side of the application. Users can adjust the time slider in order to view the instances based upon user preferences, and the layers change dynamically.
Fig. 2 Web App View of the MVP
The highlighted points can easily be differentiated by referring to the legend of layers on the right side of the web application, as seen in Fig. 2.
Upon clicking on any highlighted point, users will be able to see the complaint type, created date as well as a brief description, as shown in Fig. 3.
Additionally, the user can adjust the time slider to cover any time between 1 - 48 months and ‘move’ the slider window to highlight different incidents around the city, as seen in Fig. 4.
Fig. 3 An Electrical Wiring Complaint
Fig. 4 Time slider with Noise Complaint Highlight
The map updates the key points of interest accordingly, and mimics a ‘heatmap’ - settings of the same visualization can easily be altered by changing the base map legend.
Page updated
Report abuse