Dissolve/merge polygons using ST_Union in CARTO

Question

I am attempting to dissolve the boundaries between several polygons using a common attribute within a column in CARTO and the ST_Union function.

In my scenario, I have created a table for all US Zip Codes, & have given each Zip Code a value within a column called area_zip_r. I would like to dissolve/merge the polygon boundaries for all zip codes that share a common "area_zip_r" value, creating a single polygon for each value within "area_zip_r".

After some initial research and the help of this Stackexchange article I have come up with the following command which does dissolve/merge the polygons, but also leaves gaps of data for an unknown reason.

SELECT 
  cartodb_id, 
  area_zip_r,
  ST_union(the_geom_webmercator) as the_geom_webmercator
FROM 
  {table_name}
GROUP BY 
  area_zip_r, 
  cartodb_id

Results: Many of the area_zip_r values are seen in the output with the desired effects, but I only see a small percent of the area_zip_r values I expect to see. I am returned with approximately 500 unique area_zip_r values when I expect 2000. This is verified when mapping the results. I've attached photographs to show the area my original table covers, and the results of my ST_Union command.

What am I doing wrong?

Answer 1

As far as I have tested, it looks like the problem could be the cartodb_id. Follow these steps, and you should get the union right:

• First, I recommend applying this query in the dataset view:

SELECT 
  max(cartodb_id) as cartodb_id, 
  area_zip_r, 
  ST_union(the_geom) as the_geom
FROM 
  zip_rollup_w_modulus_id_copy_1
GROUP BY 
  area_zip_r

• Secondly, create a new dataset from the query.
• If you count the number of features, the result will be the expected: 2715.