[postgis-users] interpolate and extract points from line

[Nicolas Ribot]

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 <nicolas.ribot at gmail.com> 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 . <gamejihou at hotmail.com> 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
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users