[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

[pcr...@pcreso.com]

Hi James, I'd do this fairly easily under Linux with a script to iterate through the tasks. In fact I've done it, we have tracklines recorded for an underwater cammera. It takes a picture every 15 seconds, so I navigate along the line in 15 second intervals to get the points. As you describe, it is a matter of defining the line by temporal extents & using % of time to navigate the distance along the line. You already have the algorithm, but I'd suggest implementing it using a suitable scripting language rather than doing it entirely in SQL. Which scripting language is up to you, but as pseudocode, something along the lines of create a table to store the points: psql -d <db> -c "create table <points> ( line id, percent, time, point )" iterate through the lines: for line_id in (list of line_ids) ; do   get distance of line in seconds (psql)   measure = 0   while measure >= distance ; do     measure = measure + 1     percent = measure / distance     time = psql -d <db> -c "select (start time plus number of seconds) as interval "     psql -d <db> -c "insert into <points> values                      line id,                      percent,                      time,                      line_interpolate_point(line, percent/100)"   done done HTH,   Brent --- On Sat, 8/18/12, James David Smith <james.da...@gmail.com> wrote:

-----Inline Attachment Follows----- _______________________________________________ postgis-users mailing list postgi...@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users

[Robert Burgholzer]

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.

--
--
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

[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;

_______________________________________________
postgis-users mailing list
postgi...@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

--
************************************
David William Bitner

[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

[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

[Robert Burgholzer]

Very nice when it actually is that simple :).

rb

>> || > _______________________________________________
> postgis-users mailing list
> postgi...@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
_______________________________________________
postgis-users mailing list
postgi...@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users