Say I have a polygons that overlaps with each other, How do I achieve the same output as dissolve -> dissolve field(check "name") using SQL code?

layer fields information:
- name # <-- I want to dissolve by `name` field
- area

origin original desired output desired output

  • 4
    Get familiar with the basic table expressions and their functionality - you are looking for GROUP BY.
    – geozelot
    Oct 4, 2023 at 8:34

1 Answer 1

select st_union(array_agg(geometry_field)) as geom_union
from your_layer 
group by "name";
  • Hey this works geometrically, however the "value" field is dropped, I tried to add "value" in select statement "select value, st_union(...)", but an error is raised. What is going wrong here.
    – Cookie
    Oct 4, 2023 at 8:58
  • 1
    @Cookie, Iclude value field into group by section
    – RainForest
    Oct 4, 2023 at 9:00
  • However, if you needed an aggregate (say, sum) of the field value you should use select sum(value), st_union(...), but do not use it in the group by section.
    – jpinilla
    Oct 4, 2023 at 14:32
  • Thank you, adding value after group by works just fine. And what if have 10 more to do? Does this mean I have to list each item twice after select and groupby? select a,b,c,d,e,..... from layer group by a,b,c,d,e,.....
    – Cookie
    Oct 6, 2023 at 1:02
  • 1
    @Cookie, yes, that's correct. All fields you need to output other then group operations (max(), count, aggregate() etc.) must be included in both the select and group by sections.
    – RainForest
    Oct 6, 2023 at 3:08

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.