Why do geometries disappear after having applied ST_Union

Question

I applied ST_Union on my set of about 2000 geometries in order to get 12 distinct areas. As a result, I obtained (probably) WKB format that I'm still not able to use in order to get some interesting results.

Why?

What's the reason behind the vanishing geometries from CARTO? And why are they only obtained as WKB after exporting them to a .csv file? Why CARTO doesn't keep them so that I can visualize them simply on a map?

---

I have one, big table of data: https://skogenskonung.carto.com/dataset/dd1 The table depicts the results of the election in Berlin. There are, among others, geometries, Berlin's districts, number of people who voted, amount of votes each party has received etc.

What I want to do is to divide the data according to the column awk (awk are electoral districts), so that I have 73 rows (there 73 distinct awks). What is also important is that I want to have 73 geometries that depict these awk. So I executed the following query:

SELECT ST_Union(the_geom), SUM(right_afd) AS afd, SUM(right_cdu) AS cdu, SUM(right_wahler) AS waehler
FROM dd1
GROUP BY awk

What I obtained in the column geometry seems to be empty at first. When you hover at it, it says it is a polygon. When I export the result to the .csv file I see very, very long sequence of numbers and capital letters. When I click on "create map" out of the result of this query, it gives me: "The column 'the_geom_webmercator' doesn't exist."

Answer 1

First of all, I will recommend learning the basics of PostGIS and CARTO. There a great materials and tutorial out there. All those additional questions will be answered doing some research.

Regarding the first issue, the geometries are not being displayed because the three mandatory fields are missing: cartodb_id, the_geom (the geometry that is stored) and the_geom_webmercator (the geometry that is being displayed). So in order to fix your query, you will need to apply something like this:

with data as (
    SELECT 
        max(cartodb_id) as cartodb_id, 
        ST_Union(the_geom) as the_geom, 
        SUM(right_afd) AS afd, 
        SUM(right_cdu) AS cdu, 
        SUM(right_wahler) AS waehler
    FROM 
        dd1
    WHERE NOT ST_isValid(the_geom)
    GROUP BY awk)

SELECT 
    *, 
    st_transform(the_geom, 3857) as the_geom_webmercator 
FROM 
    data

Do notice that I am getting only the geometries that are valid, because it is the only* way to make this particular query work. So you will end up with something like this:

*I am sure there will be workarounds.