<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;">Hi James,<br><br>I'd do this fairly easily under Linux with a script to iterate through the tasks. In fact I've done it, we have tracklines recorded for an underwater cammera. It takes a picture every 15 seconds, so I navigate along the line in 15 second intervals to get the points.<br><br>As you describe, it is a matter of defining the line by temporal extents & using % of time to navigate the distance along the line. You already have the algorithm, but I'd suggest implementing it using a suitable scripting language rather than doing it entirely in SQL.<br><br>Which scripting language is up to you, but as pseudocode, something along the lines of<br><br>create a table to store the points:<br>psql -d <db> -c "create table <points> ( line id, percent, time, point )"<br><br>iterate through the lines:<br><font
face="courier,monaco,monospace,sans-serif">for line_id in (list of line_ids) ; do<br> get distance of line in seconds (psql)<br> measure = 0<br> while measure >= distance ; do<br> measure = measure + 1<br> percent = measure / distance<br> time = psql -d <db> -c "select (start time plus number of seconds) as interval "<br> psql -d <db> -c "insert into <points> values<br> line id, <br> percent, <br> time, <br> line_interpolate_point(line,
percent/100)"<br> done <br>done<br><br><br><br><font face="arial,helvetica,sans-serif">HTH,<br><br> Brent</font><br><br></font>--- On <b>Sat, 8/18/12, James David Smith <i><james.david.smith@gmail.com></i></b> wrote:<br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br>From: James David Smith <james.david.smith@gmail.com><br>Subject: [postgis-users] Creating points along a line for every second?<br>To: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net><br>Date: Saturday, August 18, 2012, 7:17 AM<br><br><div id="yiv1840382515"><div>Hi everyone,</div><div> </div><div>I'm flirted around this problem for a while, so I hope I'm not bein annoying by being repetative, but I've been tearing my hair out trying to do it. The problem is that I have a file of lines (geometry). in a table called temp4 The format of the table is, simplified,</div>
<div> </div><div>line</div><div>start_time</div><div>end_time</div><div>series (I generated this as a count of the seconds difference between the above time fields. I thought it would be useful)</div><div> </div><div>So for example my table (temp4) might look like this:</div>
<div> </div><div>line || start_time || end_time || series</div><div>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~</div><div>line_geom || 12:07:17 || 12:07:20 || 3</div>
<div>line_geom || 12:07:17 || 12:07:20 || 2</div><div>line_geom || 12:07:17 || 12:07:20 || 1</div><div> </div><div>line_geom || 12:07:21 || 12:07:26 || 6</div>
<div><div>line_geom || 12:07:21 || 12:07:26 || 5</div><div>line_geom || 12:07:21 || 12:07:26 || 4</div><div>line_geom || 12:07:21 || 12:07:26 || 3</div>
<div><div>line_geom || 12:07:21 || 12:07:26 || 2</div><div>line_geom || 12:07:21 || 12:07:26 || 1</div><div> </div></div></div>
<div> </div><div>Now what I want to do is split the lines into points, with a point for each second. Evenly spaced along the line. So if the line is 10 metres long and the difference between the start and end is 10 seconds then I want to create 9 points at 10% along the line, 20% along the line, 30% along the line etc up to a point which is 100% along the line i.e. the end of the line. I don't need a new point at the start of the line as I have that stored already.</div>
<div> </div><div>So I've been trying something like this, but it's wrong. But maybe along the right lines.</div><div> </div><div>SELECT st_line_interpolate_point(line, 1/series::float)) as new_point<br>FROM temp4</div>
<div> </div><div>Taking the first few rows of my data, the percentages along the lines I want to generate are as follows. I need to find a way to get these values into the st_line_interpolate_line function.</div><div> </div>
<div><div>line || start_time || end_time || series || percentage along line</div><div>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~</div>
<div>line_geom || 12:07:17 || 12:07:20 || 3 || 0.33%</div><div>line_geom || 12:07:17 || 12:07:20 || 2 || 0.67 </div>
<div>line_geom || 12:07:17 || 12:07:20 || 1 || 100% </div></div><div> </div><div>Sorry for the long explanation. Hope that you understand where I am coming from. Grateful for any ideas please!</div>
<div> </div><div>Thanks</div><div> </div><div>James</div>
</div><br>-----Inline Attachment Follows-----<br><br><div class="plainMail">_______________________________________________<br>postgis-users mailing list<br><a ymailto="mailto:postgis-users@postgis.refractions.net" href="/mc/compose?to=postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></blockquote></td></tr></table>