Changes between Initial Version and Version 1 of UserWikiDotDensity

Timestamp:

Jun 2, 2011, 4:46:43 PM (13 years ago)

Author:

amuhsen

Comment:

—

UserWikiDotDensity

@@ +1 @@
+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 [wiki:UserWikiRandomPoint 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 [http://lin-ear-th-inking.blogspot.com/2010/05/more-random-points-in-jts.html JTS].  Other suggestions are welcomed.
+
+This is how we typically use it:
+{{{
+#!sql 
+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:
+
+{{{
+#!sql
+-- 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
+}}}