<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class="">
All,
<div class=""><br class="">
</div>
<div class="">This is probably a generic postgres question, but . .
<div class=""><br class="">
</div>
<div class="">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
<font face="Courier" class="">row_number() over</font>, and <font face="Courier" class="">
generate_series</font>, but I’m missing something . . . I’m sure there is more than one way to do this too.</div>
<div class=""><br class="">
</div>
<div class="">
<div class=""><font face="Courier" class="">select </font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>—- seq,</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>parcelid, </font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>ST_AsText(ST_MakeLine(sp,ep) )</font></div>
<div class=""><font face="Courier" class="">from (</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>select</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>parcelid,</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp,</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>ST_PointN(geom, generate_series(2, ST_NPoints(geom) )) as ep</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>from (</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>select </font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>parcelid,</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>ST_AsText(ST_Boundary( (ST_Dump(wkb_geometry)).geom )) as geom</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>from </font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>cache.rc_parcel_poly </font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>where </font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>objectid IN (46813, 46814, 46815)</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>) as linestrings</font></div>
<div class=""><font face="Courier" class="">) AS segments</font></div>
<div class=""><br class="">
</div>
<div class="">This is the output:</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div class=""><font face="Courier" class="">parceled,<span class="Apple-tab-span" style="white-space:pre">
</span>st_astext</font></div>
<div class="">
<div class=""><font face="Courier" class="">“252923130050";<span class="Apple-tab-span" style="white-space:pre">
</span>"LINESTRING(569983.06682238 166280.506289959,569982.969482869 166321.960092038)"</font></div>
<div class=""><font face="Courier" class="">“252923130050";<span class="Apple-tab-span" style="white-space:pre">
</span>"LINESTRING(569982.969482869 166321.960092038,570115.252698928 166324.171799228)"</font></div>
<div class=""><font face="Courier" class="">“252923130050";<span class="Apple-tab-span" style="white-space:pre">
</span>"LINESTRING(570115.252698928 166324.171799228,570115.328402415 166282.171405315)"</font></div>
<div class=""><font face="Courier" class="">“252923130050";<span class="Apple-tab-span" style="white-space:pre">
</span>"LINESTRING(570115.328402415 166282.171405315,569991.947482795 166280.591207951)"</font></div>
<div class=""><font face="Courier" class="">“252923130050";<span class="Apple-tab-span" style="white-space:pre">
</span>"LINESTRING(569991.947482795 166280.591207951,569983.06682238 166280.506289959)"</font></div>
<div class=""><font face="Courier" class="">“252923130193";<span class="Apple-tab-span" style="white-space:pre">
</span>"LINESTRING(569322.752598196 166436.412697807,569190.786695376 166434.788415357)"</font></div>
<div class=""><font face="Courier" class="">“252923130193";<span class="Apple-tab-span" style="white-space:pre">
</span>"LINESTRING(569190.786695376 166434.788415357,569190.670520335 166477.753622204)"</font></div>
<div class=""><font face="Courier" class="">“252923130193";<span class="Apple-tab-span" style="white-space:pre">
</span>"LINESTRING(569190.670520335 166477.753622204,569322.618897527 166479.994418308)"</font></div>
<div class=""><font face="Courier" class="">“252923130193";<span class="Apple-tab-span" style="white-space:pre">
</span>"LINESTRING(569322.618897527 166479.994418308,569322.752598196 166436.412697807)"</font></div>
<div class=""><font face="Courier" class="">“252923130047";<span class="Apple-tab-span" style="white-space:pre">
</span>"LINESTRING(570115.1013823 166408.172812894,569982.770015925 166406.868192479)"</font></div>
<div class=""><font face="Courier" class="">“252923130047";<span class="Apple-tab-span" style="white-space:pre">
</span>"LINESTRING(569982.770015925 166406.868192479,569982.672586083 166448.324117512)"</font></div>
<div class=""><font face="Courier" class="">“252923130047";<span class="Apple-tab-span" style="white-space:pre">
</span>"LINESTRING(569982.672586083 166448.324117512,570115.025814325 166450.173206791)"</font></div>
<div class=""><font face="Courier" class="">“252923130047";<span class="Apple-tab-span" style="white-space:pre">
</span>"LINESTRING(570115.025814325 166450.173206791,570115.1013823 166408.172812894)"</font></div>
</div>
<div class=""><br class="">
</div>
<div class="">
<div style="color: rgb(0, 0, 0); font-family: 'Comic Sans MS'; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class="">
I’m trying to get to this output:</div>
<div style="color: rgb(0, 0, 0); font-family: 'Comic Sans MS'; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class="">
<br class="">
</div>
<div style="font-variant-ligatures: normal; font-variant-east-asian: normal; font-variant-position: normal; text-align: start; text-indent: 0px;" class="">
<div style="font-variant-ligatures: normal; font-variant-east-asian: normal; font-variant-position: normal;" class="">
<div class=""><font face="Courier" class="">seq,<span class="Apple-tab-span" style="white-space:pre">
</span>parceled,<span class="Apple-tab-span" style="white-space: pre;"> </span>st_astext</font></div>
<div class="">
<div class=""><font face="Courier" class="">1<span class="Apple-tab-span" style="white-space:pre">
</span>“252923130050”;<span class="Apple-tab-span" style="white-space: pre;"> </span>
"LINESTRING(569983.06682238 166280.506289959,569982.969482869 166321.960092038)"</font></div>
<div class=""><font face="Courier" class="">2<span class="Apple-tab-span" style="white-space:pre">
</span>“252923130050";<span class="Apple-tab-span" style="white-space: pre;"> </span>
"LINESTRING(569982.969482869 166321.960092038,570115.252698928 166324.171799228)"</font></div>
<div class=""><font face="Courier" class="">3<span class="Apple-tab-span" style="white-space:pre">
</span>“252923130050”;<span class="Apple-tab-span" style="white-space: pre;"> </span>
"LINESTRING(570115.252698928 166324.171799228,570115.328402415 166282.171405315)"</font></div>
<div class=""><font face="Courier" class="">4<span class="Apple-tab-span" style="white-space:pre">
</span>“252923130050”;<span class="Apple-tab-span" style="white-space: pre;"> </span>
"LINESTRING(570115.328402415 166282.171405315,569991.947482795 166280.591207951)"</font></div>
<div class=""><font face="Courier" class="">5<span class="Apple-tab-span" style="white-space:pre">
</span>“252923130050”;<span class="Apple-tab-span" style="white-space: pre;"> </span>
“LINESTRING(569991.947482795 166280.591207951,569983.06682238 166280.506289959)"</font></div>
<div class=""><font face="Courier" class=""><br class="">
</font></div>
<div class=""><font face="Courier" class="">1<span class="Apple-tab-span" style="white-space:pre">
</span>“252923130193”;<span class="Apple-tab-span" style="white-space: pre;"> </span>
"LINESTRING(569322.752598196 166436.412697807,569190.786695376 166434.788415357)"</font></div>
<div class=""><font face="Courier" class="">2<span class="Apple-tab-span" style="white-space:pre">
</span>“252923130193”;<span class="Apple-tab-span" style="white-space: pre;"> </span>
"LINESTRING(569190.786695376 166434.788415357,569190.670520335 166477.753622204)"</font></div>
<div class=""><font face="Courier" class="">3<span class="Apple-tab-span" style="white-space:pre">
</span>“252923130193”;<span class="Apple-tab-span" style="white-space: pre;"> </span>
"LINESTRING(569190.670520335 166477.753622204,569322.618897527 166479.994418308)"</font></div>
<div class=""><font face="Courier" class="">4<span class="Apple-tab-span" style="white-space:pre">
</span>“252923130193”;<span class="Apple-tab-span" style="white-space: pre;"> </span>
“LINESTRING(569322.618897527 166479.994418308,569322.752598196 166436.412697807)"</font></div>
<div class=""><font face="Courier" class=""><br class="">
</font></div>
<div class=""><font face="Courier" class="">1<span class="Apple-tab-span" style="white-space:pre">
</span>“252923130047”;<span class="Apple-tab-span" style="white-space: pre;"> </span>
"LINESTRING(570115.1013823 166408.172812894,569982.770015925 166406.868192479)"</font></div>
<div class=""><font face="Courier" class="">2<span class="Apple-tab-span" style="white-space:pre">
</span>“252923130047”;<span class="Apple-tab-span" style="white-space: pre;"> </span>
"LINESTRING(569982.770015925 166406.868192479,569982.672586083 166448.324117512)"</font></div>
<div class=""><font face="Courier" class="">3<span class="Apple-tab-span" style="white-space:pre">
</span>“252923130047”;<span class="Apple-tab-span" style="white-space: pre;"> </span>
"LINESTRING(569982.672586083 166448.324117512,570115.025814325 166450.173206791)"</font></div>
<div class=""><font face="Courier" class="">4<span class="Apple-tab-span" style="white-space:pre">
</span>“252923130047”;<span class="Apple-tab-span" style="white-space: pre;"> </span>
“LINESTRING(570115.025814325 166450.173206791,570115.1013823 166408.172812894)"</font></div>
</div>
<div class=""><font face="Courier" class=""><br class="">
</font></div>
<div class="">Any help, pointers, appreciated . . .</div>
<div class=""><br class="">
</div>
<div class="">bobb</div>
<div class=""><font face="Courier" class=""><br class="">
</font></div>
<div class=""><font face="Courier" class=""><br class="">
</font></div>
</div>
<div style="color: rgb(0, 0, 0); font-family: 'Comic Sans MS'; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class="">
<br class="">
</div>
<br class="">
</div>
<div style="color: rgb(0, 0, 0); font-family: 'Comic Sans MS'; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class="">
<div class="wp_quotepage_quote">An inventor is simply a fellow who doesn’t take his education too seriously.</div>
<div class="wp_quotepage_author">—Charles F. Kettering</div>
</div>
</div>
<br class="">
</div>
</div>
</body>
</html>