<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="">
Well that was easy!! Thanks Nicolas . . . . (I had to read up on that PATH stuff though . . . :c)
<div class=""><br class="">
</div>
<div class="">bobb</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
<div class=""><br class="">
</div>
<div class="">
<div class=""><font face="Courier" class="">with tmp as (</font></div>
<div class=""><font face="Courier" class=""> -- select 1::int as id, 'POLYGON((0 0, 1 0, 1 1, 0 0))'::geometry(polygon) as geom</font></div>
<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>parcelid as id,</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>( (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="">), tmp1 as (</font></div>
<div class=""><font face="Courier" class=""> SELECT id, st_dumppoints(geom) as d</font></div>
<div class=""><font face="Courier" class=""> FROM tmp</font></div>
<div class=""><font face="Courier" class="">), tmp2 as (</font></div>
<div class=""><font face="Courier" class=""> select t1.id, (t1.d).path[2] as segid, (t1.d).path —-,</font></div>
<div class=""><font face="Courier" class=""> —- st_makeline((t1.d).geom, (t2.d).geom) as seg</font></div>
<div class=""><font face="Courier" class=""> from tmp1 t1 join tmp1 t2 on t1.id = t2.id and (t2.d).path[2] = (t1.d).path[2] + 1</font></div>
<div class=""><font face="Courier" class="">) SELECT *</font></div>
<div class=""><font face="Courier" class=""> FROM tmp2;</font></div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div class="">output:</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div class="">
<div class=""><font face="Courier" class="">"252923130047";1;"{1,1}"</font></div>
<div class=""><font face="Courier" class="">"252923130047";2;"{1,2}"</font></div>
<div class=""><font face="Courier" class="">"252923130047";3;"{1,3}"</font></div>
<div class=""><font face="Courier" class="">"252923130047";4;"{1,4}"</font></div>
<div class=""><font face="Courier" class="">"252923130050";1;"{1,1}"</font></div>
<div class=""><font face="Courier" class="">"252923130050";2;"{1,2}"</font></div>
<div class=""><font face="Courier" class="">"252923130050";3;"{1,3}"</font></div>
<div class=""><font face="Courier" class="">"252923130050";4;"{1,4}"</font></div>
<div class=""><font face="Courier" class="">"252923130050";5;"{1,5}"</font></div>
<div class=""><font face="Courier" class="">"252923130193";1;"{1,1}"</font></div>
<div class=""><font face="Courier" class="">"252923130193";2;"{1,2}"</font></div>
<div class=""><font face="Courier" class="">"252923130193";3;"{1,3}"</font></div>
<div class=""><font face="Courier" class="">"252923130193";4;"{1,4}"</font></div>
</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
<div>
<blockquote type="cite" class="">
<div class="">On Apr 21, 2017, at 9:50 AM, Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com" class="">nicolas.ribot@gmail.com</a>> wrote:</div>
<br class="Apple-interchange-newline">
<div class="">
<div dir="ltr" class="">Hi,
<div class=""><br class="">
</div>
<div class="">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.</div>
<div class="">(to adapt in case of multipg):</div>
<div class=""><br class="">
</div>
<div class="">
<div class=""><font face="monospace, monospace" class="">with tmp as (</font></div>
<div class=""><font face="monospace, monospace" class=""> select 1::int as id, 'POLYGON((0 0, 1 0, 1 1, 0 0))'::geometry(polygon) as geom</font></div>
<div class=""><font face="monospace, monospace" class="">), tmp1 as (</font></div>
<div class=""><font face="monospace, monospace" class=""> SELECT id, st_dumppoints(geom) as d</font></div>
<div class=""><font face="monospace, monospace" class=""> FROM tmp</font></div>
<div class=""><font face="monospace, monospace" class="">), tmp2 as (</font></div>
<div class=""><font face="monospace, monospace" class=""> select <a href="http://t1.id/" class="">
t1.id</a>, (t1.d).path[2] as segid, (t1.d).path,</font></div>
<div class=""><font face="monospace, monospace" class=""> st_makeline((t1.d).geom, (t2.d).geom) as seg</font></div>
<div class=""><font face="monospace, monospace" class=""> from tmp1 t1 join tmp1 t2 on
<a href="http://t1.id/" class="">t1.id</a> = <a href="http://t2.id/" class="">t2.id</a> and (t2.d).path[2] = (t1.d).path[2] + 1</font></div>
<div class=""><font face="monospace, monospace" class="">) SELECT *</font></div>
<div class=""><font face="monospace, monospace" class=""> FROM tmp2;</font></div>
</div>
<div class=""><br class="">
</div>
<div class="">Nicolas</div>
</div>
<div class="gmail_extra"><br class="">
<div class="gmail_quote">On 21 April 2017 at 15:59, Basques, Bob (CI-StPaul) <span dir="ltr" class="">
<<a href="mailto:bob.basques@ci.stpaul.mn.us" target="_blank" class="">bob.basques@ci.stpaul.mn.us</a>></span> wrote:<br class="">
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div style="word-wrap:break-word" 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="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap"></span>—- seq,</font></div>
<div class=""><font face="Courier" class=""><span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap"></span>parcelid, </font></div>
<div class=""><font face="Courier" class=""><span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap"></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="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap"></span>select</font></div>
<div class=""><font face="Courier" class=""><span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap"></span>parcelid,</font></div>
<div class=""><font face="Courier" class=""><span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap"></span>ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp,</font></div>
<div class=""><font face="Courier" class=""><span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap"></span>ST_PointN(geom, generate_series(2, ST_NPoints(geom) )) as ep</font></div>
<div class=""><font face="Courier" class=""><span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap"></span>from (</font></div>
<div class=""><font face="Courier" class=""><span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap"></span>select </font></div>
<div class=""><font face="Courier" class=""><span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap"></span>parcelid,</font></div>
<div class=""><font face="Courier" class=""><span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap"></span>ST_AsText(ST_Boundary( (ST_Dump(wkb_geometry)).geom )) as geom</font></div>
<div class=""><font face="Courier" class=""><span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap"></span>from </font></div>
<div class=""><font face="Courier" class=""><span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap"></span>cache.rc_parcel_poly </font></div>
<div class=""><font face="Courier" class=""><span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap"></span>where </font></div>
<div class=""><font face="Courier" class=""><span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap"></span>objectid IN (46813, 46814, 46815)</font></div>
<div class=""><font face="Courier" class=""><span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap"></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="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>st_astext</font></div>
<div class="">
<div class=""><font face="Courier" class="">“252923130050";<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(569983.06682238 166280.506289959,569982.<wbr class="">969482869 166321.960092038)"</font></div>
<div class=""><font face="Courier" class="">“252923130050";<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(569982.969482869 166321.960092038,570115.<wbr class="">252698928 166324.171799228)"</font></div>
<div class=""><font face="Courier" class="">“252923130050";<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(570115.252698928 166324.171799228,570115.<wbr class="">328402415 166282.171405315)"</font></div>
<div class=""><font face="Courier" class="">“252923130050";<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(570115.328402415 166282.171405315,569991.<wbr class="">947482795 166280.591207951)"</font></div>
<div class=""><font face="Courier" class="">“252923130050";<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(569991.947482795 166280.591207951,569983.<wbr class="">06682238 166280.506289959)"</font></div>
<div class=""><font face="Courier" class="">“252923130193";<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(569322.752598196 166436.412697807,569190.<wbr class="">786695376 166434.788415357)"</font></div>
<div class=""><font face="Courier" class="">“252923130193";<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(569190.786695376 166434.788415357,569190.<wbr class="">670520335 166477.753622204)"</font></div>
<div class=""><font face="Courier" class="">“252923130193";<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(569190.670520335 166477.753622204,569322.<wbr class="">618897527 166479.994418308)"</font></div>
<div class=""><font face="Courier" class="">“252923130193";<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(569322.618897527 166479.994418308,569322.<wbr class="">752598196 166436.412697807)"</font></div>
<div class=""><font face="Courier" class="">“252923130047";<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(570115.1013823 166408.172812894,569982.<wbr class="">770015925 166406.868192479)"</font></div>
<div class=""><font face="Courier" class="">“252923130047";<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(569982.770015925 166406.868192479,569982.<wbr class="">672586083 166448.324117512)"</font></div>
<div class=""><font face="Courier" class="">“252923130047";<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(569982.672586083 166448.324117512,570115.<wbr class="">025814325 166450.173206791)"</font></div>
<div class=""><font face="Courier" class="">“252923130047";<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(570115.025814325 166450.173206791,570115.<wbr class="">1013823 166408.172812894)"</font></div>
</div>
<div class=""><br class="">
</div>
<div class="">
<div style="font-family: 'Comic Sans MS'; font-size: 12px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-variant-east-asian: normal; font-variant-position: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;" class="">
I’m trying to get to this output:</div>
<div style="font-family: 'Comic Sans MS'; font-size: 12px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-variant-east-asian: normal; font-variant-position: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;" class="">
<br class="">
</div>
<div style="font-variant-ligatures:normal;font-variant-east-asian:normal;text-align:start;text-indent:0px" class="">
<div style="font-variant-ligatures:normal;font-variant-east-asian:normal" class="">
<div class=""><font face="Courier" class="">seq,<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>parceled,<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>st_astext</font></div>
<div class="">
<div class=""><font face="Courier" class="">1<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>“252923130050”;<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(569983.06682238 166280.506289959,569982.<wbr class="">969482869 166321.960092038)"</font></div>
<div class=""><font face="Courier" class="">2<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>“252923130050";<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(569982.969482869 166321.960092038,570115.<wbr class="">252698928 166324.171799228)"</font></div>
<div class=""><font face="Courier" class="">3<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>“252923130050”;<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(570115.252698928 166324.171799228,570115.<wbr class="">328402415 166282.171405315)"</font></div>
<div class=""><font face="Courier" class="">4<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>“252923130050”;<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(570115.328402415 166282.171405315,569991.<wbr class="">947482795 166280.591207951)"</font></div>
<div class=""><font face="Courier" class="">5<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>“252923130050”;<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>“LINESTRING(569991.947482795 166280.591207951,569983.<wbr class="">06682238 166280.506289959)"</font></div>
<div class=""><font face="Courier" class=""><br class="">
</font></div>
<div class=""><font face="Courier" class="">1<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>“252923130193”;<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(569322.752598196 166436.412697807,569190.<wbr class="">786695376 166434.788415357)"</font></div>
<div class=""><font face="Courier" class="">2<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>“252923130193”;<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(569190.786695376 166434.788415357,569190.<wbr class="">670520335 166477.753622204)"</font></div>
<div class=""><font face="Courier" class="">3<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>“252923130193”;<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(569190.670520335 166477.753622204,569322.<wbr class="">618897527 166479.994418308)"</font></div>
<div class=""><font face="Courier" class="">4<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>“252923130193”;<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>“LINESTRING(569322.618897527 166479.994418308,569322.<wbr class="">752598196 166436.412697807)"</font></div>
<div class=""><font face="Courier" class=""><br class="">
</font></div>
<div class=""><font face="Courier" class="">1<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>“252923130047”;<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(570115.1013823 166408.172812894,569982.<wbr class="">770015925 166406.868192479)"</font></div>
<div class=""><font face="Courier" class="">2<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>“252923130047”;<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(569982.770015925 166406.868192479,569982.<wbr class="">672586083 166448.324117512)"</font></div>
<div class=""><font face="Courier" class="">3<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>“252923130047”;<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>"LINESTRING(569982.672586083 166448.324117512,570115.<wbr class="">025814325 166450.173206791)"</font></div>
<div class=""><font face="Courier" class="">4<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>“252923130047”;<span class="m_-8052612792053816388Apple-tab-span" style="white-space:pre-wrap">
</span>“LINESTRING(570115.025814325 166450.173206791,570115.<wbr class="">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="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;" class="">
<br class="">
</div>
<br class="">
</div>
<div style="font-family: 'Comic Sans MS'; font-size: 12px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-variant-east-asian: normal; font-variant-position: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;" class="">
<div class="m_-8052612792053816388wp_quotepage_quote">An inventor is simply a fellow who doesn’t take his education too seriously.</div>
<div class="m_-8052612792053816388wp_quotepage_author">—Charles F. Kettering</div>
</div>
</div>
<br class="">
</div>
</div>
</div>
<br class="">
______________________________<wbr class="">_________________<br class="">
postgis-users mailing list<br class="">
<a href="mailto:postgis-users@lists.osgeo.org" class="">postgis-users@lists.osgeo.org</a><br class="">
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank" class="">https://lists.osgeo.org/<wbr class="">mailman/listinfo/postgis-users</a><br class="">
</blockquote>
</div>
<br class="">
</div>
_______________________________________________<br class="">
postgis-users mailing list<br class="">
<a href="mailto:postgis-users@lists.osgeo.org" class="">postgis-users@lists.osgeo.org</a><br class="">
https://lists.osgeo.org/mailman/listinfo/postgis-users</div>
</blockquote>
</div>
<br class="">
<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="">
<br class="Apple-interchange-newline">
<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="">
<dt class="">"In the end, everything is a gag."</dt><dd class="">- Charlie Chaplin
<div class=""><br class="">
</div>
</dd></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>
<br class="Apple-interchange-newline">
</div>
<br class="">
</div>
</div>
</div>
</body>
</html>