0

I am using CartoDB and am mapping water quality data in a particular region in Mexico.

My organization has data of multiple locations over many years.

Since we are showing the same data of the same location but over multiple years, I wanted to have a info window that allows the user to scroll through each year of data.

However, each point was plotted on top of one another so that you can only see and click on one point.

I know I can just create new columns titled Date 2, Date 3, and Fluoride 2 and Fluoride 3 and so on, but this isn't as visually pleasing and not all of the points have the same number of data or years tested.

Therefore, many windows would have more empty fields.

Is there a way to have my info window allow the user to scroll through the data for different years so that all of the stacked data points can be visible through scrolling through the info window?

In ArcMap, this feature is possible when any data points have the same geometry, but I can't seem to figure it out with CartoDB.

1

You can run a SQL query to aggregate your data for points that are in the same location (or share another similar attribute if the point varies in location by small amounts).

For instance, you can run the following query to aggregate time data at the same point:

SELECT 
    string_agg(to_char(timestamp, 'DD Mon, YYYY'),', ') As agg_dates, 
    the_geom, 
    the_geom_webmercator,
    min(cartodb_id) As cartodb_id
FROM
    table_name
GROUP BY 
    the_geom, the_geom_webmercator

Once this query is run, the data in the timestamp column at identical locations will be aggregated into a list separated by commas, which you can easily include in an infowindow. It uses the date formatting function to_char. It's described in the docs here.

If you provide more specifics about your dataset I can update the query above to better fit your situation.

EDIT: updated query to reflect a date type aggregating.

  • Thanks for the answer! I am going to give this a try. Last time I tried something similar to this, it messed up my map and I had to recode a lot of things. Basically, my data is as follows: coordinates, date of water test (column name is "date"), fluoride level (column name is "fluoride"), arsenic level (column name is "arsenic", notes about the testing site (column name is "notes"), and a hyperlink to associated documents for the site (column name is "documents"). Since I have data for a few years at multiple sites, it is important to be able to show all data over that span. Thanks again! – Cameron Plese Sep 29 '15 at 15:52
  • Here is what it looks like so far. Below is a link to our website that has the map hosted on it: catis-mexico.org/water-quality-monitoring – Cameron Plese Sep 29 '15 at 15:55
  • I keep getting an error message reading "function string_agg (timestamp with time zone) does not exist" when I try this SQL. I have tried doing "date_agg" as well since the column type is "date", but that has resulted in the same error. – Cameron Plese Sep 30 '15 at 15:51

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.