<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Just to follow up on this, here is the code we (Abdel-Rahman M. Muhsen and I) finally got around to writing. We found a function called randompoint which just creates random points within the bounding box of the geometry from Alexandre Sorokine. <a href="http://sorokine.blogspot.com/2011/05/postgis-function-for-random-point.html">http://sorokine.blogspot.com/2011/05/postgis-function-for-random-point.html</a> (I see he's updated it 4 days ago to be better for multi-polygons! We don't have his updates yet.)<div><br></div><div><div>Then we wrote 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.)</div><div><br></div><div>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 <a href="http://lin-ear-th-inking.blogspot.com/2010/05/more-random-points-in-jts.html">http://lin-ear-th-inking.blogspot.com/2010/05/more-random-points-in-jts.html</a> . Other suggestions are welcomed.</div><div><br></div><div>This is how we typically use it:</div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"><br></span></font></div><div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">create or replace view dot_cnt as </span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">select the_geom, polygon_id, (quantity_column/1000000)::integer as numpoints from original_data;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"><br></span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">select dot_density('dot_cnt',</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">'the_geom',</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">'polygon_id',</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">'numpoints');</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"><br></span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">alter table dp rename to save_it_for_future_use;</span></font></div></div><div><br></div><div>And here are the functions:</div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px; "><br></span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"><br></span></font></div><div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">CREATE OR REPLACE FUNCTION randompoint(geom geometry)</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> RETURNS geometry AS</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">-- from Alexandre Sorokine</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">$BODY$</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">DECLARE</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> maxiter INTEGER := 1000;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> i INTEGER := 0;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> x0 DOUBLE PRECISION;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> dx DOUBLE PRECISION;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> y0 DOUBLE PRECISION;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> dy DOUBLE PRECISION;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> xp DOUBLE PRECISION;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> yp DOUBLE PRECISION;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> rpoint Geometry;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">BEGIN</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> -- find envelope</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> x0 = ST_XMin(geom);</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> dx = (ST_XMax(geom) - x0);</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> y0 = ST_YMin(geom);</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> dy = (ST_YMax(geom) - y0);</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> </span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> WHILE i < maxiter LOOP</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> i = i + 1;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> xp = x0 + dx * random();</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> yp = y0 + dy * random();</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> rpoint = ST_SetSRID( ST_MakePoint( xp, yp ), ST_SRID(geom) );</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> EXIT WHEN ST_Within( rpoint, geom );</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> END LOOP;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> </span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> IF i > maxiter THEN</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> RAISE NOTICE 'number of interations exceeded max';</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> END IF; </span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> </span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> RETURN rpoint;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">END; </span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">$BODY$</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> LANGUAGE 'plpgsql' VOLATILE</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> COST 100;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">ALTER FUNCTION randompoint(geometry) OWNER TO "usrPostgres";</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"><br></span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">-- Function: dot_density(text, text, text, text)</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"><br></span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">CREATE OR REPLACE FUNCTION dot_density(geom_table text, geom_col text, zone_col text, num_of_points_col text)</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> RETURNS SETOF record AS</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">$BODY$</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">DECLARE </span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> counter integer:=0;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> tazrec record;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> pointrec record;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> result record;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"><br></span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> num_points integer:=0;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> np integer :=0; </span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">begin</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"><br></span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">DROP sequence if exists randpnt_id;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">CREATE sequence randpnt_id;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"><br></span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">DROP TABLE IF EXISTS dp;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">CREATE TABLE dp</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">(</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> gid integer PRIMARY KEY,</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> ser integer,</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> "zone" integer,</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> decrease_or_increase integer,</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> the_geom geometry</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">); </span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"><br></span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">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</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> RAISE INFO 'Treating zone: %' , tazrec.zone_col;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> num_points = tazrec.num_of_points_col;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"><br></span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> IF num_points !=0 THEN np := num_points/abs(num_points);</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> ELSE np=0;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> END IF;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> </span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> </span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"><br></span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> 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 ;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> </span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"><br></span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">END LOOP;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"><br></span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">RETURN ; </span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">end;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">$BODY$</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> LANGUAGE plpgsql VOLATILE</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> COST 100</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"> ROWS 1000;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;">ALTER FUNCTION dot_density(text, text, text, text) OWNER TO postgres;</span></font></div><div><font class="Apple-style-span" face="Courier" size="2"><span class="Apple-style-span" style="font-size: 10px;"><br></span></font></div><div><br></div><div><div>On 2010-05-06, at 3:10 AM, strk wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div>ST_RandomPoinsOnSurface(geometry, numpoints) would be an interesting<br>function indeed. Sounds like a good job for GEOS/JTS.<br><br>--strk;<br><br>On Mon, May 03, 2010 at 10:49:32PM -0600, John Abraham wrote:<br><blockquote type="cite">One of the things I miss about using ESRI's GIS is the ability to do dot-density maps. Within a polygon, the number of dots is proportional to a value, and the dots are randomly placed. I find it useful to be able to present several data values at once (e.g. blue dots for population, red dots for employment). <br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">I also find that it is a more intuitive way of scaling for zone size than dividing the value by the area of the zone. That is, the count of the dots represents the actual number, but the density of the dots represents the density of the number. So I don't have to decide whether to divide the value by the area of the polygon to plot density: both the absolute number and the density are easily visible.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">Since my open-source GIS viewing systems (mostly QGIS and Mapserver) won't plot dot-density, I've done without.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">But today I realized that I can build these on the server instead. I can generate random points within the bounding-box of the polygon, throwing out those that aren't contained within the polygon, repeating until I have enough. Then I can save these points as a separate layer, and display this layer using almost any desktop or web based viewer!<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">Has anyone done this? Can I do it in SQL or do I need to write something in PL/pgsql?<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">--<br></blockquote><blockquote type="cite">John Abraham<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">PS I just bought the Postgis In Action book; enjoying it so far.<br></blockquote><blockquote type="cite">_______________________________________________<br></blockquote><blockquote type="cite">postgis-users mailing list<br></blockquote><blockquote type="cite"><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br></blockquote><blockquote type="cite"><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></blockquote><br>-- <br><br> () Free GIS & Flash consultant/developer<br> /\ <a href="http://strk.keybit.net/services.html">http://strk.keybit.net/services.html</a><br><br><br></div></blockquote></div><br><div>
<div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; font-size: medium; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; font-size: medium; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div><div>-- <br>John Abraham<br>This email address is being phased out, please update your records to use <a href="mailto:john@theabrahams.ca">john@theabrahams.ca</a> for personal email and <a href="mailto:jea@hbaspecto.com">jea@hbaspecto.com</a> for work email.<br><br></div></div></div></span></div></span></div>
</div>
<br></div></div></body></html>