Query to combine fields to create a label

Sometimes when you are viewing a map you want to combine several fields into one in order to create a common label. This is common when you want to combine languages or combine numeric values and their measurement. 

In this example, we'll combine a numeric value and it's unit of measurement to create a new field that we can label with. 

This dataset came from the snowfall data provided by NOAA. It is collected by a large number of weather stations across the United States. 

In the dataset it has two fields 'amount' and 'amount_units'

To combine these values and format them for labeling we can do a few things with SQL. We go to the advanced queries page and enter this query: 

select *, (to_char( cast( amount as float), '9999.9') || ' ' || amount_units) as label from dataset_14473 

This query is doing several things:

First, 'amount' was imported as a string, so we need to cast it to a float. The reason we are doing this is that we want to limit the number of trailing decimal values to 1.

Second, we convert the resultant float into a string with the format '9999.9' meaning only have 1 value after the decimal point. 

Third, we add a space ' ' and the unit type from the field 'amount_units'

Finally, we select all of that as a new field called 'label' and all the other fields as well with the *, and we have the final query.

Creating a new permanent dataset from this query will allow us to label using this field and share it with others. 

For more information see the formatting functions and string functions from PostgreSQL.