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

Fri Apr 21 09:17:58 PDT 2017

Well that was easy!!  Thanks Nicolas . . . . (I had to read up on that PATH stuff though . . .    :c)

bobb

with tmp as (
 -- select 1::int as id, 'POLYGON((0 0, 1 0, 1 1, 0 0))'::geometry(polygon) as geom
  select
parcelid as id,
( (ST_Dump(wkb_geometry)).geom ) as geom
from
cache.rc_parcel_poly
where
objectid IN (46813, 46814, 46815)
), 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;

output:

"252923130047";1;"{1,1}"
"252923130047";2;"{1,2}"
"252923130047";3;"{1,3}"
"252923130047";4;"{1,4}"
"252923130050";1;"{1,1}"
"252923130050";2;"{1,2}"
"252923130050";3;"{1,3}"
"252923130050";4;"{1,4}"
"252923130050";5;"{1,5}"
"252923130193";1;"{1,1}"
"252923130193";2;"{1,2}"
"252923130193";3;"{1,3}"
"252923130193";4;"{1,4}"

On Apr 21, 2017, at 9:50 AM, Nicolas Ribot <nicolas.ribot at gmail.com<mailto:nicolas.ribot at gmail.com>> wrote:

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<http://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<http://t1.id/> = t2.id<http://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<mailto: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<mailto:postgis-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users

"In the end, everything is a gag."
- Charlie Chaplin

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