GreenInfo-Network / nyc-crash-mapper-chart-view

Chart view for NYC Crash Mapper that allows for viewing Trends, Comparing, and Ranking of various NYC geographies
http://vis.crashmapper.org
MIT License
2 stars 1 forks source link

SQL breaks when selecting #120

Closed danrademacher closed 4 years ago

danrademacher commented 4 years ago

As far as I can tell by progressively testing older commits, the SQL queries have broken for Community Boards, NTAs, and NYPD Precincts ever since this commit, https://github.com/GreenInfo-Network/nyc-crash-mapper-chart-view/commit/655ae725c4eab64fc566f7a0e2d9b5f9c766badf (and the larger one right before it).

It produces this query: https://chekpeds.carto.com/api/v2/sql?q=SELECT+COUNT(c.cartodb_id)+as+total_crashes,+CASE+WHEN+borough+!%3D+%27%27+THEN+CONCAT(borough,+%27,+%27,+%27Community+Board%27,+%27+%27,+community_board)+ELSE+CONCAT(%27+No+Borough,+%27,+%27Community+Board%27,+%27+%27,+community_board)+END+AS+community_board,+SUM(c.number_of_cyclist_injured)+as+cyclist_injured,+SUM(c.number_of_cyclist_killed)+as+cyclist_killed,+SUM(c.number_of_motorist_injured)+as+motorist_injured,+SUM(c.number_of_motorist_killed)+as+motorist_killed,+SUM(c.number_of_pedestrian_injured)+as+pedestrian_injured,+SUM(c.number_of_pedestrian_killed)+as+pedestrian_killed,+SUM(c.number_of_pedestrian_injured+%2B+c.number_of_cyclist_injured+%2B+c.number_of_motorist_injured)+as+persons_injured,+SUM(c.number_of_pedestrian_killed+%2B+c.number_of_cyclist_killed+%2B+c.number_of_motorist_killed)+as+persons_killed,+year+%7C%7C+%27-%27+%7C%7C+LPAD(month::text,+2,+%270%27)+as+year_month+FROM+crashes_all_prod+c+WHERE+the_geom+IS+NOT+NULL+AND+community_board+IS+NOT+NULL+AND+community_board::text+!%3D+%27%27+AND+AND+borough+IS+NOT+NULL+AND+borough+!%3D+%27%27+AND+TRUE+GROUP+BY+year_month,+CASE+WHEN+borough+!%3D+%27%27+THEN+CONCAT(borough,+%27,+%27,+%27Community+Board%27,+%27+%27,+community_board)+ELSE+CONCAT(%27+No+Borough,+%27,+%27Community+Board%27,+%27+%27,+community_board)+END+ORDER+BY+year_month+asc,+CASE+WHEN+borough+!%3D+%27%27+THEN+CONCAT(borough,+%27,+%27,+%27Community+Board%27,+%27+%27,+community_board)+ELSE+CONCAT(%27+No+Borough,+%27,+%27Community+Board%27,+%27+%27,+community_board)+END+asc

which produces this error:

{
"error": [
"syntax error at or near \"AND\""
]
}

AND there it is:

SELECT 
    COUNT(c.cartodb_id) as total_crashes, 
    CASE 
        WHEN borough != '' THEN CONCAT(borough, ', ', 'Community Board', ' ', community_board) 
        ELSE 
        CONCAT(' No Borough, ', 'Community Board', ' ', community_board) 
    END AS community_board, 
    SUM(c.number_of_cyclist_injured) as cyclist_injured, 
    SUM(c.number_of_cyclist_killed) as cyclist_killed, 
    SUM(c.number_of_motorist_injured) as motorist_injured, 
    SUM(c.number_of_motorist_killed) as motorist_killed, 
    SUM(c.number_of_pedestrian_injured) as pedestrian_injured, 
    SUM(c.number_of_pedestrian_killed) as pedestrian_killed, 
    SUM(c.number_of_pedestrian_injured + c.number_of_cyclist_injured + c.number_of_motorist_injured) as persons_injured, 
    SUM(c.number_of_pedestrian_killed + c.number_of_cyclist_killed + c.number_of_motorist_killed) as persons_killed, 
    year || '-' || LPAD(month::text, 2, '0') as year_month 
FROM crashes_all_prod c 
WHERE 
    the_geom IS NOT NULL AND 
    community_board IS NOT NULL AND 
    community_board::text != '' AND AND 
    borough IS NOT NULL AND 
    borough != '' 
    AND TRUE 
GROUP BY 
    year_month, 
    CASE 
        WHEN borough != '' THEN CONCAT(borough, ', ', 'Community Board', ' ', community_board) 
        ELSE CONCAT(' No Borough, ', 'Community Board', ' ', community_board) END 
ORDER BY 
    year_month asc, 
    CASE 
        WHEN borough != '' THEN CONCAT(borough, ', ', 'Community Board', ' ', community_board) ELSE CONCAT(' No Borough, ', 'Community Board', ' ', community_board)
    END 
    asc

Double AND AND in the WHERE clause.