<html><head></head><body><div class="ydpe5da8134yahoo-style-wrap" style="font-family:verdana, helvetica, sans-serif;font-size:16px;"><div></div></div><div dir="ltr" data-setdir="false">Hopefully someone can help with a problem I'm having.</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">I have a table with simple linestrings that I need to create a randomly modified version of.</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">The linestrings represent vessel tracks. I can identify a set of "similar" tracks & create a single "average" linestring that is somewhat representative.</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">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.</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">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.</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">I use the first two vertices with ST_Makeline(), then append a vertex for the third point, as in the SQL below. <br></div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">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!).</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">Any suggestions as to how I can force a different random result for each record that is updated?</div><div dir="ltr" data-setdir="false">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.<br></div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false"><div><i>update events<br>set jittered = ST_MakeLine(<br> (select ST_Project(<br> ST_POINTN(std_track,1),<br> (random()*5000),<br> radians(random()*360))::geometry<br> from std_tow),<br> (select ST_Project(<br> ST_PointN(std_track,2),<br> (random()*5000),<br> radians(random()*360))::geometry<br> from std_tow)<br> );</i></div><div><br></div><div><br></div><div dir="ltr" data-setdir="false">Thanks,</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false"> Brent Wood<br></div></div><div dir="ltr" data-setdir="false"><br></div></body></html>