Style qualitative data using hex color codes

Context

In this guide, we're going to uncover how to use hex color codes in Builder to bring qualitative crime data from Chicago to life. Ever wondered how to give each crime category its own unique color? We'll show you how to do that with randomized hex color codes. We'll also dive into setting specific colors based on conditions, tapping into the power of CARTO Workflows and SQL. Once we have our colors ready, we'll use Builder's HexColor feature to effortlessly style our layers. By the end of our journey, you'll be ready to create a vibrant and clear map showcasing the intricacies of crime in Chicago. Excited to transform your data visualization? Let's jump right in!

Step-by-Step Guide:

In this guide, we'll walk you through:

• Creating hex color codes with Workflows

• Generating hex color codes via SQL

• Styling layers in Builder using hex color codes

Creating hex color codes with Workflows

1. Access Workflows from your CARTO Workspace using the Navigation menu.

1. Select the data warehouse where you have the data accessible. We'll be using the CARTO Data Warehouse, which should be available to all users.

1. Navigate the data sources panel to locate your table, and drag it onto the canvas. In this example we will be using the chicago_crime_sample table available in demo data. You should be able to preview the data both in tabular and map format.

1. We are going to generate random hex color codes based on distinct category values. For that, add the Hex color generator component into the canvas. This component will first select the distinct values of an input column and then generate a unique hex color code for each of them. In our case, we'll select primary_type as the column input, which defines the type of crime committed. Then, we click on "Run".

1. We can preview the data to confirm a new column named primary_type_hex has been added to your output table. This one contains distinct hex color values for each distinct input category.

1. Now let save our output as a table using Save as Table component. We will use this output later to generate our Builder map.

1. Add annotations so you can provide further context to other users accessing the Workflows.

Now that we're done with the first step of having a table ready to visualize the specific locations of crimes, we'll move to the generation of a new separate table for an extra layer in the visualization. In this case, we'll leverage Spatial Indexes to gain insight into the ratio of arrested to non-arrested crimes. By doing so, we can better grasp the geographical distribution and patterns of resolved versus unresolved crimes.

1. First, transform the crime point location to H3. To do so, use the H3 from Geopoint component using 12 as the resolution level. Once run successfully you can preview the data and the map results.

1. To discern if a crime resulted in an arrest, we need to convert the arrest column from a Boolean type to a String type. We'll accomplish this transformation using the CAST component.

1. Now we can use Simple Filter component to identify the crimes that were arrested (True) vs not arrested (False).

1. For each Simple Filter output, we will add a Create column component where we will define a specific hex color code value to the same column named arrest_hex as per below screenshot. Let's also add some annotations so is clear what we are doing on these steps.

1. Now that we have generated the arrest_hex column, we will use UNION component to bring our dataset back together.

1. Finally, let's save our results in a new table using Save as Table component.

---

Generating hex color codes using SQL

We can generate Hex color codes directly using SQL, both in the DW Console and the CARTO Platform. Within CARTO, you have the flexibility to use either the Workflows with the Custom SQL Query component or the Builder SQL Editor.

Below you can find two different examples on how you can use SQL to generate hex color codes:

• Define hex color codes using CASE WHEN statement:

WITH data_ AS (
  SELECT 
	geom,
    CAST(arrest as string) as arrest
FROM carto-demo-data.demo_tables.chicago_crime_sample)

SELECT
	a.*,
	CASE 
        WHEN arrest = 'true' THEN '#8cbcac'
        WHEN arrest = 'false' THEN '#ec9c9d'
        ELSE ''
    END AS arrest_hex
FROM data_ a

• Generate random hex color code values for each DISTINCT category value:

WITH data AS (
  SELECT DISTINCT primary_type
  FROM carto-demo-data.demo_tables.chicago_crime_sample
),
hex_ AS (
SELECT 
  primary_type,
  CONCAT(
    '#',
    LPAD(FORMAT('%02x', CAST(RAND() * 255 AS INT64)), 2, '0'), -- Red
    LPAD(FORMAT('%02x', CAST(RAND() * 255 AS INT64)), 2, '0'), -- Green
    LPAD(FORMAT('%02x', CAST(RAND() * 255 AS INT64)), 2, '0')  -- Blue
  ) AS random_hex_color
FROM data)
SELECT 
    a.geom, 
    a.unique_key, 
    a.primary_type, 
    b.random_hex_color
FROM carto-demo-data.demo_tables.chicago_crime_sample a LEFT JOIN hex_ b 
ON a.primary_type = b.primary_type

---

Styling qualitative data using hex color codes in Builder

Now that we have generated our tables in Workflows containing the hex color code values, we are ready to style it in Builder using the HexColor functionality that allows you to style qualitative data leveraging your stored hex color code values.

1. First, let's load our first output table in Step 6 named chicago_crime_hex. We will do so adding it as a SQL Query source. To do so, copy the qualified table name from the Save as Table component in Workflows or access the table in Data Explorer.

1. Now let's rename your map to "Crime Analysis in Chicago" and the layer to "Crimes".

1. Now open the Layer style configuration, and follow the steps below:

   • In the Color based on selector pick primary_type column to associate with the hex color code.

   • In the Palette section, click on 'HexColor' type.

   • Finally, pick the column with the hex color code values, which in our instance is named primary_type_hex.

You should now observe the crime point locations styled based on the hex color codes from your data source. Furthermore, consult the legend to understand the association between categories and colors.

1. Change the Stroke Color to black and set the Radius Size to 6.

Next, integrate the aggregated H3 grid to assess the arrest vs. non-arrest ratio. This will help pinpoint areas where crimes often go without subsequent arrests, enabling us to bolster security measures in those regions

1. For that, add a new source adding chicago_crime_h3_hex table created in Step 13. A new layer named "Layer 2" will be added to your map in the top position.

1. Rename the new layer to "Crimes H3" and move it to the second layer position, just below Crimes point layer.

1. Next step would be to style the "Crime H3" layer. Open the Layer style configuration. In the Basic section, set the Resolution to 3. This will decrease the granularity of the aggregation so we are able to visualize it with the crime point location overlaying on top.

1. Now, let's style the cell using our stored hex color codes. For that, select arrest column in the Color based on section as the category using MODE as the aggregation method. Then, choose 'HexColor' type and select arrest_hex as your Color Field.

1. To finalise with the layer options, we will set the Visibility by zoom level of the "Crimes" point location layer from 11 to 21, so that only the H3 layer is visible at lower zoom levels.

1. Once we have the styling ready, we will proceed to add some Widgets to our map. First, we will include a Formula Widget with the COUNT of "Crimes" point source.

1. To continue providing insights derived from our sources, we will add a Pie Widget linked to "Crimes H3" source displaying the percentage of arrest vs not arrest proportion of crimes.

1. Finally, we will add a Category Widget linked to "Crimes" point source displaying the crimes by type as below.

1. Once we have finished adding widgets, we can proceed to add a map description using markdown syntax. In our case, we will add some explanation about how to style qualitative data using HexColor functionality. However, you can add your description as desired, for example to inform viewer users how to navigate on this map and obtain insights.

1. We are ready to publish and share our map. To do so, click on the Share button located at the top right corner and set the permission to Public. Copy the URL link to seamlessly share this interactive web map app with others.

And we're ready to visualized our results! Your map should look similar to the below.