1

I have two datasets:

  1. A point shapefile of companies (each row/record is a unique company)
  2. A list of awards given to those companies (each row is a unique award, but since companies can win multiple awards, the same company can exist in multiple rows).

I want to create a Carto map where each point on the map represents one company, but when you click on that company point's pop-up, it shows all the awards that that company has won.

I've tried multiple variations of the "Link second layer" and "Add Columns from Second Dataset" analysis options, but I'm not getting the result I want. I'm thinking that the join created in "Add Columns from Second Dataset" is just a one-to-one join, and it's only joining on the first record it comes across that has a shared company, OR the pop-up itself is restricted to showing only one award.

1

Builder's Add Columns from Second Dataset is the analysis you are looking for. It will join one to many. So my guess is that you are dealing with the classic overlapping point problem. As you have commented, you would have several geometries one on top of the other. When clicking on a point, it will show you the information of the last element to be rendered. In order to fix this problem there are several (not so easy) workarounds. In this help article, you will find links to some of them.

1
  • 1
    Yes, this is correct! I'll give you credit for this answer, but I'll add my own answer, too, just to show exactly how we ended up solving this problem. I think it most closely resembles the json_agg SQL function at the link you posted. Although because we did it the way we did and didn't try the exact solutions proposed at that link, I can't actually say which is better. But I will certainly explore them more as we continue to work on this project and edit my answer if we find that anything works better. – Kristen G. Feb 7 '20 at 17:11
1

I've accepted ramiroaznar's answer, but I wanted to also post the solution my colleague and I ended up using, which gave us what we needed:

In the SQL window of the "Companies" dataset in Carto Builder, we wrote the following SQL, which creates an inner join between the Companies dataset and the Awards dataset, grouped by company.

SELECT c.cartodb_id, c.the_geom, c.the_geom_webmercator, c.company,
string_agg(a.award, '<br><br>') as award_field 
FROM company_list c
INNER JOIN awards_list a 
    ON c.company = a.company
GROUP BY c.cartodb_id, c.the_geom, c.the_geom_webmercator, c.company

With this we now have a new column in our Companies dataset, which contains a string list of all of the company's awards, which we can now show in our map pop-up, so that under each company name is a list, separated by line breaks, i.e. <BR><BR>, of all the awards that company received.

So, the pop-up looks something like the following. (We opted to put so much space between the awards simply because the real awards are very long, so all of the text was running together without the extra spacing):

Company Name
The Best Company

Awards
Awesome Award #1

Awesome Award #2

Awesome Award #3

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.