Here is a function I use with track data that has measure values in seconds:<div><br><blockquote style="margin:0 0 0 40px;border:none;padding:0px"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
CREATE OR REPLACE FUNCTION everysecond(g geometry)<br> RETURNS geometry AS<br>$BODY$<br>select st_makeline(st_locate_along_measure($1,g)) from generate_series(0,floor(st_m(st_endpoint($1)))::int,1) g;<br>$BODY$<br> LANGUAGE sql VOLATILE<br>
COST 100;</blockquote><div><br></div></blockquote>You could certainly either add measures to your lines -- 'update table set line=st_addmeasure(line,0,series)' or do something similar to the above just using st_locatebetween;<br>
<div><br><br><div class="gmail_quote">On Fri, Aug 17, 2012 at 2:48 PM, Robert Burgholzer <span dir="ltr"><<a href="mailto:rburghol@vt.edu" target="_blank">rburghol@vt.edu</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
If i understand this right, the series field tells you which second in total time of N seconds that it took to move along the line. (end_time - start_time)::float should give you the total time in seconds, and series/total_time should give you the parameter that you want for <span>st_line_interpolate_point</span><div>
<span><br></span></div><div><span>HTH<br></span></div><div><span>r.b.<span></span><br></span><div><div class="h5">On Friday, August 17, 2012, James David Smith wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><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>
</blockquote></div></div></div><span class="HOEnZb"><font color="#888888"><br><br>-- <br>--<br>Robert W. Burgholzer<br><a href="http://www.findingfreestyle.com/" target="_blank">http://www.findingfreestyle.com/</a><br>On Facebook - <a href="http://www.facebook.com/pages/Finding-Freestyle/151918511505970" target="_blank">http://www.facebook.com/pages/Finding-Freestyle/151918511505970</a><br>
Twitter - <a href="http://www.twitter.com/findfreestyle" target="_blank">http://www.twitter.com/findfreestyle</a><br>What's a tweeted swim set? A Sweet? No, a #swaiku! Get them by following <a href="http://twitter.com/findfreestyle" target="_blank">http://twitter.com/findfreestyle</a><br>
</font></span><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto: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>
<br></blockquote></div><br><br clear="all"><div><br></div>-- <br>************************************<br>David William Bitner<br>
</div></div>