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 [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
