1

I have three datasets with some geospatial (US census tract) overlap that I want to combine into one. so far I've used merge vector layers to do this since it keeps even rows that do not have matches in all three of the layers (which I don't think any of the join tools do). the issue is that merge vector layers doesn't join them based on a field value (i.e. census tract), so there are multiple instances of census tracts in the census tract column -- one from each layer that it was found in. I want these to all be on one row. the final layer would look like the result of appending all of the columns from each of the 3 layers, joining them by the census tract column while keeping those without matches. I'll give an example of what's happening and what I'd like to happen enter image description here

enter image description here

and with my knowledge of how to use 'Join' tools like 'Join attributes by field value' the output layer only has the overlap, so when I use 'Join attributes by field value', first with dogs and trees, it gives me

enter image description here

I'm sure there is a better way but this is where my knowledge and searches have taken me.

2
  • It would help if you could be more specific. Can you show what output you get, and what output you are desiring?
    – amball
    Commented Oct 9, 2023 at 14:20
  • yes thank you I just edited it to be more specific
    – gispain310
    Commented Oct 9, 2023 at 14:36

1 Answer 1

2

What I would do is create a virtual layer with the following code:

SELECT census_tract, max(dogs) as dogs, max(trees) as trees, max(roads) as roads
FROM (
   SELECT census_tract, dogs, NULL AS trees, NULL AS roads
   FROM layer_1
   UNION ALL 
   SELECT census_tract, NULL AS dogs, trees, NULL AS roads
   FROM layer_2 
   UNION ALL 
   SELECT census_tract, NULL AS dogs, NULL AS trees, roads
   FROM layer_3 ) AS subquery
 GROUP BY census_tract;

Beware of blanks within layer and field names, as they can return an error. That's why I always use underscores _ between words (e.g. layer_1).

After that, you can save it as a vector layer if the performance is too slow working with a virtual layer.

3
  • This worked! Thank you.
    – gispain310
    Commented Oct 9, 2023 at 18:57
  • you might consider accepting @jpinilla's answer
    – amball
    Commented Oct 10, 2023 at 15:01
  • Revising the question, I have noticed that not all possible census_tract values are in table_1, so my first proposed code wouldn't work properly. Please note the edited answer.
    – jpinilla
    Commented Oct 11, 2023 at 13:52

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.