Skip to content

Instantly share code, notes, and snippets.

@stuartlynn
Last active March 1, 2018 19:25
Show Gist options
  • Save stuartlynn/92b0d473177b850ec876181cde5170e6 to your computer and use it in GitHub Desktop.
Save stuartlynn/92b0d473177b850ec876181cde5170e6 to your computer and use it in GitHub Desktop.
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
)
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 dot_density(geoms[selected_poly], 1) into p;
return next p;
END LOOP;
END
$function$
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment