Back to Parent

SQL

WITH 
  -- group points with identical point geometries
  -- sort groups by their y-axis location so they will be rendered top to bottom
  m AS (
    SELECT array_agg(cartodb_id) id_list, the_geom_webmercator, ST_Y(the_geom_webmercator) y 
    FROM paulmoscosoriofrio.garfield_color4_dataset
    GROUP BY the_geom_webmercator 
    ORDER BY y DESC
  ),
  -- turn the groups of points back into individual rows with a number 
  f AS (
    SELECT  generate_series(1, array_length(id_list,1)) p, unnest(id_list) cartodb_id, the_geom_webmercator 
    FROM m
  )
 -- replace geometries join with original dataset on cartodb_id to get the attributes
SELECT  ST_Translate(f.the_geom_webmercator,0,f.p*12) the_geom_webmercator, f.cartodb_id, q.dominant_color_hex, q.hash_tags, q.liked_by, q.display_url, q.location_name, q.taken_at, q.caption, q.color_str
FROM f, paulmoscosoriofrio.garfield_color4_dataset q 
WHERE f.cartodb_id = q.cartodb_id
Click to Expand

Content Rating

Is this a good/useful/informative piece of content to include in the project? Have your say!

0