Skip to content

Instantly share code, notes, and snippets.

@ramiroaznar
Created October 7, 2016 07:50
Show Gist options
  • Save ramiroaznar/9afe6c0e881e9adc6a5415cc9fbeaf4e to your computer and use it in GitHub Desktop.
Save ramiroaznar/9afe6c0e881e9adc6a5415cc9fbeaf4e to your computer and use it in GitHub Desktop.
Stacking Chips SQL query
WITH
m AS (
SELECT
array_agg(cartodb_id) id_list,
the_geom_webmercator,
ST_Y(the_geom_webmercator) y
FROM table_name
GROUP BY the_geom_webmercator
ORDER BY y DESC
),
f AS (
SELECT
generate_series(1, array_length(id_list,1)) p,
unnest(id_list) cartodb_id,
the_geom_webmercator
FROM m
)
SELECT
ST_Translate(f.the_geom_webmercator,0,f.p*50) the_geom_webmercator, --50 is the offset parameter
f.cartodb_id --add here the rest of your fields
FROM f, table_name q
WHERE f.cartodb_id = q.cartodb_id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment