2

I'm working with the Global Administrative Boundaries shapefile, and am have a CSV of values I'd like to join to it.

My issue, however, is that there are multiple ways to spell the region names. Look at the Catalonia example below: there are 6 different ways to spell the region name, depending on the language. enter image description here

My question is this: If the join field value is "Catalonia" in my CSV, how would I go about joining my data to the shapefile on any of the values in the VARNAME_1 column? Or should I split VARNAME_1 into multiple columns, and then somehow join on "one of any, if any" values?

Note 1: There isn't a consistent number of languages in the VARNAME_1 column. Note 2: Using QGIS 2.18.2, MacOS

  • Since your data seems to be of the nightmarish type, I have no practical answer aside from advising to fix it before doing the join. Of course this doesn't help if you're facing thousands of rows with different numbers of alternate toponyms. I removed my answer and will let someone with more scripting skill answer this. – Gabriel C. Sep 21 '18 at 19:29
  • Np, appreciate your putting thought behind this — it's a frustrating problem! There may be a fix using some other tools, but I'm hoping that there's a way to do so from within the QGIS ecosystem. – scrollex Sep 21 '18 at 19:30
  • 1
    The method described at gis.stackexchange.com/questions/127415/… might be a good starting point. You can split a field value at a defined character and save the new value to another field. This will definitely help you isolate the different spellings. – Gabriel C. Sep 21 '18 at 19:45
2

Use the DB Manager and an SQLite JOIN:

  • Load both layers in QGIS
  • Open QGIS | Database | DB Manager...

    • double-click Virtual Layers | Project layers
    • open the SQL Window (2nd symbol from left)
    • run (Execute)

      SELECT a.*,
             b.*
      FROM <shapefile_name> AS a
      JOIN <csv_layer_name> AS b
        ON instr(a.str, b.str) > 0;
      

      where instr(string, substring) checks string for substring and returns true the position of the first matched character if there's a pattern match (as in regex pattern matching, with all implied caution; substring = 'York' will match string = 'NewYork'. The join is one-to-one excluding, meaning that there will only be as many rows as there are matches; use LEFT JOIN if there possibly are more entries in the shapefile_name layer that you want to keep (and also switch the layers in the join if it's the other way around).

    • tick Load as new layer, choose a name and hit Load

    • <new_layer> context menu | Save as.. and save to file
  • uh oh...had a little mistake there; if you've tried already and it failed, try again. should work now as explained. – ThingumaBob Sep 22 '18 at 7:57
0

This might be a simple answer, but couldn't you create a new field named "Join" and then do a simple "if else" statement. I'm not the best at python but I'm sure you could do something along the lines of "if [Name_1] == Cataluña", "then [join] == Catalonia" "elif [Name_1] == Catalogna", then [join] == Catalonia", with a statement for each different spelling and the last expression being an 'else' instead of 'elif'. Sorry I can't help with the specific syntax, but it will look something like what is below. Good luck!

if expression1:
   then statement 
elif expression 2:
   then statement 
elif expression 3:
   then statement 
else expression 4:
   then statement
  • That's not really a scalable programmatic approach. I could technically look though each entry and write this code but it's even quicker to just edit the CSV. Even that's still a prohibitive amount of time, though. – scrollex Sep 22 '18 at 14:10
0

While not everyone will have the same workflow, I think it is good practice to split all possible values into different fields. For example, in Canada, some federally generated data has attribute tables where toponyms appear for both official languages. They are split into two fields, which makes searching for entities much easier.

I would advise to split the fields. Joining will be much easier, and searching the data will also be simplified. If your csv always uses the same language, you might want to extract only the matching value in your field. For this you can use the method described here and repeat it until your string is split in the required fields.

I took a look at your data and the number of entities is relatively low, which makes it realistic to fix them. To split the values into new fields, go to the field calculator and use those two expressions (replacing "Test" with your own field).

enter image description here

The first one isolates the first substring before the '|' character while the second one will isolate the rest of the string after the first '|'. Just repeat the second one on every new split field until you have no vertical slashes left.

You'll probably have to manually copy the values you want in a new field because the languages aren't all the same but it'll work for this small entity number.

  • Unfortunately, it seems like there are different numbers of languages. This ranges from one several. – scrollex Sep 21 '18 at 19:14

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.