wiki:UserWikiDotDensity

This is a function called dot_density which creates a table called dp which has a point geometry for each point in the polygon. If there are negative numbers in the point count, it creates the positive number of points but flags them in another column. (We often plot dot density maps with red dots for decreases and blue dots for increases.) This function utilizes the RandomPoint function.

There are still a few improvements we'll likely make. We'd like to create the geometry column using the addgeometrycolumn function. We'd like to specify the output table name as a function parameter. There was also a good idea to use Halton sequences or other "pseudo-random" sequences, instead of truly random points, and Martin Davis implemented a few of the other "pseudo-random" ideas in JTS. Other suggestions are welcomed.

This is how we typically use it:

CREATE OR REPLACE VIEW dot_cnt as 
SELECT the_geom, polygon_id, (quantity_column/1000000)::integer AS numpoints FROM original_data;

SELECT dot_density('dot_cnt',
'the_geom',
'polygon_id',
'numpoints');

ALTER TABLE dp RENAME TO save_it_for_future_use;

And here are the functions:

-- 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)
  RETURNS SETOF record AS 
$BODY$
DECLARE 
    counter integer:=0;
    tazrec record;
    pointrec record;
    result record;
    num_points integer:=0;
    np integer :=0; 
BEGIN

DROP SEQUENCE if exists randpnt_id;
CREATE SEQUENCE randpnt_id;

DROP TABLE IF EXISTS dp;
CREATE TABLE dp(
  gid integer PRIMARY KEY,
  ser integer,
  "zone" integer,
  decrease_or_increase integer,
  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 := num_points/abs(num_points);
    ELSE 
        np=0;
    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
Last modified 6 years ago Last modified on Jun 2, 2011 4:46:43 PM