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:

Table of amphibian data

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.


1 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".

  • Thanks for your response. Concatenate is a good idea, but it's not what I'm after. I think that the key issue that I'm facing has to do with the size of the data that I'm dealing with. Understanding how union exactly handles the polygons and tables would be helpful to reduce the size of the resulting layers. I think the issue I'm facing might be from disjointed polygons that form in the union that creates invalid geometries and duplicated polygons that are disjointed. I'm not entirely certain. I'm trying to work around the problem in r-stats using package sf and cleangeo. Jun 6, 2019 at 23:32
  • The union function can create geometry errors and zero area polygons when working with lots of overlapping spatial data + multiple polygon layers. I'm working on a resolution to this problem using the rstats sf package with sf_combine, sf_union, and as_Spatial to convert the layers into sp compatible format to run it through the cleangeo package. I was looking for a detailed introduction to the union process for filling the data tables appropriately, but there is more to this in terms of ensuring that there's clean geometries in the process. Jun 7, 2019 at 15:48
  • 1
    Your explanation of the union process was as thorough and as detailed as any I've seen elsewhere. I'm not sure what's left to explain. My answer provides a way to fix the issue caused by the QGIS union process, where one field is split into two fields. It seems like your question is too broad to be addressed by a single answer. If you can break it up into shorter, more focused questions, you might get better help.
    – csk
    Jun 7, 2019 at 16:28

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.