fitnr · November 8, 2016 16:20
diff --git a/README.md b/README.md
diff --git a/dot_density.sql b/dot_density.sql
 CREATE OR REPLACE FUNCTION cdb_dot_density(g geometry , no_points Integer, max_iter Integer DEFAULT 1000 )
   RETURNS setof geometry AS
 $$
 DECLARE  
     extent GEOMETRY;
     test_point Geometry;
     width    NUMERIC;
     height   NUMERIC;
     x0       NUMERIC;
     y0       NUMERIC;
     xp       NUMERIC;
     yp       NUMERIC;
  no_left  INTEGER;
     points   GEOMETRY[];
 BEGIN       
   extent  := ST_Envelope(g);
   width   := ST_XMax(extent) - ST_XMIN(extent);
   height  := ST_YMax(extent) - ST_YMIN(extent);
   x0    := ST_XMin(extent);
   y0    := ST_YMin(extent);
   no_left := no_points;
   
   LOOP 
     if(no_left=0) THEN
       EXIT;
     END IF;
     
     xp = x0 + width*random();
     yp = y0 + height*random();
     test_point = CDB_LATLNG(yp,xp);
     
     IF(ST_Contains(g, test_point)) THEN
       no_left = no_left - 1;
       RETURN NEXT test_point;
     END IF;
   END LOOP;
  
  
 END
 $$
 LANGUAGE plpgsql VOLATILE
diff --git a/query.sql b/query.sql
 CREATE TABLE ADD_white AS (
    WITH one AS (
        SELECT pluto.the_geom,
            white_noth,
            asian_noth,
            black_noth,
            hl,
            LT_201_Race.cartodb_id,
            -- resarea,
            -- ST_AREA(
            --     ST_INTERSECTION(pluto.the_geom, LT_201_Race.the_geom)
            -- ) / ST_AREA(pluto.the_geom) AS area_frac,
            ST_AREA(
                ST_INTERSECTION(pluto.the_geom, LT_201_Race.the_geom)
            ) / ST_AREA(pluto.the_geom) * resarea AS live_area
        FROM LT_201_Race,
            nyc_pluto_15v1_resonly AS pluto
        WHERE ST_INTERSECTS(pluto.the_geom, LT_201_Race.the_geom)
    ),
    totals AS (
        SELECT
            SUM(live_area) AS total_live_area,
            cartodb_id
        FROM one
        GROUP BY cartodb_id
    ),
    fracs AS (
        SELECT totals.total_live_area,
            one.*,
            one.live_area / totals.total_live_area AS frac
        FROM one,
            totals
        WHERE totals.cartodb_id = one.cartodb_id
    )
    SELECT
        weighted_DD(
            array_agg(the_geom),
            min(white_noth)::NUMERIC,
            array_agg(frac)::NUMERIC[]
        ) AS the_geom
    FROM fracs
    GROUP BY cartodb_id
 )
diff --git a/select_random_weights.sql b/select_random_weights.sql
 create or replace function select_random_weights(array_ids numeric[], weights numeric[]) returns NUMERIC
 as $$
 DECLARE
     result NUMERIC;
 BEGIN
 
     WITH idw as (
         select unnest(array_ids) as id, unnest(weights) as percent
     ),
     CTE AS (
         SELECT random() * (SELECT SUM(percent) FROM idw) R
     )
     SELECT *
     FROM (
         SELECT id, SUM(percent) OVER (ORDER BY id) S, R
         FROM idw as percent CROSS JOIN CTE
     ) Q
     WHERE S >= R
     ORDER BY id
     LIMIT 1
     into result;
     return result;
 END
 $$ LANGUAGE plpgsql
