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

[Basques, Bob (CI-StPaul)]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170421/76081211/attachment.html>