[postgis-users] Adding a sub-series or sub-index to output

[Nicolas Ribot]

Hi,

You could use st_dumppoints directly to dump polygons points, then by
joining on itself, generate the segments, using the dump.path column to
keep a segment id for each polygon.
(to adapt in case of multipg):

with tmp as (
  select 1::int as id, 'POLYGON((0 0, 1 0, 1 1, 0 0))'::geometry(polygon)
as geom
), tmp1 as (
    SELECT id, st_dumppoints(geom) as d
    FROM tmp
), tmp2 as (
  select t1.id, (t1.d).path[2] as segid, (t1.d).path,
    st_makeline((t1.d).geom, (t2.d).geom) as seg
  from tmp1 t1 join tmp1 t2 on t1.id = t2.id and (t2.d).path[2] =
(t1.d).path[2] + 1
) SELECT *
  FROM tmp2;

Nicolas

On 21 April 2017 at 15:59, Basques, Bob (CI-StPaul) <
bob.basques at ci.stpaul.mn.us> wrote:

> All,
>
> This is probably a generic postgres question, but . .
>
> I’m trying to generate a line segment data table from a (parcel) polygon
> dataset.  I need to bind the polygon (ParcelId) to each segment as well as
> maintain the sequence of the line strings that make up the source polygon,
> so I need to add in a index series on the fly to the output of each
> segment.  I have the segment output working, based on pulling bits from the
> internet together, although I think I may be going at this backwards, maybe
> I should be select and adding index on a parcel by parcel basis instead of
> selecting them all at once at the bottom . . . I’ve tried various
> permutations in the first sub select of row_number() over, and
> generate_series, but I’m missing something . . . I’m sure there is more
> than one way to do this too.
>
> select
> —- seq,
> parcelid,
> ST_AsText(ST_MakeLine(sp,ep) )
> from (
> select
> parcelid,
> ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp,
> ST_PointN(geom, generate_series(2, ST_NPoints(geom)  )) as ep
> from (
> select
> parcelid,
> ST_AsText(ST_Boundary( (ST_Dump(wkb_geometry)).geom )) as geom
> from
> cache.rc_parcel_poly
> where
> objectid IN (46813, 46814, 46815)
> ) as linestrings
> ) AS segments
>
> This is the output:
>
>
> parceled, st_astext
> “252923130050"; "LINESTRING(569983.06682238 166280.506289959,569982.969482869
> 166321.960092038)"
> “252923130050"; "LINESTRING(569982.969482869 166321.960092038,570115.252698928
> 166324.171799228)"
> “252923130050"; "LINESTRING(570115.252698928 166324.171799228,570115.328402415
> 166282.171405315)"
> “252923130050"; "LINESTRING(570115.328402415 166282.171405315,569991.947482795
> 166280.591207951)"
> “252923130050"; "LINESTRING(569991.947482795 166280.591207951,569983.06682238
> 166280.506289959)"
> “252923130193"; "LINESTRING(569322.752598196 166436.412697807,569190.786695376
> 166434.788415357)"
> “252923130193"; "LINESTRING(569190.786695376 166434.788415357,569190.670520335
> 166477.753622204)"
> “252923130193"; "LINESTRING(569190.670520335 166477.753622204,569322.618897527
> 166479.994418308)"
> “252923130193"; "LINESTRING(569322.618897527 166479.994418308,569322.752598196
> 166436.412697807)"
> “252923130047"; "LINESTRING(570115.1013823 166408.172812894,569982.770015925
> 166406.868192479)"
> “252923130047"; "LINESTRING(569982.770015925 166406.868192479,569982.672586083
> 166448.324117512)"
> “252923130047"; "LINESTRING(569982.672586083 166448.324117512,570115.025814325
> 166450.173206791)"
> “252923130047"; "LINESTRING(570115.025814325 166450.173206791,570115.1013823
> 166408.172812894)"
>
> I’m trying to get to this output:
>
> seq, parceled, st_astext
> 1 “252923130050”; "LINESTRING(569983.06682238 166280.506289959,569982.969482869
> 166321.960092038)"
> 2 “252923130050"; "LINESTRING(569982.969482869 166321.960092038,570115.252698928
> 166324.171799228)"
> 3 “252923130050”; "LINESTRING(570115.252698928 166324.171799228,570115.328402415
> 166282.171405315)"
> 4 “252923130050”; "LINESTRING(570115.328402415 166282.171405315,569991.947482795
> 166280.591207951)"
> 5 “252923130050”; “LINESTRING(569991.947482795 166280.591207951,569983.06682238
> 166280.506289959)"
>
> 1 “252923130193”; "LINESTRING(569322.752598196 166436.412697807,569190.786695376
> 166434.788415357)"
> 2 “252923130193”; "LINESTRING(569190.786695376 166434.788415357,569190.670520335
> 166477.753622204)"
> 3 “252923130193”; "LINESTRING(569190.670520335 166477.753622204,569322.618897527
> 166479.994418308)"
> 4 “252923130193”; “LINESTRING(569322.618897527 166479.994418308,569322.752598196
> 166436.412697807)"
>
> 1 “252923130047”; "LINESTRING(570115.1013823 166408.172812894,569982.770015925
> 166406.868192479)"
> 2 “252923130047”; "LINESTRING(569982.770015925 166406.868192479,569982.672586083
> 166448.324117512)"
> 3 “252923130047”; "LINESTRING(569982.672586083 166448.324117512,570115.025814325
> 166450.173206791)"
> 4 “252923130047”; “LINESTRING(570115.025814325 166450.173206791,570115.1013823
> 166408.172812894)"
>
> Any help, pointers, appreciated  . . .
>
> bobb
>
>
>
>
> An inventor is simply a fellow who doesn’t take his education too
> seriously.
> —Charles F. Kettering
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170421/61c4a001/attachment.html>