<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0in;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
.MsoChpDefault
{mso-style-type:export-only;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="2050" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal>Hi All,<o:p></o:p></p>
<p class=MsoNormal> I’m
trying to find the problem with my logic in the following query. I want
to randomize a point, and neither place it too close or too far from the
initial point. I’d like to further constrain the placement of that
point so that it stays within certain bounds. Imagine, if you will
sensitive point data that might need a little fuzzing—1) I don’t
want to fuzz it too much (outer circle), 2) I don’t want it too close to
the original point (inner circle) 3) but I want it to also stay within certain
boundaries in order to retain some verisimilitude, e.g. must stay within forest
boundaries.<o:p></o:p></p>
<p class=MsoNormal style='text-indent:.5in'>I’ve tested this in PostGIS 1.3.5
on a Windows Server 2003 machine (postgres 8.3), and in PostGIS 2.0 on Debian (with
postgres 8.4).<o:p></o:p></p>
<p class=MsoNormal style='text-indent:.5in'>In general, it works really well.
Occasionally, I get a point that seems to meet my first to criteria but not my
third<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><b>Here’s a few hundred of the same point fuzzed:<o:p></o:p></b></p>
<p class=MsoNormal><img width=347 height=248 id="Picture_x0020_1"
src="cid:image002.png@01CC93C8.BFA9CBA0"><o:p></o:p></p>
<p class=MsoNormal><b>Function:<o:p></o:p></b></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>CREATE OR REPLACE FUNCTION ST_Donut<o:p></o:p></p>
<p class=MsoNormal> (
geom GEOMETRY, --
GEOMETRY point to be processed<o:p></o:p></p>
<p class=MsoNormal> dough
DOUBLE PRECISION, --
Outside ring<o:p></o:p></p>
<p class=MsoNormal> nut
DOUBLE PRECISION, --
Inside ring<o:p></o:p></p>
<p class=MsoNormal> itermax
INTEGER, --
Maximum number of iterations<o:p></o:p></p>
<p class=MsoNormal> bite
GEOMETRY) --POLYGON
that randomized point must intersect<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal> RETURNS
GEOMETRY --
Returns the randomized point<o:p></o:p></p>
<p class=MsoNormal> AS
$$<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal> DECLARE<o:p></o:p></p>
<p class=MsoNormal> xmin
DOUBLE PRECISION; --
xmin for envelope of donut<o:p></o:p></p>
<p class=MsoNormal> ymin
DOUBLE PRECISION; --
ymin for envelope of donut<o:p></o:p></p>
<p class=MsoNormal> delta
DOUBLE PRECISION; --
difference between xmax and xmin<o:p></o:p></p>
<p class=MsoNormal> pointx
DOUBLE PRECISION; --
output point x<o:p></o:p></p>
<p class=MsoNormal> pointy
DOUBLE PRECISION; --
output point y<o:p></o:p></p>
<p class=MsoNormal> returnpoint
GEOMETRY;<o:p></o:p></p>
<p class=MsoNormal> i
INTEGER := 0; --
index integer for while loop<o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal> BEGIN<o:p></o:p></p>
<p class=MsoNormal> xmin
= ST_X( geom ) - dough; -- find min x, min y for resulting
envelope<o:p></o:p></p>
<p class=MsoNormal> ymin
= ST_Y( geom ) - dough; -- in which to place initial
points<o:p></o:p></p>
<p class=MsoNormal> delta
= dough*2; --
for calculating max extent for envelope<o:p></o:p></p>
<p class=MsoNormal> WHILE
i < itermax LOOP<o:p></o:p></p>
<p class=MsoNormal> i
= i + 1;<o:p></o:p></p>
<p class=MsoNormal> pointx
= xmin + delta * random(); --
generate random point value<o:p></o:p></p>
<p class=MsoNormal> pointy
= ymin + delta * random();<o:p></o:p></p>
<p class=MsoNormal> returnpoint
= ST_SetSRID( ST_MakePoint( pointx, pointy ), ST_SRID( geom ) );<o:p></o:p></p>
<p class=MsoNormal> EXIT
WHEN<o:p></o:p></p>
<p class=MsoNormal> ST_DWithin(
returnpoint, geom, dough ) --
test to see if inside outer ring<o:p></o:p></p>
<p class=MsoNormal> AND
NOT<o:p></o:p></p>
<p class=MsoNormal> ST_DWithin(
returnpoint, geom, nut ) --
test to see if outside inner ring<o:p></o:p></p>
<p class=MsoNormal> AND<o:p></o:p></p>
<p class=MsoNormal> ST_Intersects(
returnpoint, bite ) --test
to see if inside inclusion polygon<o:p></o:p></p>
<p class=MsoNormal> ;<o:p></o:p></p>
<p class=MsoNormal> END
LOOP;<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal> IF
i > itermax THEN<o:p></o:p></p>
<p class=MsoNormal> RAISE
NOTICE 'Reached maximum iterations without placing point inside donut.';<o:p></o:p></p>
<p class=MsoNormal> END
IF;<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal> RETURN
returnpoint; -- return the GEOMETRY<o:p></o:p></p>
<p class=MsoNormal> END;<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>$$ LANGUAGE plpgsql;<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><b>To run:<o:p></o:p></b></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>CREATE TABLE donut_bite_test1 AS<o:p></o:p></p>
<p class=MsoNormal style='text-indent:.5in'>SELECT ST_Donut(ST_MakePoint(0, 0),
40, 60, 1000, ST_Buffer(ST_MakePoint(-60, -60), 60))<o:p></o:p></p>
<p class=MsoNormal style='margin-left:.5in;text-indent:.5in'>UNION ALL<o:p></o:p></p>
<p class=MsoNormal style='text-indent:.5in'>SELECT ST_Donut(ST_MakePoint(0, 0),
60, 40, 1000, ST_Buffer(ST_MakePoint(-60, -60), 60))<o:p></o:p></p>
<p class=MsoNormal style='margin-left:.5in;text-indent:.5in'>UNION ALL<o:p></o:p></p>
<p class=MsoNormal style='text-indent:.5in'><i>Etc. etc.<o:p></o:p></i></p>
<p class=MsoNormal style='text-indent:.5in'>SELECT ST_Donut(ST_MakePoint(0, 0),
60, 40, 1000, ST_Buffer(ST_MakePoint(-60, -60), 60))<o:p></o:p></p>
<p class=MsoNormal style='text-indent:.5in'>;<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Best,<o:p></o:p></p>
<p class=MsoNormal>Steve<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><!--[if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600"
o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f"
stroked="f">
<v:stroke joinstyle="miter" />
<v:formulas>
<v:f eqn="if lineDrawn pixelLineWidth 0" />
<v:f eqn="sum @0 1 0" />
<v:f eqn="sum 0 0 @1" />
<v:f eqn="prod @2 1 2" />
<v:f eqn="prod @3 21600 pixelWidth" />
<v:f eqn="prod @3 21600 pixelHeight" />
<v:f eqn="sum @0 0 1" />
<v:f eqn="prod @6 1 2" />
<v:f eqn="prod @7 21600 pixelWidth" />
<v:f eqn="sum @8 21600 0" />
<v:f eqn="prod @7 21600 pixelHeight" />
<v:f eqn="sum @10 21600 0" />
</v:formulas>
<v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect" />
<o:lock v:ext="edit" aspectratio="t" />
</v:shapetype><v:shape id="cmp-ms-90x122.png" o:spid="_x0000_s1026" type="#_x0000_t75"
alt="http://www.clemetparks.com/images/esig/cmp-ms-90x122.png" style='position:absolute;
margin-left:1.5pt;margin-top:0;width:67.5pt;height:91.5pt;z-index:1;
visibility:visible;mso-wrap-style:square;mso-wrap-distance-left:9pt;
mso-wrap-distance-top:0;mso-wrap-distance-right:9pt;
mso-wrap-distance-bottom:0;mso-position-horizontal:absolute;
mso-position-horizontal-relative:text;mso-position-vertical:absolute;
mso-position-vertical-relative:text'>
<v:imagedata src="cid:image001.png@01CC93C9.EBDBEC70" o:href="http://www.clemetparks.com/images/esig/cmp-ms-90x122.png" />
<w:wrap type="square"/>
</v:shape><![endif]--><![if !vml]><img width=90 height=122
src="cid:image001.png@01CC93C9.EBDBEC70" align=left hspace=12
alt="http://www.clemetparks.com/images/esig/cmp-ms-90x122.png" v:shapes="cmp-ms-90x122.png"><![endif]><span
style='font-size:14.0pt;font-family:"Arial","sans-serif";color:#006C56'>Stephen
Mather<br>
</span><span style='font-family:"Arial","sans-serif";color:#006C56'>Geographic
Information Systems (GIS) Manager<br>
</span><span style='font-size:9.0pt;font-family:"Arial","sans-serif";
color:#006C56'>(216) 635-3243<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:9.0pt;font-family:"Arial","sans-serif";
color:#006C56'>svm@clevelandmetroparks.com<br>
</span><a href="http://www.clemetparks.com/"><span style='font-size:9.0pt;
font-family:"Arial","sans-serif"'>clevelandmetroparks.com</span></a><o:p></o:p></p>
</div>
</body>
</html>