PostgreSQL – Using Aggregate and Infowindow in CartoDB

aggregatecartoinfowindowpostgresql

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:

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

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

Best Answer

Well, the_geo_webmercator should be the_geom_webmercator, before that gets you.

The rest, you'll need a way to pull a single cartodb_id instead of trying to group them. A simple way is just to grab the min one, something like this, Min(cartodb_id) cartodb_id. So I would do,

SELECT  address, string_agg(name || ', ' || profession, '; ' ORDER BY name, profession),
        the_geom_webmercator, Min(cartodb_id) cartodb_id
FROM table_name 
group by address, the_geom_webmercator

I'm assuming that address is always the same giving a geom, so that should work.

Related Question