EURO DEMO: Query for injury type + cross location in Ben Yehuda

[baruchpicard]

JSON: { "name": "accident_severity_by_cross_location", "data": { "items":[ { "injury_severity": "פצוע קל", "cross_location": "במעבר חצייה", "accident_count": 40 }, { "injury_severity": "פצוע קל", "cross_location": "לא במעבר חצייה", "accident_count": 11 }, { "injury_severity": "פצוע קשה", "cross_location": "לא במעבר חצייה", "accident_count": 10 }, { "injury_severity": "פצוע קשה", "cross_location": "במעבר חצייה", "accident_count": 5 } ], "text": { "title": "חומרת פגיעות בהולכי רגל ברחוב בן יהודה בתל אביב לפי מיקום החצייה" } }, "meta": { "rank": 18 } }

SQL CODE: SELECT CASE WHEN injury_severity_hebrew IN ('פצוע קשה', 'הרוג') THEN 'פצוע קשה' WHEN injury_severity_hebrew IN ('פצוע קל') THEN 'פצוע קל' ELSE 'לא ידוע' END AS injury_severity, CASE WHEN cross_location_hebrew LIKE '%לא במעבר חצייה%' THEN 'לא במעבר חצייה' WHEN cross_location_hebrew NOT LIKE '%לא במעבר חצייה%' THEN 'במעבר חצייה' END AS cross_location, count(DISTINCT provider_and_id) accident_count FROM involved_markers_hebrew WHERE injured_type_hebrew = 'הולך רגל' AND street1_hebrew = 'בן יהודה' AND accident_yishuv_name ='תל אביב -יפו' AND cross_location_hebrew IS NOT NULL AND cross_location_hebrew NOT LIKE '%לא ידוע%' GROUP BY 1, 2 ORDER BY 3 DESC

VISUALIZATION:

LINK TO REDASH

[atalyaalon]

@baruchpicard great work! @BarVolunteering I added a PR for mock data, now we're left with implementing it so we'll get real data in the API. Note that the title should change by the location text (we did it in most_severe_accidents_table Widget)

[atalyaalon]

@baruchpicard Thank you! Following our session - a few notes and changes required:

Regarding the table queried : involved_markers_hebrew cross_location is not per involved but per accident (which is not intuitive and perhaps shouldn't be that way, but that's the data we're getting from the CBS).

Regarding the filter accident_type_hebrew: there are accidents that are not classified as 'פגיעה בהולך רגל' but a pedestrian was injured - hence this filter is irrelevant - perhaps it better to find the "provider_and_id" of accidents in which the injured_type in involved table is a pedestrian.

Regarding this filter: road_type < 3 In general I suggest not using it this way, it's confusing - it's better to filter all of the relevant road types (use "IN"). In our case, I think that for now this filter is not needed - also if a pedestrian was killed in a highway we would like to know how many more were there - so it's a non-urban road.

In addition - performing this: accident_severity < 3 is confusing as well, and we should specify the severities with "in" or "=" and not use < or >. Maybe it's indeed better to use the injury severity of the pedestrian in the involved table instead.

Thanks! great work!

[baruchpicard]

Thank @atalyaalon ! fixed all, and added link to redash + visualization. for this comment: "perhaps it better to find the "provider_and_id" of accidents in which the injured_type in involved table is a pedestrian." I changed the filter to find injured, not accidents, without a nested query. Do you think I need to do it in a different way?

[atalyaalon]

@baruchpicard I think maybe we want to count injured instead of accidents - even though the cross location is per accident and not per involved.

[atalyaalon]

counting injured can be done using count distinct on concat(involveid, '', provider_and_id)

[baruchpicard]

counting injured can be done using count distinct on concat(involveid, '', provider_and_id)

updated :-)

[atalyaalon]

@baruchpicard wonderful! Following steps:

1. Regarding query:

   • I suggest separating הרוג from פצוע קשה - having 3 categories instead of 2
   • I suggest using both street1_hebrew and street2_hebrew (with or between them)
   • I suggest using the codes of the categorical fields and not the Hebrew names (cross_location, injury_severity, injured_type)

2. Create an issue using the new template See as an example the issue created here and check out the redash query attached to it. Also attach the visualization to the issue. Also please mention in the issue the current Widget id that exists in the code (since Mock data already exists for this Widget): injury_severity_by_cross_location

Thank you!

[atalyaalon]

@MichalOren can you take this one and continue where we stopped?

[atalyaalon]

@DorAdam will take this one

[atalyaalon]

Closing this one - #1677 opened instead