I'd like to use CartoDb to map historic addresses of a city (link to demo). Individual addresses are mapped as points on the map. Detailed information for each address point should be displayed in the infowindow The geocoded addresses are stored like this:
NAME PROFESSION ADDRESS ... (+ CartoDb fields like cartodb_id, the_geom_webmercator) Smith shopkeeker grand-rue 10 Martin baker grand-rue 11 Wagner grocery grand-rue 11
As long as there is only one person living at an address, I get the desired infowindow by simply doing a select * from _table_name_. When two or more persons are associated with the same address I obviously would need some aggregate function in PostgreSQL.
I am experimenting with:
SELECT address, string_agg(name || ', ' || profession, '; ' ORDER BY name, profession) FROM table_name group by address
I am running into two problems:
In order to map, CartoDb expects the columns 'cartodb_id' and 'the_geo_webmercator' to be included in the SELECT statement. When doing so, the result of the query is not grouped by 'address' in the desired way; instead the unique 'cartodb_id' is used for grouping. So, the following is not grouping according to 'address':
SELECT address, string_agg(name || ', ' || profession, '; ' ORDER BY name, profession), the_geo_webmercator, cartodb_id FROM table_name group by address, the_geo_webmercator, cartodb_id
The output of 'string_agg' is treated as one field in the design for the infowindow.
Grand-rue 11 Martin, baker; Wagner, grocery
What I would like to have, though, is a separate treatment of 'name' and 'profession' in the infowindow, like:
Grand-rue 11 1. Martin baker 2. Wagner grocery
Any hint is appreciated.