query.sql

create table result as (

 with one as (
     SELECT nyc_pluto_15v1_resonly.the_geom,
            white_noth,
            asian_noth,
            black_noth,
            hl,
            lt_12_race.cartodb_id,
            resarea,
            ST_AREA(ST_INTERSECTION(nyc_pluto_15v1_resonly.the_geom, lt_12_race.the_geom))/ST_AREA(nyc_pluto_15v1_resonly.the_geom) as area_frac,
            ST_AREA(ST_INTERSECTION(nyc_pluto_15v1_resonly.the_geom, lt_12_race.the_geom))/ST_AREA(nyc_pluto_15v1_resonly.the_geom)*resarea as live_area
     FROM lt_12_race,
         nyc_pluto_15v1_resonly
     WHERE ST_INTERSECTS(nyc_pluto_15v1_resonly.the_geom, lt_12_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
)

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

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 dot_density(geoms[selected_poly], 1) into p;
         return next p;
     END LOOP;
 END
 
 
 $function$