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

[James David Smith]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120817/3e6d9033/attachment.html>