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

[pcreso at 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.david.smith at gmail.com> wrote:

From: James David Smith <james.david.smith at gmail.com>
Subject: [postgis-users] Creating points along a line for every second?
To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
Date: Saturday, August 18, 2012, 7:17 AM

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,


 linestart_timeend_timeseries (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         ||        2line_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         ||        5line_geom         ||   12:07:21                ||   12:07:26         ||        4line_geom         ||   12:07:21                ||   12:07:26         ||        3


line_geom                || 12:07:21                ||  12:07:26         ||        2line_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

-----Inline Attachment Follows-----

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120817/a6df45e3/attachment.html>