Final data is almost ready to be imported to CARTO and loaded into the app. Will need to update each of the SQL queries to reflect the changed schema for the crashes table.

• do sum(crash_count) for total crashes instead of doing count(cartodb_id) as total_crashes

• will have to do something like count(unnest(contributing_factor)) now that there is a single field for contributing factors that is of the type text array.

• For filtering by date range will have to select by year and month columns instead of using date_val column (relates to #36)

Things get squashed in the Stats Legend UI right now when width is < 1000px. Removing contributing factors & legend between 768px & 1000px will fix this.

For example prevent user from selecting a month earlier than August when year is 2011 and later than January when year is 2017. Being able to change the month to one that has no data won't update the data in the app and may confuse or mislead the user.

App currently says: "Map data last updated 12/31/2016", either remove or add a feature that alters this date when the ETL script runs and successfully updates the data on Carto.

So when sharing by URL the user can see the polygon that was previously drawn.

update README to include description of the project, that it's a React Redux app using CARTO, etc.

• When drawing a polygon a user should be able to cancel the draw. Switching to a different boundary filter should cancel the draw as well.

• After the map loads new data from drawing a polygon, a user should be able to draw another polygon.

To left of title

For example during drawing of custom boundary & selecting a filter by boundary polygon.

Currently the crash dot sizes are hard coded. It may make sense to compute equal interval or quantile breaks when the range of the crash count changes after a filter is applied.

For example, currently when all years of the dataset are selected the max crash count for one location is 415, but when a single year or single month is selected the max would be substantially lower.

So that application state can be shared via URL.

This Includes:

• Injury: Cyclist, Ped, Driver
• Fatality: " " "
• No Injury / Fatality
• Geo
• identifier
• lngLats

When a user toggles a boundary such as NYPD and then toggles Custom, the boundary polygons remain while also enabling the draw feature and vice versa.

Toggling from one to the other should cancel out the previous.

Currently the UI is problematic:

If I select a person type such as "cyclist", but not a injury type such as "injury" or "fatality", it becomes much more difficult to filter, and more confusing to the user. In the case of cyclists we can use the vehicle type code to see all collisions where a cyclist was involved but not necessarily injured or killed. However there is no such code to filter for pedestrians who may have been involved in a crash but not injured or killed. Likewise selecting "motorist" without selecting "injury" or "fatality" or "no injury / fatality" would really just mean selecting every single crash, as all crashes typically involve motorists. We could theoretically select crashes that involved 2 or more cyclists, but there is no way to select a crash that only involved cyclists and pedestrians.

Thus, it would make more sense to refactor the UI as follows:

• "other" would select all crashes with no injuries or fatalities
• users could select multiple buttons, eg: fatalities for cyclists & pedestrians

For example when a user doesn't filter the data by a boundary.

Will mean removing the AND c.the_geom IS NOT NULL clause, may affect query performance?

Display a landing / greeting page for users visiting on mobile asking them to view on Desktop. Mobile layout will be integrated in a later of phase of work.

Related to #16 the total is coming from the "persons" fields, not being summing cyclist, pedestrian, and motorist. It should do the latter.

In the sample final data extract Dejan sent that covers the time period of July 1, 2016 to December 31, 2016 values from the unique_key field don't match those in the NYC Open Data portal.

• sample extract table on CARTO: https://chekpeds.carto.com/tables/export2016_07

• filtered data on Socrata: https://data.cityofnewyork.us/Public-Safety/collisions_july_2016_to_december_2016/g6h3-9f7y/data

Most of the other columns are left empty. Easiest fix is to not select them until NYPD improves their data.

1. When a user clicks a boundary filter button, such as borough, the corresponding GeoJSON data should be loaded on the map as a L.geoJson layer.

   • store.filterArea.geo will be set to boundary type. Will require mapping between button name & boundary table name in Carto

2. Clicking on a polygon of the boundary layer will trigger the filtering of the crash data using the corresponding polygon geometry.

   • store.filterArea.identifier will be set to id of boundary polygon.

3. The map should zoom & pan to the bounding box / extent of that polygon.

4. Polygon data should then be removed from map to not block interactions with crash data

5. To the right of Filter Button should display the identifier of selected polygon, eg: 101 or Brookyln or Crown Heights

Once GeoJSON is fetched for a polygon it can be kept in the Redux store so that it doesn't need to be fetched again.

Q: If user is zoomed in to an area and selects a boundary filter, should map zoom to extent of the data?

Filter by custom area interaction will be similar, but without the ajax fetch.

Modal Component needed for:

• Disclaimer
• About
• Copyright
• Download Data

Parts of application state to consider, with state-full URLs in mind:

• map zoom & center (will live in URL hash only)
• crash type: fatality, injury, no fatality or injury, or all
• person type: pedestrian, cyclist, motorist, or all
• area filter: borough, precinct, community board, ..., custom
  • areas with set boundary can have a set identifier code or number, eg: 1 for Manhattan (borough)
  • custom will require a list of lat lon points representing the custom shape
• start date (date format)
• end date (date format)

State-full URL will only affect app state when page is loaded or refreshed.

To think about:

• how the application state determine the CARTO SQL query
• default application state (date range & probably "city wide" enabled)

Enable tooltips for crash dots on map.

To be provided by client.

If hosting on github pages and redirecting URL, will need to make sure all links are changed from https to http. Also site name should be updated in social media meta tags as it is currently pointing to https://clhenrick.github.io/nyc-crash-mapper/.

• turn off https option in initCartoLayer
• use http version of carto basemap tiles url in app_config
• use // for google fonts css in index.html
• use http version of cartodb.js css in index.html
• use http version of cartob.js in index.html

Use an SQL API call to grab the min and max years of the data.

Legend includes a dot style for "no injury or fatality" so there should be an equivalent filter for one.

To show the user where the crashes occurred that they filtered. Functionality could be disabled via a check box?

In order to use the data compiled by John Krauss for 2009 - 2011 the date range filter should be limited to month and year. That data is summary data and fills in the gap for some years between the TA data and NYC Open Data.

Write up a statement that explains:

• data sources: NYC Open Data Portal, NY Crash Data Bandaid, TA FOIL of NY DMV data
• data normalization methodology
• why data in map doesn't reflect data in stats (see #27)
• etc...

To be more consistent with Stats Counts.

Causes the SQL query to be fired prematurely. Should either remove store.filterByArea.geo before firing SQL or prevent interaction with Filter by Type buttons when the Filter by Boundary polygons are loaded / loading onto the map.

Currently it's being covered by the UI in its default position.

• Possibly add "Number of Crashes" to indicate size represents crash count
• Fix CSS to correctly position circles and text for legend, possibly transform: translate(x, y) ?
• Move "LEGEND" text to the right to align with color circles for crash type

Use an SQL order by clause to make sure fatalities are always on top, followed by injuries, followed by no fat / inj.

For example when selecting all data from July 1st – July 31st, the query returns data up to but not inclusive of July 31st.

Sample query:

select distinct date_val from export2016_07
where date_val >= date '2016-07-01'
AND date_val <= date '2016-07-31'
order by date_val desc

Certain values have single quotes in them like Hell's Kitchen which is a value of the "identifier" field for the nyc_neighborhood table.

When hovering over a filter area polygon it should highlight and display the identifier in a tool tip.

Dynamically create SQL queries using string concatenation & template literals, determined by applied data filters. Use Akil's queries in ./sql/ as a starting point.

An ETL script will run daily on a Heroku Scheduler to retrieve the most recent data from the NYC Open Data portal, format it, and load it into the Carto table.

Via Christine on 2/2/2017

When hovering over each dot I add up approximately 40 fatalities versus the 124 shown in the summary

Some rows have higher values for the type of person injured or killed than "number_of_persons_killed" or "number_of_persons_injured". For example, a row may have a value of 3 for "number_of_pedestrians_killed" but then have a value of 2 or 1 or 0 for "number_of_persons_killed". Typically in the data it seems that "number_of_persons_killed" reflects a total for all three categories of person killed: cyclist, motorist, and pedestrian. Same for "number_of_persons_injured".

As such the columns "number_of_persons_injured" and "number_of_persons_killed" should be treated with suspect.

Here is a screenshot of selecting such rows in CARTO:

When Citywide boundary filter is enabled, display a disclaimer message to the right of the cartodb attribution stating that stats may differ than points on map due to lack of location information provided by the NYPD.

Would be preferable to import the data to Carto directly from Postgres using the Import API PostgreSQL Connector: https://carto.com/docs/carto-engine/import-api/database-connectors#the-postgresql-connector

Waiting to hear back from the folks at Carto to enable it.

The CartoLayer tiles can take a few seconds or longer to load at times, so the app should give the user some indication that they are loading after a filter is applied. This stackexchange post seems like a good solution.

Too much info currently in tooltips, better to move some of it to an infowindow.

Embed social media widgets in each of these?

Ask client for GA key.

Had the other way around before :P

Handle smaller screen widths, e.g. 1024px. Possible options:

• Hide legend
• Make font smaller
• stack number above factor in Contributing Factors
• Abbreviate cyclist, ped, driver to c, p, d (?)

When a user clicks on one it should stay active until another button is selected or it is clicked again.

Filter by Type will allow for both a crash type and person type to be selected.