1

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
1

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.

  • This pulls the desired results, i.e. all names, professions per address. But then I can't map this in CartoDB ('Map interaction is disabled, select cartodb_id to enable it') and use the pulled information for an infowindow. – Peter Mar 25 '14 at 8:17
  • can you cut and paste the final sql you are running here? I've tested it and it works, with infowindows. I get the feeling your final version leaves something out from the above. – andrewxhill Mar 25 '14 at 11:19
  • It works now! I am using: 'SELECT strasse_heute, string_agg(familienname || ', ' || beruf, '<br>' ORDER BY familienname, beruf), the_geom_webmercator, Min(cartodb_id) cartodb_id FROM vauban group by strasse_heute, the_geom_webmercator'. By using some basic HTML in string_agg I also can style the infowindow a bit. – Peter Mar 26 '14 at 7:49

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.