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

[David William Bitner]

Here is a function I use with track data that has measure values in seconds:

CREATE OR REPLACE FUNCTION everysecond(g geometry)
>   RETURNS geometry AS
> $BODY$
> select st_makeline(st_locate_along_measure($1,g)) from
> generate_series(0,floor(st_m(st_endpoint($1)))::int,1) g;
> $BODY$
>   LANGUAGE sql VOLATILE
>   COST 100;


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;


On Fri, Aug 17, 2012 at 2:48 PM, 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
>
>


-- 
************************************
David William Bitner
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120817/c56a7e10/attachment.html>