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). – J. Monticolo Dec 9 '20 at 19:41
  • 3
    or better : lpad(to_string("STATE"), 2, '0') || lpad(to_string("COUNTY"), 3, '0'). – J. Monticolo Dec 9 '20 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 '20 at 21:46
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, privacy policy and cookie policy

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