Skip to content

Instantly share code, notes, and snippets.

@mhkeller
Created November 4, 2013 04:43
Show Gist options
  • Save mhkeller/7298180 to your computer and use it in GitHub Desktop.
Save mhkeller/7298180 to your computer and use it in GitHub Desktop.
The PostGIS query powering the Syrian refugee map http://projects.aljazeera.com/2013/syrias-refugees/index.html
-- Census tract population is column dp0010001
WITH op AS
(SELECT the_geom,
cartodb_id,
dp0010001,
Row_number() OVER (
ORDER BY Cdb_latlng(/* Starting latitude */, /* Starting longitude */) <-> the_geom) AS row_number
FROM /* Table name */ LIMIT /* Max census tracts queryable */),
sm AS
(SELECT the_geom,
cartodb_id,
dp0010001,
Sum(dp0010001) OVER (
ORDER BY row_number ASC) AS running_total
FROM op LIMIT /* Max census tracts queryable */)
SELECT ST_AsGeoJson(ST_Union(the_geom)) AS geometry,
'children' AS vector_group
FROM sm
WHERE running_total < /* Number of child refugees */
UNION ALL
SELECT ST_AsGeoJson(ST_Union(the_geom)) AS geometry,
'adults' AS vector_group
FROM sm
WHERE running_total >= /* Number of child refugees */
AND running_total < /* Number of child and adult refugees */
UNION ALL
SELECT ST_AsGeoJson(ST_Union(the_geom)) AS geometry,
'idp' AS vector_group
FROM sm
WHERE running_total >= /* Number of child and adult refugees */
AND running_total < /* Number of child and adult refugees and internally displaced people */
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment