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 > <rburg...@vt.edu<javascript:_e({}, 'cvml', 'rburg...@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@postgis.refractions.net <javascript:_e({}, 'cvml', >> 'postgis-users@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
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users