diff --git a/weighted_dot_density.sql b/weighted_dot_density.sql
 CREATE OR REPLACE FUNCTION stuartlynn.weighted_dd(geoms geometry[], no_points numeric, weights numeric[])
 RETURNS SETOF geometry
 LANGUAGE plpgsql
 AS $function$
 DECLARE 
     i NUMERIC;
     ids NUMERIC[];
     selected_poly NUMERIC;
     p GEOMETRY;
 BEGIN
     with idseries as (
         select generate_series(1,array_upper(geoms,1)) as id      
     )    
     select array_agg( id) from idseries into ids;
 
 
     FOR i in 1..no_points
     LOOP
         select select_random_weights(ids, weights) into selected_poly;
         select cdb_dot_density(geoms[selected_poly], 1) into p;
         return next p;
     END LOOP;
 END
 
 
 $function$
	CREATE OR REPLACE FUNCTION cdb_dot_density(g geometry , no_points Integer, max_iter Integer DEFAULT 1000 )
	RETURNS setof geometry AS
	$$
	DECLARE
	extent GEOMETRY;
	test_point Geometry;
	width NUMERIC;
	height NUMERIC;
	x0 NUMERIC;
	y0 NUMERIC;
	xp NUMERIC;
	yp NUMERIC;
	no_left INTEGER;
	points GEOMETRY[];
	BEGIN
	extent := ST_Envelope(g);
	width := ST_XMax(extent) - ST_XMIN(extent);
	height := ST_YMax(extent) - ST_YMIN(extent);
	x0 := ST_XMin(extent);
	y0 := ST_YMin(extent);
	no_left := no_points;

	LOOP
	if(no_left=0) THEN
	EXIT;
	END IF;

	xp = x0 + width*random();
	yp = y0 + height*random();
	test_point = CDB_LATLNG(yp,xp);

	IF(ST_Contains(g, test_point)) THEN
	no_left = no_left - 1;
	RETURN NEXT test_point;
	END IF;
	END LOOP;


	END
	$$
	LANGUAGE plpgsql VOLATILE
	CREATE TABLE ADD_white AS (
	WITH one AS (
	SELECT pluto.the_geom,
	white_noth,
	asian_noth,
	black_noth,
	hl,
	LT_201_Race.cartodb_id,
	-- resarea,
	-- ST_AREA(
	-- ST_INTERSECTION(pluto.the_geom, LT_201_Race.the_geom)
	-- ) / ST_AREA(pluto.the_geom) AS area_frac,
	ST_AREA(
	ST_INTERSECTION(pluto.the_geom, LT_201_Race.the_geom)
	) / ST_AREA(pluto.the_geom) * resarea AS live_area
	FROM LT_201_Race,
	nyc_pluto_15v1_resonly AS pluto
	WHERE ST_INTERSECTS(pluto.the_geom, LT_201_Race.the_geom)
	),
	totals AS (
	SELECT
	SUM(live_area) AS total_live_area,
	cartodb_id
	FROM one
	GROUP BY cartodb_id
	),
	fracs AS (
	SELECT totals.total_live_area,
	one.*,
	one.live_area / totals.total_live_area AS frac
	FROM one,
	totals
	WHERE totals.cartodb_id = one.cartodb_id
	)
	SELECT
	weighted_DD(
	array_agg(the_geom),
	min(white_noth)::NUMERIC,
	array_agg(frac)::NUMERIC[]
	) AS the_geom
	FROM fracs
	GROUP BY cartodb_id
	)
	create or replace function select_random_weights(array_ids numeric[], weights numeric[]) returns NUMERIC
	as $$
	DECLARE
	result NUMERIC;
	BEGIN

	WITH idw as (
	select unnest(array_ids) as id, unnest(weights) as percent
	),
	CTE AS (
	SELECT random() * (SELECT SUM(percent) FROM idw) R
	)
	SELECT *
	FROM (
	SELECT id, SUM(percent) OVER (ORDER BY id) S, R
	FROM idw as percent CROSS JOIN CTE
	) Q
	WHERE S >= R
	ORDER BY id
	LIMIT 1
	into result;
	return result;
	END
	$$ LANGUAGE plpgsql
	CREATE OR REPLACE FUNCTION stuartlynn.weighted_dd(geoms geometry[], no_points numeric, weights numeric[])
	RETURNS SETOF geometry
	LANGUAGE plpgsql
	AS $function$
	DECLARE
	i NUMERIC;
	ids NUMERIC[];
	selected_poly NUMERIC;
	p GEOMETRY;
	BEGIN
	with idseries as (
	select generate_series(1,array_upper(geoms,1)) as id
	)
	select array_agg( id) from idseries into ids;


	FOR i in 1..no_points
	LOOP
	select select_random_weights(ids, weights) into selected_poly;
	select cdb_dot_density(geoms[selected_poly], 1) into p;
	return next p;
	END LOOP;
	END


	$function$