<html><head></head><body><div class="ydp688cabceyahoo-style-wrap" style="font-family:verdana, helvetica, sans-serif;font-size:16px;"><div></div>
<div dir="ltr" data-setdir="false">Gotcha...</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">I'm glad it didn't really make sense to you either. That is sort of what I thought might be happening.<br></div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">Nice to know I'm not a totally lost cause!!!<br></div><div><br></div><div dir="ltr" data-setdir="false">That works perfectly,</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">Thanks heaps!!<br></div><div><br></div><div><br></div>
</div><div id="ydp4b316ddfyahoo_quoted_0708346464" class="ydp4b316ddfyahoo_quoted">
<div style="font-family:'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;color:#26282a;">
<div>
On Sunday, November 19, 2023 at 11:09:34 AM GMT+13, Regina Obe <lr@pcorp.us> wrote:
</div>
<div><br></div>
<div><br></div>
<div><div id="ydp4b316ddfyiv7486868869"><div><div class="ydp4b316ddfyiv7486868869WordSection1"><p class="ydp4b316ddfyiv7486868869MsoNormal">I’m guessing the reason why your example doesn’t work is because the planner is doing some serious short-circuiting cause it sees two subselects that are using no variables from the updated table. </p><p class="ydp4b316ddfyiv7486868869MsoNormal">I suspect this is a bug or some intentional stuff that makes no sense to me. Cause I’m pretty sure I’ve done something like UPDATE sometable SET geom = ST_GeneratePoints(somestaticgeom,1);</p><p class="ydp4b316ddfyiv7486868869MsoNormal">And gotten different answers. So I suspect it’s the subselect throwing it off or it is intentionally treating like a constant because that subselect doesn’t involve the table being updated.</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> </p><p class="ydp4b316ddfyiv7486868869MsoNormal">So the trick I see is to incorporate some value from your events table into your routine.</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> </p><p class="ydp4b316ddfyiv7486868869MsoNormal">Try computing first and then updating like so the below gives me different answers for each row.</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> </p><p class="ydp4b316ddfyiv7486868869MsoNormal">WITH a AS (</p><p class="ydp4b316ddfyiv7486868869MsoNormal">SELECT e.id, ST_Makeline(</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> ST_GeometryN(</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> ST_GeneratePoints(</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> ST_Buffer(</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> ST_POINTN(s.std_track,1),</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> 0.01),</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> 1),</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> 1), ST_GeometryN(</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> ST_GeneratePoints(</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> ST_Buffer(</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> ST_POINTN(s.std_track,2),</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> 0.01),</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> 1),</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> 1)</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> ) AS geom</p><p class="ydp4b316ddfyiv7486868869MsoNormal">FROM events AS e, std_tow AS s</p><p class="ydp4b316ddfyiv7486868869MsoNormal">)</p><p class="ydp4b316ddfyiv7486868869MsoNormal">UPDATE events</p><p class="ydp4b316ddfyiv7486868869MsoNormal">set jittered = a.geom</p><p class="ydp4b316ddfyiv7486868869MsoNormal">FROM a</p><p class="ydp4b316ddfyiv7486868869MsoNormal">WHERE a.id = events.id;</p><p class="ydp4b316ddfyiv7486868869MsoNormal">select ST_AsText(jittered) from events;</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> </p><p class="ydp4b316ddfyiv7486868869MsoNormal">the other way to do it, making the planner realize that just cause the code is exactly the same and doesn’t involve the table being updated, doesn’t mean you want all your values to be the same, is to incorporate your event id in your randomize like so</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> </p><p class="ydp4b316ddfyiv7486868869MsoNormal">update events</p><p class="ydp4b316ddfyiv7486868869MsoNormal">set jittered = ST_Makeline(</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> (select ST_GeometryN(</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> ST_GeneratePoints(</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> ST_Buffer(</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> ST_POINTN(std_track,1),</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> 0.01),</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> 1,</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> (random()*1000)::int + events.id),</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> 1)</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> from std_tow),</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> (select ST_GeometryN(</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> ST_GeneratePoints(</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> ST_Buffer(</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> ST_POINTN(std_track,2),</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> 0.01),</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> 1,</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> (random()*1000)::int + events.id),</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> 1)</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> from std_tow));</p><p class="ydp4b316ddfyiv7486868869MsoNormal">select ST_AsText(jittered) from events;</p><p class="ydp4b316ddfyiv7486868869MsoNormal"> </p><p class="ydp4b316ddfyiv7486868869MsoNormal"> </p><p class="ydp4b316ddfyiv7486868869MsoNormal"> </p><p class="ydp4b316ddfyiv7486868869MsoNormal"> </p><div style="border-width: medium medium medium 1.5pt; border-style: none none none solid; border-color: currentcolor currentcolor currentcolor blue; padding: 0in 0in 0in 4pt;"><div id="ydp4b316ddfyiv7486868869yqt10409" class="ydp4b316ddfyiv7486868869yqt8129383420"><div><div style="border-width: 1pt medium medium; border-style: solid none none; border-color: rgb(225, 225, 225) currentcolor currentcolor; padding: 3pt 0in 0in;"><p class="ydp4b316ddfyiv7486868869MsoNormal"><b>From:</b> Brent Wood <pcreso@yahoo.com> <br clear="none"><b>Sent:</b> Saturday, November 18, 2023 4:35 PM<br clear="none"><b>To:</b> Regina Obe <lr@pcorp.us>; PostGIS Users Discussion <postgis-users@lists.osgeo.org><br clear="none"><b>Subject:</b> Re: [postgis-users] Generating new random points throughout an update</p></div></div><p class="ydp4b316ddfyiv7486868869MsoNormal"> </p><div><div><div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;color:black;">Thanks for your time & advice Regina, I appreciate it.</span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;color:black;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;color:black;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;color:black;">I still can't get this to work as I think it should, so have included actual SQL's to show what I'm doing, using ST_GeneratePoints() this time...</span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;color:black;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;color:black;">I create a db & add the postgis extension....</span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;color:black;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;color:black;">Then create the two tables to test, inserting 3 empty geometries in one & a simple linestring in the other:</span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;color:black;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;color:black;">create table events (id integer,</span></i><span style="font-size:12.0pt;font-family:sans-serif;color:black;"></span></p><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;color:black;"> jittered geometry(LINESTRING,4326));</span></i><span style="font-size:12.0pt;font-family:sans-serif;color:black;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;color:black;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;color:black;">insert into events (id) values (1);</span></i><span style="font-size:12.0pt;font-family:sans-serif;color:black;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;color:black;">insert into events (id) values (2);</span></i><span style="font-size:12.0pt;font-family:sans-serif;color:black;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;color:black;">insert into events (id) values (3);</span></i><span style="font-size:12.0pt;font-family:sans-serif;color:black;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;color:black;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;color:black;">create table std_tow (id integer,</span></i><span style="font-size:12.0pt;font-family:sans-serif;color:black;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;color:black;"> std_track geometry(LINESTRING,4326));</span></i><span style="font-size:12.0pt;font-family:sans-serif;color:black;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;color:black;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;color:black;">insert into std_tow values (1, ST_SetSRID(</span></i><span style="font-size:12.0pt;font-family:sans-serif;color:black;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;color:black;"> ST_MakeLine(</span></i><span style="font-size:12.0pt;font-family:sans-serif;color:black;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;color:black;"> ST_MakePoint(176,-47),</span></i><span style="font-size:12.0pt;font-family:sans-serif;color:black;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;color:black;"> ST_MakePoint(177,-48)</span></i><span style="font-size:12.0pt;font-family:sans-serif;color:black;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;color:black;"> ),</span></i><span style="font-size:12.0pt;font-family:sans-serif;color:black;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;color:black;"> 4326)); </span></i><span style="font-size:12.0pt;font-family:sans-serif;color:black;"></span></p></div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;color:black;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;color:black;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;">I want to update the empty linestrings in one table (events) with slightly randomised versions of the linestring in the other (std_tow).</span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;">ST_GeneratePoints() supposedly generates random points (in a polygon created by buffering the vertices in the standard linestring) without a seed, so I run it with no seed & view the results:</span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;"> </span></p></div><div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;">update events<br clear="none">set jittered = ST_Makeline(<br clear="none"> (select ST_GeometryN(<br clear="none"> ST_GeneratePoints(<br clear="none"> ST_Buffer(<br clear="none"> ST_POINTN(std_track,1),<br clear="none"> 0.01),<br clear="none"> 1),<br clear="none"> 1)<br clear="none"> from std_tow),<br clear="none"> (select ST_GeometryN(<br clear="none"> ST_GeneratePoints(<br clear="none"> ST_Buffer(<br clear="none"> ST_POINTN(std_track,2),<br clear="none"> 0.01),<br clear="none"> 1),<br clear="none"> 1)</span></i><span style="font-size:12.0pt;font-family:sans-serif;"></span></p><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;"> from std_tow));</span></i><span style="font-size:12.0pt;font-family:sans-serif;"></span></p></div></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;">select ST_AsText(jittered) from events;</span></i><span style="font-size:12.0pt;font-family:sans-serif;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;"> </span></p></div></div></div><div><div><p style="margin-bottom:12.0pt;" class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;">LINESTRING(175.99658281229873 -46.99893493622685,177.0081812507064 -47.99873318845546)<br clear="none">LINESTRING(175.99658281229873 -46.99893493622685,177.0081812507064 -47.99873318845546)<br clear="none">LINESTRING(175.99658281229873 -46.99893493622685,177.0081812507064 -47.99873318845546)<br clear="none">(3 rows)</span></i><span style="font-size:12.0pt;font-family:sans-serif;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;">I get three identical linestrings. </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;">I figured I'd use a different integer random seed (between 0 and 1000) in ST_GeneratePoints() to force a different result each time:</span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;"> </span></p></div><div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;">update events<br clear="none">set jittered = ST_Makeline(<br clear="none"> (select ST_GeometryN(<br clear="none"> ST_GeneratePoints(<br clear="none"> ST_Buffer(<br clear="none"> ST_POINTN(std_track,1),<br clear="none"> 0.01),<br clear="none"> 1,<br clear="none"> (random()*1000)::int),<br clear="none"> 1)<br clear="none"> from std_tow),<br clear="none"> (select ST_GeometryN(<br clear="none"> ST_GeneratePoints(<br clear="none"> ST_Buffer(<br clear="none"> ST_POINTN(std_track,2),<br clear="none"> 0.01),<br clear="none"> 1,<br clear="none"> (random()*1000)::int),<br clear="none"> 1)<br clear="none"> from std_tow));<br clear="none">select ST_AsText(jittered) from events;</span></i><span style="font-size:12.0pt;font-family:sans-serif;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;"> </span></p></div></div></div><div><p style="margin-bottom:12.0pt;" class="ydp4b316ddfyiv7486868869MsoNormal"><i><span style="font-size:12.0pt;"> LINESTRING(175.9943248467802 -46.996045972449906,176.9919097521138 -48.00102135929174)<br clear="none"> LINESTRING(175.9943248467802 -46.996045972449906,176.9919097521138 -48.00102135929174)<br clear="none"> LINESTRING(175.9943248467802 -46.996045972449906,176.9919097521138 -48.00102135929174)<br clear="none">(3 rows)</span></i><span style="font-size:12.0pt;font-family:sans-serif;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;">I get different vertices from the first attempt, but all 3 records are still the same values - despite supposedly having a different seed.</span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;">I figure the Postgres query optimiser must be reusing the result from the subqueries rather than recalculating it each time, but am not sure, and the optimiser cannot be turned off.</span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;">What am I doing wrong??? (or how can I do it right!!)</span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;">Much appreciated,</span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;"> Brent</span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;"> </span></p></div></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:12.0pt;font-family:sans-serif;"> </span></p></div></div></div><div id="ydp4b316ddfyiv7486868869ydp8ff1cde5yahoo_quoted_0469449270"><div><div id="ydp4b316ddfyiv7486868869yqt43017" class="ydp4b316ddfyiv7486868869yqt8129383420"><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">On Sunday, November 19, 2023 at 06:44:16 AM GMT+13, Regina Obe <<a shape="rect" href="mailto:lr@pcorp.us" rel="nofollow" target="_blank">lr@pcorp.us</a>> wrote: </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span></p></div><div><p class="ydp4b316ddfyiv7486868869MsoNormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span></p></div></div><div><div id="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018"><div><div><div id="ydp4b316ddfyiv7486868869yqt33427" class="ydp4b316ddfyiv7486868869yqt8129383420"><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">Well when I run random() I do get a different answer for each run so random behaves as I would expect. I didn’t look that closely at your query with random.</span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">e.g.</span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">SELECT random()</span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">FROM generate_series(1,100);</span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">Even if within the same row, the random numbers are different:</span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">SELECT random(), random()</span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">FROM generate_series(1,10);</span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">If you were doing random()::integer as input into ST_GeneratePoints, I thought maybe that was a typo on your end. Then your random number would only be 0 or 1, which is not that random.</span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">So if you really were doing ST_GeneratePoints(geom, random()::integer) then that would explain why you got much less than random results with ST_GeneratePoints.</span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span></p></div><div style="border-width: medium medium medium 1.5pt; border-style: none none none solid; padding: 0in 0in 0in 4pt; border-color: currentcolor currentcolor currentcolor blue;"><div id="ydp4b316ddfyiv7486868869yqt78008" class="ydp4b316ddfyiv7486868869yqt8129383420"><div id="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018yqt36444"><div><div style="border-width: 1pt medium medium; border-style: solid none none; padding: 3pt 0in 0in; border-color: currentcolor;"><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><b><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">From:</span></b><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> Brent Wood <<a shape="rect" href="mailto:pcreso@yahoo.com" rel="nofollow" target="_blank">pcreso@yahoo.com</a>> <br clear="none"><b>Sent:</b> Saturday, November 18, 2023 1:29 AM<br clear="none"><b>To:</b> Regina Obe <<a shape="rect" href="mailto:lr@pcorp.us" rel="nofollow" target="_blank">lr@pcorp.us</a>>; PostGIS Users Discussion <<a shape="rect" href="mailto:postgis-users@lists.osgeo.org" rel="nofollow" target="_blank">postgis-users@lists.osgeo.org</a>><br clear="none"><b>Subject:</b> Re: [postgis-users] Generating new random points throughout an update</span></p></div></div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span></p><div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:12.0pt;font-family:sans-serif;color:#26282A;">Hi Regina,</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:12.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:12.0pt;font-family:sans-serif;color:#26282A;">The seed was an int generated from random(), so I'd expected to generate a different result every time. This didn't happen.</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:12.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:12.0pt;font-family:sans-serif;color:#26282A;">Do I understand that if I omit the seed, I'll get a different point each time by default?</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:12.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:12.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:12.0pt;font-family:sans-serif;color:#26282A;">Thanks,</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:12.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:12.0pt;font-family:sans-serif;color:#26282A;"> Brent </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:12.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div></div></div></div><div id="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyahoo_quoted_0556156328"><div><div id="ydp4b316ddfyiv7486868869yqt04481" class="ydp4b316ddfyiv7486868869yqt8129383420"><div id="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018yqt74257"><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">On Saturday, November 18, 2023 at 06:01:37 PM GMT+13, Regina Obe <<a shape="rect" href="mailto:lr@pcorp.us" rel="nofollow" target="_blank">lr@pcorp.us</a>> wrote: </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div></div></div><div><div id="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105"><div><div><div id="ydp4b316ddfyiv7486868869yqt23770" class="ydp4b316ddfyiv7486868869yqt8129383420"><div id="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018yqt51294"><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">If you want the answer different each time, you don’t want to feed a seed to ST_GeneratePoints. </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">The seed argument was added because some people wanted to generate the same answer for each run.</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"><a shape="rect" href="https://postgis.net/docs/ST_GeneratePoints.html" rel="nofollow" target="_blank">https://postgis.net/docs/ST_GeneratePoints.html</a> (note the sentence: The optional </span><code><span style="font-size:10.0pt;color:#26282A;">seed</span></code><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> is used to regenerate a deterministic sequence of points, and must be greater than zero.)</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div></div><div style="border-width: medium medium medium 1.5pt; border-style: none none none solid; padding: 0in 0in 0in 4pt; border-color: currentcolor currentcolor currentcolor blue;"><div id="ydp4b316ddfyiv7486868869yqt79157" class="ydp4b316ddfyiv7486868869yqt8129383420"><div id="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018yqt37078"><div id="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105yqt47953"><div><div style="border-width: 1pt medium medium; border-style: solid none none; padding: 3pt 0in 0in; border-color: currentcolor;"><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><b><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">From:</span></b><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> postgis-users <<a shape="rect" href="mailto:postgis-users-bounces@lists.osgeo.org" rel="nofollow" target="_blank">postgis-users-bounces@lists.osgeo.org</a>> <b>On Behalf Of </b>Brent Wood via postgis-users<br clear="none"><b>Sent:</b> Friday, November 17, 2023 11:53 PM<br clear="none"><b>To:</b> PostGIS Users Discussion <<a shape="rect" href="mailto:postgis-users@lists.osgeo.org" rel="nofollow" target="_blank">postgis-users@lists.osgeo.org</a>><br clear="none"><b>Cc:</b> Brent Wood <<a shape="rect" href="mailto:pcreso@yahoo.com" rel="nofollow" target="_blank">pcreso@yahoo.com</a>><br clear="none"><b>Subject:</b> [postgis-users] Generating new random points throughout an update</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div></div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">Hopefully someone can help with a problem I'm having.</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">I have a table with simple linestrings that I need to create a randomly modified version of.</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">The linestrings represent vessel tracks. I can identify a set of "similar" tracks & create a single "average" linestring that is somewhat representative.</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">Many of the records don't have a linestring, but for statistical purposes I need to assign a linestring to each - by creating a jittered version of the average linestring so they are not all identical.</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">The simplest approach I have tried is to use an update with ST_Project() given a random() distance & random() direction applied to each vertex in the average line.</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">I use the first two vertices with ST_Makeline(), then append a vertex for the third point, as in the SQL below. </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">My problem is that every new line is identical. From some Googled hints, I figure the optimiser has decided to run random() once & re-use the value instead of running the function for every iteration (but I could be wrong!).</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">Any suggestions as to how I can force a different random result for each record that is updated?</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">I also tried using ST_GeneratePoints() in a buffer around each point, but need to use something like (random()::int as the seed, and this seems to do exactly the same - valid linestrings are generated, but they are identical, so I'm assuming the seed is not being recalculated for each record.</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><i><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">update events<br clear="none">set jittered = ST_MakeLine(<br clear="none"> (select ST_Project(<br clear="none"> ST_POINTN(std_track,1),<br clear="none"> (random()*5000),<br clear="none"> radians(random()*360))::geometry<br clear="none"> from std_tow),<br clear="none"> (select ST_Project(<br clear="none"> ST_PointN(std_track,2),<br clear="none"> (random()*5000),<br clear="none"> radians(random()*360))::geometry<br clear="none"> from std_tow)<br clear="none"> );</span></i><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;">Thanks,</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> Brent Wood</span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div></div></div></div></div><div><p class="ydp4b316ddfyiv7486868869ydp8ff1cde5yiv1316434018ydp281dcc1fyiv3900421105msonormal"><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"> </span><span style="font-size:10.0pt;font-family:sans-serif;color:#26282A;"></span></p></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div>
</div>
</div></body></html>