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.sm...@gmail.com> wrote:
From: James David Smith <james.david.sm...@gmail.com>
Subject: [postgis-users] Creating points along a line for every second?
To: "PostGIS Users Discussion" <postgis-users@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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
