How to achieve equivalent to Dissolve from ArcGIS for Desktop using ST_Union?

Question

I am trying to achieve an equivalent to the Dissolve tool from ArcGIS for Desktop using ST_Union from PostGIS but it seems I am not getting expected result.

I have one table which has certain attributes with the Polygon Geometry. (like FID, Locstat, Loccnt, Shape)

Here is my query :

SELECT c.fid, ST_Union(c.boundaryshape) FROM c Group by c.fid,c.boundaryshape;

Answer 1

Your problem is likely because of what you have included in the group by field.

ST_UNION is an aggregate function, meaning that it is dissolving based on what you specify as the GROUP parameter.

Here is what you entered:

SELECT c.fid, ST_Union(c.boundaryshape) FROM c Group by c.fid,c.boundaryshape;

According to this, you are grouping by your fid, which is probably a unique identifier, and also by boundaryshape, which is the geometry. The problem with this is that it is highly unlikely that the features sharing a unique ID, are going to have identical geometries. Therefore, the ST_Union is probably going to end up with something like the following:

Input:   
fid     boundaryshape  
1         11  
1         12  
1         11  
1         13  
2         12  
2         13  
2         12  
3          5  
3          2  
3          4  

Output:  
fid     boundaryshape  
1        (11 + 11)  
1        12  
1        13  
2        (12 + 12)  
2        13  
3        2  
3        4  
3        5

In this case, you should only be dissolving based on your unique identifier, unless you actually have a number of duplicate geometries with the same fid, that you are trying to get rid of. Even in that situation though, you should only need to group based on the fid, and the union will take care of the geometry duplication.

I would try the following:

SELECT c.fid, ST_Union(c.boundaryshape) FROM c GROUP BY c.fid;

That should give you the desired result of dissolving all features based on the common attribute fid.

Here is the help document at the PostGIS site: ST_Union

See the first example listed at the bottom and notice that it does not include the geometry in the group by section.