Create a density grid from geometries. For use in CartoDB

density_grid.sql

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;