[postgis-users] Creating points along a line for every second?

[James David Smith]

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 <rburghol at 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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>