And even better: starting generate_series at 0 will give you the 245 points you're expecting:
select st_line_interpolate_point( geom, generate_series(0, seg_fldr-1) * (1/(seg_fldr::float - 1)) ) as geom from smoothnav_line_wgs84; ;) On 8 April 2012 13:19, Nicolas Ribot <[email protected]> wrote: > Hi, > > Is st_segmentize() not enough to add points to your lines ? > And are you sure you want to compute distance in decimal degrees ? > there is the st_distanceSphere() function to compute distances on lat > long data. > > Concerning the line interpolation, I think you are misusing the > generate_series and st_line_interpolate_point functions: > > st_line_interpolate_point takes a fraction of total length, between 0 > and 1 (1 is the total length of your line). > So you want to generate "seg_fldr" iterations, each representing 1 / > (seg_fldr-1) fraction of the line: > > select st_line_interpolate_point( > geom, generate_series(1, seg_fldr-1) * (1/(seg_fldr::float - 1)) > ) as geom > from smoothnav_line_wgs84; > > It will generate seg_fldr-1 points spanning onto the line. > > Nicolas > > > On 8 April 2012 09:34, Gery . <[email protected]> wrote: >> >> >> Hi, >> >> This seems to be quite simple but I'm having a hard time trying to get what >> I want. The thing is that I have several lines and I'm trying to interpolate >> them and then extract the points to a CSV file for example. I'm still stuck >> in the interpolation step. This is what I have so far: >> >> whole table: >> mydb=# select id,profile,dist_dd,seg_fldr from smoothnav_line_wgs84 order by >> id; >> id | profile | dist_dd | seg_fldr >> ----+--------------------+--------------+---------- >> 1 | 5148P-LF | 0.0010699943 | 245 >> 2 | 5745P-LF | 0.0015981060 | 282 >> 3 | 5920P-LF | 0.0012989445 | 295 >> 4 | 0053P-LF | 0.0013805557 | 304 >> 5 | 0233P-LF | 0.0012318259 | 278 >> 6 | 0416P-LF | 0.0012958645 | 287 >> 7 | 0601P-LF | 0.0011592394 | 227 >> 8 | 0729P-LF | 0.0013005116 | 328 >> 9 | 0946P-LF | 0.0012521930 | 239 >> 10 | 1119P-LF | 0.0013112367 | 300 >> 11 | 1258P-LF | 0.0013001241 | 254 >> 12 | 1434P-LF | 0.0012408036 | 269 >> 13 | 1622P-LF | 0.0010311844 | 225 >> 14 | 1800P-LF | 0.0009614843 | 229 >> 15 | 1927P-LF | 0.0011774532 | 247 >> 16 | 2103P-LF | 0.0011027050 | 243 >> 17 | 2236P-LF | 0.0011613916 | 231 >> 18 | 2410P-LF | 0.0010903102 | 254 >> 19 | 2546P-LF | 0.0011652277 | 227 >> 20 | 2722P-LF | 0.0010217751 | 246 >> 21 | 2903P-LF | 0.0010245117 | 225 >> 22 | 3056P-LF | 0.0011750912 | 283 >> 23 | 3240P-LF | 0.0010244741 | 209 >> 24 | 3403P-LF | 0.0012303394 | 302 >> 25 | 3550P-LF | 0.0011860473 | 246 >> 26 | 3733P-LF | 0.0013065220 | 299 >> 27 | 3922P-LF | 0.0011613916 | 232 >> 28 | 4213P-LF | 0.0027780243 | 588 >> 29 | 4517P-LF | 0.0027969726 | 629 >> 30 | 4831P-LF | 0.0028498347 | 571 >> 31 | 5146P-LF | 0.0029280876 | 633 >> 32 | 5459P-LF | 0.0028549158 | 551 >> 33 | 5823P-LF | 0.0028575039 | 597 >> 34 | 0124P-LF | 0.0029464684 | 616 >> 35 | 0436P-LF | 0.0029445455 | 609 >> 36 | 0808P-LF | 0.0029842086 | 592 >> (36 rows) >> >> one row for tests: >> mydb=# create table test1 as select * from smoothnav_line_wgs84 where id=1; >> >> now, to generate points along the line in test1 I need the distance between >> points, so this is the dist_dd/(seg_fldr-1). In the id=1 above would be: >> 0.0010699943/(245-1) = 0.000004385222540983606557, so, I can get this like: >> >> mydb=# select dist_dd/(seg_fldr-1) from test1 ; >> ?column? >> ---------------------------- >> 0.000004385222540983606557 >> (1 row) >> >> so far so good, here it comes the problem, I need to get 245 points from my >> line, this is starting from the actual origin and putting a point every >> 0.000004385222540983606557 decimal degrees, so at the end I should come up >> with 245 points instead of 49 points (because the current geom is actually >> composed of only 49 points), so I really think my command line doesn't >> reflect what I want, but it's what I'm trying to figure it out: >> >> mydb=# select st_line_interpolate_point(geom,(dist_dd/(seg_fldr-1))), >> generate_series(1,max((seg_fldr)::int)) from test1; >> >> and I'm getting this error: ERROR: column "test1.geom" must appear in the >> GROUP BY clause or be used in an aggregate function >> >> I keep reading but perhaps this is too advanced to my sql understanding. Any >> help will be really appreciated. >> >> Thanks, >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
