2

I would like to join US census population estimates with county boundaries.

The population estimates layer (co-est2019-alldata) contains STATE and COUNTY fields with state and county FIPS codes, like this:

STATE COUNTY
36 51
1 15

The county boundary (cb_2018_us_county_5m) layer contains the same values but with slightly different formatting and additionally a GEOID string field that combines the two:

STATEFP COUNTYFP GEOID
36 051 36051
01 015 01015

QGIS doesn't support joining on multiple fields, but a common workaround is to create a new virtual field that combines the target attributes. I want to create a virtual field in the population layer that matches the GEOID field in the county boundary layer.

This would be simple if there were a function equivalent to sprintf, because then I could just write:

sprintf('%02s%03s', "STATE", "COUNTY")

...but as far as I can tell, no such function exists. What's the best way to handle this?

3
  • Something like right('0' || to_string("STATE"), 2) || right('00' || to_string("COUNTY"), 3). Dec 9, 2020 at 19:41
  • 3
    or better : lpad(to_string("STATE"), 2, '0') || lpad(to_string("COUNTY"), 3, '0'). Dec 9, 2020 at 19:42
  • 1
    @J.Monticolo perfect! Want to post that as an answer? I hadn't previously noticed those string functions.
    – larsks
    Dec 9, 2020 at 21:46

1 Answer 1

3

Try this one :

lpad(to_string("STATE"), 2, '0') || lpad(to_string("COUNTY"), 3, '0')

returns

if "STATE" = 1 and "COUNTY" = 15 : 01015
if "STATE" = 22 and "COUNTY" = 5 : 22005

From the documentation:

  • lpad(string, width, fill) (doc) :

    Returns a string padded on the left to the specified width, using a fill character. If the target width is smaller than the string’s length, the string is truncated.

  • to_string(number) (doc) : (because I wasn't sure that your fields were characters)

    Converts a number to string.

  • || : concatenate two strings

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.