0

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?

Un-stylized map of all US Zip Codes with "area_zip_r" value

Results of ST_Union showing gaps in data

0

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.
  • That worked! Thanks! Was the problem simply the missing max command in front of the cartodb_id & including the cartodb_id in the group by clause? or something to do with invalid geometries? – Sbraund Jul 11 '18 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.