Using SQL in QGIS field calculator to populate new column?

Question

Courtesy of Geofabrik I have a "places" point shapefile which includes in the attribute table a column named "type". As can be seen in the attached image the column contains fields such as "city", "town" and "village".

For labelling purposes, how do I populate the "font" column with integers such that when "type" = 'village' then "font" will be set to (say) 7, or when "type" = 'city' then "font" will be set to (say) 12, and so on?

enter image description here

Answer 1

If You want to have separate column use CASE statements in the Field Calculator - similar to what underdark suggests. Write something like this:

CASE
 WHEN "type" = 'village' THEN 7
 WHEN "type" = 'city' THEN 12
 .....more statements
END

Answer 2

Create a CSV with type and font values and join it to the points. Save the result as a new file. I think that should be fastest.

Alternatively, you can write CASE WHEN ... THEN ... ELSE ... END statements in field calculator to select the correct font size.

But actually, you don't even have to save the font size in a separate column. You can use expression-based label size to define the size on-the-fly.

Answer 3

You can use the Fields option. Right click on your layer> Properties> Fields. There you can choose the widget Value map for your target attribute and set a description for the values stored in this column. The type "village" would have the description 7. After that you have the numbers for labelling.

This is like a replacement and the original types cannot be seen anymore. It depends on your labelling purposes, if you want to label your points with the original types as well.

Otherwise you have to "copy/paste" this column:

One solution could be to add a column with unique IDs (e.g. new column name type_id) to your layer (for joining it with itself). How you can do this is explained here. For your business you can revert to the osm_id of your layer. After that you export your attribute table of point layer to dbf/ or csv file. Then you join your layer with the dbf/ or csv file (type_id-type_id join, or osm_id-osm_id). This solution is explained in this answer as well. Before you export your attribute table you can kick out the columns you don't want to export. Right click your layer> Properties> Fields and choose the widget Hidden instead of Line Edit to hide unwanted columns.

After the join you have two identical columns. With one of them you can do the "value mapping" mentioned above.