-- Function: randompoint(geometry)

-- DROP FUNCTION randompoint(geometry);

CREATE OR REPLACE FUNCTION randompoint(geom geometry)
  RETURNS geometry AS
$BODY$
DECLARE
 maxiter INTEGER := 1000;
 i INTEGER := 0;
 x0 DOUBLE PRECISION;
 dx DOUBLE PRECISION;
 y0 DOUBLE PRECISION;
 dy DOUBLE PRECISION;
 xp DOUBLE PRECISION;
 yp DOUBLE PRECISION;
 rpoint Geometry;
BEGIN
 -- find envelope
 x0 = ST_XMin(geom);
 dx = (ST_XMax(geom) - x0);
 y0 = ST_YMin(geom);
 dy = (ST_YMax(geom) - y0);
 
 WHILE i < maxiter LOOP
  i = i + 1;
  xp = x0 + dx * random();
  yp = y0 + dy * random();
  rpoint = ST_SetSRID( ST_MakePoint( xp, yp ), ST_SRID(geom) );
  EXIT WHEN ST_Within( rpoint, geom );
 END LOOP;
 
 IF i > maxiter THEN
  RAISE NOTICE 'number of interations exceeded max';
 END IF; 
 
 RETURN rpoint;
END; 
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION randompoint(geometry) OWNER TO "usrPostgres";

-- Function: dot_density(text, text, text, text)

create or replace function dot_density(geom_table text, geom_col text, zone_col text, num_of_points_col text, total_points integer)
 RETURNS SETOF record AS
$BODY$
DECLARE
  total_positive double precision;
  total_negative double precision;
  ratio double precision;

begin

   EXECUTE 'SELECT SUM('|| num_of_points_col || ') FROM ' || geom_table || ' WHERE ' || num_of_points_col || ' >0;' INTO total_positive;
   EXECUTE 'SELECT SUM('|| num_of_points_col || ') FROM ' || geom_table || ' WHERE ' || num_of_points_col || ' <0;' INTO total_negative;

   total_positive = CASE when total_positive is null then 0 else total_positive end;
   total_negative = CASE when total_negative is null then 0 else total_negative end;

   ratio := CASE WHEN total_positive > -total_negative THEN total_points/total_positive
	ELSE -total_points/total_negative END;

RAISE INFO 'Value per dot is %', 1/ratio;
  EXECUTE 'DROP VIEW IF EXISTS q4dd; CREATE OR REPLACE VIEW q4dd as SELECT ' || geom_col ||', '||zone_col||', case when '|| num_of_points_col || ' is null then 0 else ('|| num_of_points_col||'*'||ratio|| ')::integer end as points from '||geom_table||';';
  PERFORM dot_density(1/ratio, 'q4dd', geom_col, zone_col, 'points');

RETURN;

end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
   

--DROP FUNCTION dot_density(double precision, text, text, text, text);


CREATE OR REPLACE FUNCTION dot_density(point_value double precision, geom_table text, geom_col text, zone_col text, num_of_points_col text)
  RETURNS SETOF record AS
$BODY$
DECLARE 
 counter integer:=0;
 tazrec record;
 pointrec record;
 result record;

 num_points integer:=0;
 np double precision :=0; 
begin

DROP sequence if exists randpnt_id;
CREATE sequence randpnt_id;

CREATE TABLE dp
(
  gid integer PRIMARY KEY,
  ser integer,
  "zone" integer,
  value double precision,
  the_geom geometry
); 

for tazrec in EXECUTE 'SELECT ' || zone_col || ' as geom_col , ' || zone_col || ' as zone_col, '|| num_of_points_col || ' as num_of_points_col FROM ' || geom_table Loop
 RAISE INFO 'Treating zone: %' , tazrec.zone_col;
 num_points = tazrec.num_of_points_col;


 IF num_points >= 0 THEN np := point_value;
 ELSE np := -point_value;
 END IF;
 
 

 EXECUTE 'INSERT INTO dp SELECT nextval(''randpnt_id'') as gid, generate_series, '|| tazrec.zone_col ||  ', ' || np ||' , randompoint(the_geom) FROM ' || geom_table || ', generate_series(1, '|| abs(num_points) ||') WHERE '|| zone_col || '='|| tazrec.zone_col ;
  

END LOOP;

RETURN ; 
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION dot_density(double precision, text, text, text, text) OWNER TO postgres;
/*


*/
