[postgis-users] interpolate and extract points from line

[Nicolas Ribot]

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