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

[Robert Burgholzer]

And to do this without an additional internal function (or external
scripting language) you can use the postgres function "generate_series" in
an SQL statement, I have some examples with a precipitation dataset using
dates, but you could certainly adapt to your case with points:

http://sifn.bse.vt.edu/sifnwiki/index.php/Woomm_noaa

Regards,
rb

On Friday, August 17, 2012, David William Bitner wrote:

> 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<javascript:_e({}, 'cvml', '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 <javascript:_e({}, 'cvml',
>> 'postgis-users at postgis.refractions.net');>
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
>
> --
> ************************************
> David William Bitner
>


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