2

I'm mapping thousands of concert performances by Iron Maiden on a Carto map using Carto Editor. I was researching solutions to displaying multiple entries on the same point and came across the "stacking chips" solution as shared by Carto.

Using the answer to this related question on Stack Overflow, I have applied the SQL and get it to display and it looks great.

However, I would like to re-oder the chips in the columns to display in order of when the concerts happened, so the most recent concerts would be at the top of the stacks, the earliest at the bottom.

My map can be found here.

Here's my SQL query:

WITH 

data as (

SELECT 

cartodb_id, 

ST_SnapToGrid(the_geom,.5) as the_geom

FROM iron_maiden

),

m AS (

SELECT array_agg(cartodb_id) id_list, the_geom, ST_Y(the_geom) y 

FROM data 

GROUP BY the_geom

ORDER BY y DESC

),

f AS (

SELECT  generate_series(1, array_length(id_list,1)) p, unnest(id_list) cartodb_id, the_geom 

FROM m

)

SELECT      

ST_Transform(ST_Translate(

  f.the_geom,

  0,

  f.p*.22

),3857) the_geom_webmercator, 

f.cartodb_id, 

q.Header,
q.Date,
q.Tour,
q.City,
q.Country,
q.Venue,
q.Long,
q.Lat,
q.Wikipedia,
q.Official_website,
q.Footer

FROM f, iron_maiden q

WHERE f.cartodb_id = q.cartodb_id 

Apologies if this is a simple question, but i'm not a programmer!

1

You can add ORDER BY q.Date at the end of your query, and ASC or DESC depending on your choose. Adding this to the subquery should also work.

  • Thanks but that doesn't quite work - the stacking seems changes - I think it groups by venue, rather than multiple venues in the same locality which is what was happening before. – James99 Mar 14 '17 at 19:10
  • I understood that you needed to order the chips based on your date column. Creating stacking chips grouped by a column value is another question. By the way, there is an updated version (with a CartoCSS) for making stacking chips. – ramiroaznar Mar 15 '17 at 14:32
  • Many thanks for sending through the updated version - I've now applied that. To be clear I do want the entries order by date, which is a column in my table. But the issue remains with this new version. When I apply ORDER BY t.date then the columns don't look the same or are as easy on the eye I think that this helps to explain it... Before sorting by date: ultraimg.com/images/2017/03/15/0bLz.jpg After sorting by date: ultraimg.com/images/2017/03/15/0bLK.jpg – James99 Mar 15 '17 at 15:45
  • I see now your problem. Have a look at @Redoute answer. The key is to ordering within the subquery (also you need to transform from string to date). – ramiroaznar Mar 15 '17 at 16:13
1

Your date column is a string like '31 March 1985', so not directly sortable. I also replaced the array_agg/unnest/generate_series thing with the window function row_number() OVER (). Try this query:

WITH snap as (SELECT
    cartodb_id,
    Date,
    ST_SnapToGrid(the_geom, 0.5) as the_geom
    FROM iron_maiden),
sort as (SELECT
    cartodb_id, 
    row_number() OVER (PARTITION BY the_geom ORDER BY cast(Date as date)) as p,
    the_geom
    FROM snap)
SELECT
    q.cartodb_id, 
    q.Header,
    q.Date,
    q.Tour,
    q.City,
    q.Country,
    q.Venue,
    q.Long,
    q.Lat,
    q.Wikipedia,
    q.Official_website,
    q.Footer,
    ST_Transform(ST_Translate(s.the_geom, 0, s.p * 0.22), 3857) the_geom_webmercator
    FROM iron_maiden q, sort s
    WHERE s.cartodb_id = q.cartodb_id
    ORDER BY s.p;
  • Many thanks for taking the time with me, I do appreciate it! It's telling me that "sql/table must contain p variable" in the CSS editor pane. – James99 Mar 15 '17 at 16:17
  • Sorry, I can't see what you are trying to do. If you really need snap.p (that is the position number in a stack) in the final result, you can simply add s.p to the final SELECT. – Redoute Mar 15 '17 at 16:55
  • Updated my answer: Seeing your pictures I think the result set needs an ORDER BY too. – Redoute Mar 15 '17 at 17:01

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.