2

I'm a novice with Carto and GIS generally. I have a data table with a few thousand lines of data. Each row of data has a country and a numerical value.

I'm trying to total the value by country and show a single point per country.

I've tried reading documentation, but am overwhelmed by reading up on PostgreSQL - what I am attempting to do is so simple, yet I don't find a way of making it work.

Should I change my data structure - to only have one line of data per country? I would be surprised is that was necessary!

As you see in the map - the Netherlands has 3 or 4 overlapping circles - there should only be a single circle.

Map of data

Edit: additional picture.

concentric circles

3

In order to group your data by country and show only one point, I suggest using Create Centroids of Geometries Builder analysis. Here is a tutorial that exmplains how to do it. As far as I understood your requirement, you will need to set your country column as category and avg, sum... of your numerical value as the aggregation operation.

UPDATE: If you want to have access to the original data source (in order to see the table or access the SQL panel), you can drag and drop the layer source out:

drag-node-out

  • Thanks Ramiro - that works. I tested this using only my Europe data; I've found that I have only 7 lines of data now. It would be useful to me if I could see the few hundred individual lines of data. I have a Product field I would like to use as a Widget. I should have been clearer in my question that I wished to keep the 100s of individual lines of data and apply a style or analysis afterwards. – Tally Oct 12 '18 at 12:11
  • You can still add widgets to the original layer source from the WIDGETS panel, thus selecting A0 instead of ' A1`. Also, you can drag layer nodes such as your layer source out of the chain of analysis as explained in my update. IMHO this is one of the best features of Builder. – ramiroaznar Oct 12 '18 at 19:18
  • @Tally if the answer is correct to you please mark it as answered – Jorge Sanz Oct 19 '18 at 8:46
  • Hi Jorge, I tried out the above. I found that the additional (widget) layer is not behaving as I'd like. I would like the points to sum for the total value per country, but also to be filterable by Product too. I've found the additional layer is again showing many concentric circles, which isn't the desired behaviour. Imagine I filter by Product A, I would like the country data point to sum all of Product A values for that country, instead of displaying them individually. I've updated my question with an additional picture. – Tally Oct 19 '18 at 9:24
  • Can you update your question and share the dataset with us? I am confident that Builder can achieve what you are looking for? – ramiroaznar Oct 19 '18 at 9:32

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.