Hi Robert, Thanks for your suggestion - this worked great - and was easier than learning how to do loops and similar! (though thanks for the suggestions from others too!). I knew just a bit of lateral thinking was required!
James On 17 August 2012 20:48, Robert Burgholzer <rburg...@vt.edu> wrote: > 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 > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
