Skip to content

Instantly share code, notes, and snippets.

@andrewxhill
Created March 19, 2012 14:50
Show Gist options
  • Save andrewxhill/2115041 to your computer and use it in GitHub Desktop.
Save andrewxhill/2115041 to your computer and use it in GitHub Desktop.
Create a density grid from geometries. For use in CartoDB
CREATE TYPE CDB_DensityGridPair AS (
geom GEOMETRY,
val FLOAT
);
CREATE OR REPLACE FUNCTION CDB_DensityGrid(table_name TEXT, geom_name TEXT, dimension FLOAT)
RETURNS SETOF CDB_DensityGridPair AS $$
DECLARE
BEGIN
RETURN QUERY
EXECUTE 'SELECT ST_Envelope(GEOMETRYFROMTEXT(''LINESTRING(''||(st_xmax(geom)+(seed/2))||'' ''||(st_ymax(geom)+(seed/2))||'', ''||(st_xmin(geom)-(seed/2))||'' ''||(st_ymin(geom)-(seed/2))||'')'',3857)) as geom, val FROM (SELECT '||dimension||' as seed, count(*)::float as val,
st_snaptogrid('||geom_name||',0,0,'||dimension||','||dimension||') as geom FROM '||table_name||' t GROUP BY st_snaptogrid('||geom_name||',0,0,'||dimension||','||dimension||')) k'
RETURN;
END;
$$ language 'plpgsql';
--SELECT CDB_DensityGrid('import_csv_2','the_geom_webmercator',5000);
CREATE OR REPLACE FUNCTION CDB_DensityGrid(table_name TEXT, geom_name TEXT, dimension FLOAT, method TEXT, method_column TEXT)
RETURNS SETOF CDB_DensityGridPair AS $$
DECLARE
BEGIN
IF method = 'MAX' THEN
RETURN QUERY
EXECUTE 'SELECT ST_Envelope(GEOMETRYFROMTEXT(''LINESTRING(''||(st_xmax(geom)+(seed/2))||'' ''||(st_ymax(geom)+(seed/2))||'', ''||(st_xmin(geom)-(seed/2))||'' ''||(st_ymin(geom)-(seed/2))||'')'',3857)) as geom, val FROM (SELECT '||dimension||' as seed, max('||method_column||')::float as val,
st_snaptogrid('||geom_name||',0,0,'||dimension||','||dimension||') as geom FROM '||table_name||' t GROUP BY st_snaptogrid('||geom_name||',0,0,'||dimension||','||dimension||')) k'
RETURN;
ELSIF method = 'MIN' THEN
RETURN QUERY
EXECUTE 'SELECT ST_Envelope(GEOMETRYFROMTEXT(''LINESTRING(''||(st_xmax(geom)+(seed/2))||'' ''||(st_ymax(geom)+(seed/2))||'', ''||(st_xmin(geom)-(seed/2))||'' ''||(st_ymin(geom)-(seed/2))||'')'',3857)) as geom, val FROM (SELECT '||dimension||' as seed, min('||method_column||')::float as val,
st_snaptogrid('||geom_name||',0,0,'||dimension||','||dimension||') as geom FROM '||table_name||' t GROUP BY st_snaptogrid('||geom_name||',0,0,'||dimension||','||dimension||')) k'
RETURN;
ELSIF method = 'SUM' THEN
RETURN QUERY
EXECUTE 'SELECT ST_Envelope(GEOMETRYFROMTEXT(''LINESTRING(''||(st_xmax(geom)+(seed/2))||'' ''||(st_ymax(geom)+(seed/2))||'', ''||(st_xmin(geom)-(seed/2))||'' ''||(st_ymin(geom)-(seed/2))||'')'',3857)) as geom, val FROM (SELECT '||dimension||' as seed, sum('||method_column||')::float as val,
st_snaptogrid('||geom_name||',0,0,'||dimension||','||dimension||') as geom FROM '||table_name||' t GROUP BY st_snaptogrid('||geom_name||',0,0,'||dimension||','||dimension||')) k'
RETURN;
ELSIF method = 'AVG' THEN
RETURN QUERY
EXECUTE 'SELECT ST_Envelope(GEOMETRYFROMTEXT(''LINESTRING(''||(st_xmax(geom)+(seed/2))||'' ''||(st_ymax(geom)+(seed/2))||'', ''||(st_xmin(geom)-(seed/2))||'' ''||(st_ymin(geom)-(seed/2))||'')'',3857)) as geom, val FROM (SELECT '||dimension||' as seed, avg('||method_column||')::float as val,
st_snaptogrid('||geom_name||',0,0,'||dimension||','||dimension||') as geom FROM '||table_name||' t GROUP BY st_snaptogrid('||geom_name||',0,0,'||dimension||','||dimension||')) k'
RETURN;
END IF;
END;
$$ language 'plpgsql';
-- SELECT CDB_DensityGrid('import_csv_2','the_geom_webmercator',5000, 'MAX', 'views');
-- SELECT CDB_DensityGrid('import_csv_2','the_geom_webmercator',5000, 'MIN', 'views');
-- SELECT CDB_DensityGrid('import_csv_2','the_geom_webmercator',5000, 'SUM', 'views');
-- SELECT CDB_DensityGrid('import_csv_2','the_geom_webmercator',5000, 'AVG', 'views');
--An example query on CartoDB
--SELECT (dg).geom as the_geom_webmercator, (dg).val as cuisine FROM (SELECT CDB_DensityGrid('ny_restaurants_ratings_dg','the_geom_webmercator',50, 'MAX', 'cuisine') as dg) f;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment