[postgis-users] interpolate and extract points from line

[Gery .]

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,