Union, merge, intersection, join layers - logical difference and clear explanation for QGIS

Question

I note that there are answers available on the difference between join and merge (e.g., Difference between 'join' and 'merge'?) that link to ArcGIS resources as a reference. However, I think that this problem is more complicated and I'm seeking a sleek summary applicable to QGIS.

I have a large data set that I integrated by running multiple unions between layers (e.g., "streams" U "amphibians" U "species at risk" U "ungulate winter range", etc...). The data set became too large to handle, so I had to break it into pieces and rebuild. This process has made me think more critically about the various union, merge, and join tools.

My goal is to identify what environmental features intersect with a study area that primarily consists of urban roads. I have four final layers that resulted from multiple rounds of unionizing layers of different environmental features:

1. P1P1
2. P1P2
3. P2P1
4. P2P2

Each has of the four layers has the same features within their respective data tables. I tried unionize these layers (e.g., P1P1 U P1P2 = P112, P2P1 U P2P2 = P221, and then P112 U P221 = Final) but I run into computation issues (see here).

As a work-around I thought I would run an intersection (∩) for each of the 4 layers and my study area (roads), which would reduce the computational power required. I thought I could then I merge the different resultant layers to get the result I'm after.

• P1P1 ∩ "study area" = P1P1∩
• P1P2 ∩ "study area" = P1P2∩...and so on...
• Merge P1P1∩ with P1P2∩ = a, then
• Merge P2P1∩ with P2P1∩ = b...and so on...and then
• Merge a + b + c + d = Final result.

That was my plan. However, there is an issue with the data tables. For example, if I have a salamander and a frog in an overlapping area, then the merged data table will only report one or the other. It does not append the information to create the following:

The data table includes only the salamander OR the frog, but not both. Now, for comparison I ran merge, union (QGIS vector overlay), polygon union (SAGA), join attributes by location.

• The merge creates a data table with 2079 rows and seems to be the correct output, but it only gives salamander OR frog and not (salamander, frog) where these spatially overlap.
• The union (QGIS vector overlay) gives 2906 rows with separate fields for Amphibian and Amphibian_2, the polygon union (SAGA) gives 4979 rows with separate fields for Amphibian and Amphibian_1.
• The join gives 2811 rows with separate fields for Amphibian and Amphibian_2.

I made this comparison because I thought there might be a way to append fields from the union data tables; while I can think of a way to do this with the field calculator, I would like to understand the logic prior to advancing in this direction. I was also surprised that polygon union (SAGA) did not give the same result as union (QGIS vector overlay), which makes me concerned and wonder about the steps that I followed to build the initial layers.

I have also tried to solve this problem by working with the rstats package sf using the geos_combine function. This creates another type of output that looks more like the merge function in the QGIS.

Is there a single illustrative resource that could help me to understand the difference in logic as it applies to the QGIS functions (union, merge, join, intersection) as this relates to the data tables as described in this example; same goes for r-stats sf package? Should I be seeking an Append function as exists in ArcGIS? I note that there is the plugin called "Append Features to Layer", but I want to clearly understand the logic to manage my data appropriately to bring this information together logically and with the correct final answer.

Answer 1

I don't understand everything you're trying to achieve, but I think this will help with some of it:

When you have a result with the amphibian field data in multiple fields, use the Field Calculator to concatenate the data from those two fields.

Use an expression like

concat("Amphibian" || ', ', "Amphibian_2")

Note the two different concatenation methods. The difference is in how they handle NULL input. The concat() function converts a null value into a blank string, so concat(<null>, 'text') gives you text. The || symbol gives a NULL result if any of the inputs are NULL, so <null> || 'text' gives you a null output.

• When the fields have the values Salamander and Frog, the result will be 'Salamander, Frog'
• When the fields have the values NULL and Frog, the result will be Frog
• When the fields have the values Salamander and NULL, the result will be Salamander,

You can further refine this expression in many ways, depending on what the inputs are likely to be.

If there are likely to be duplicate values, you can handle this by putting all the values into an array, and use the array_distinct() function to extract a list of unique values.

1. create an array using the string_to_array() function with a similar expression as above
2. use array_distinct() to extract the unique values

3. use array_to_string() to convert back to a string

   array_to_string(array_distinct(string_to_array(concat("Amphibian" || ',', "Amphibian_2" || ',', "Amphibian_3" || ',', "Amphibian_4"),',')), ',')

Note: You won't be able to create a new field called "Amphibian" if you already have a field with that name. Instead give it a name like "Amphibian_combined". Once you have all the data you want in "Amphibian_combined", delete the "Amphibian" field, and copy the "Amphibian_combined" field into a new field called "Amphibian".