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 st_line_interpolate_point
HTH r.b. On Friday, August 17, 2012, James David Smith wrote: > Hi everyone, > > 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, > > line > start_time > end_time > series (I generated this as a count of the seconds difference between the > above time fields. I thought it would be useful) > > So for example my table (temp4) might look like this: > > line || start_time || end_time > || series > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > line_geom || 12:07:17 || 12:07:20 > || 3 > line_geom || 12:07:17 || 12:07:20 || > 2 > line_geom || 12:07:17 || 12:07:20 > || 1 > > line_geom || 12:07:21 || 12:07:26 > || 6 > line_geom || 12:07:21 || 12:07:26 || > 5 > line_geom || 12:07:21 || 12:07:26 || > 4 > line_geom || 12:07:21 || 12:07:26 || > 3 > line_geom || 12:07:21 || 12:07:26 || > 2 > line_geom || 12:07:21 || 12:07:26 || > 1 > > > 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. > > So I've been trying something like this, but it's wrong. But maybe along > the right lines. > > SELECT st_line_interpolate_point(line, 1/series::float)) as new_point > FROM temp4 > > 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. > > line || start_time || end_time || > series || percentage along line > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > line_geom || 12:07:17 || 12:07:20 || > 3 || 0.33% > line_geom || 12:07:17 || 12:07:20 || > 2 || 0.67 > line_geom || 12:07:17 || 12:07:20 || 1 > || 100% > > Sorry for the long explanation. Hope that you understand where I am coming > from. Grateful for any ideas please! > > Thanks > > James > -- -- Robert W. Burgholzer http://www.findingfreestyle.com/ On Facebook - http://www.facebook.com/pages/Finding-Freestyle/151918511505970 Twitter - http://www.twitter.com/findfreestyle What's a tweeted swim set? A Sweet? No, a #swaiku! Get them by following http://twitter.com/findfreestyle
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
