Rating bus stops based on attributes and mapping on Carto

Question

I have a bus stop dataset that I want to plot onto Carto. The dataset includes columns of attributes about the bus stop and I want to create a new column called Rating which would rate based on those attributes (whether the bus stop has route maps or how pedestrian friendly it is).

First, I created a new column called rating using this sql code.

alter table metro_bus_stops
add rating int NOT NULL DEFAULT(0)

Then, I used the following code to add 3 points for the bus stops that has bike racks (attribute = bstp_has_bkrs)

update metro_bus_stops
SET rating = rating + 3
WHERE bstp_has_bkrs = 'Y'

I have 27 attributes total that would add or subtract points to the rating. I want to map the bus stop points based on these rating score on Carto eventually. Is there a better way to work with this rather than adding calculations for each attribute individually? I wonder if I could do this better in Python with a formula using if else statement.

This is the bus stop data I am using if it would help.

Any feedback would be appreciated!

Answer 1

You can combine any number of columns to your SQL statement, combining them with logical operators like AND and OR. This should be the easiest way to do it, something like:

update metro_bus_stops
SET rating = rating + 3
WHERE bstp_has_bkrs = 'Y' 
   OR attribute2 = value2 AND
   AND attribute3 > value3 
   OR ...