Gaussian Distribution for Dummy Data in CartoDB

sql.txt

/*
Given a table of census tracts, this query will generate a gaussian distribution of points offset from the tract's centroid.

Based on this example by Paul Ramsey: http://boundlessgeo.com/2014/10/postgis-training-creating-overlays/
*/
WITH tracts AS (
 SELECT *, random() AS u1, random() AS u2, 25 AS f, generate_series(1,total_pop/100) FROM chriswhong.nytracts 
)

SELECT 
  ST_SetSRID(ST_MakePoint(
     ST_X(ST_Centroid(the_geom)) + ((sqrt(-2 * ln(u1)) * cos(2*pi()*u2))/f),
     ST_Y(ST_Centroid(the_geom)) + ((sqrt(-2 * ln(u1)) * sin(2*pi()*u2))/f)),4326) AS the_geom,
  ST_Transform(
  ST_SetSRID(ST_MakePoint(
     ST_X(ST_Centroid(the_geom)) + ((sqrt(-2 * ln(u1)) * cos(2*pi()*u2))/f),
     ST_Y(ST_Centroid(the_geom)) + ((sqrt(-2 * ln(u1)) * sin(2*pi()*u2))/f)),4326),3857) AS the_geom_webmercator,
  total_pop
FROM tracts