0

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."

4
  • 2
    If you are having the same problem as in your last question. You always need to add cartodb_id, the_geom and the_geom_webmercator as mandatory fields in your questies. Could you share the query you are applying? A link to your dataset would be also very helpful. Jan 16, 2018 at 15:37
  • Please consider posting your query (or a simplified version of it). Without it, it's impossible for us to help. Jan 16, 2018 at 16:04
  • I've edited my post. Added the dataset, the query and some additional questions.
    – mångata
    Jan 16, 2018 at 22:06
  • 1
    Please only ask one question per post, as per the tour. Also, as ramiroaznar says, a lot of what you ask is in the docs. Jan 17, 2018 at 10:12

1 Answer 1

2

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:

enter image description here

*I am sure there will be workarounds.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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