Adding a sub-series or sub-index to output

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Adding a sub-series or sub-index to output

Nicolas Ribot-2
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, (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;

Nicolas

On 21 April 2017 at 15:59, Basques, Bob (CI-StPaul) <[hidden email]> 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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Adding a sub-series or sub-index to output

Basques, Bob (CI-StPaul)
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 <[hidden email]> 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, (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;

Nicolas

On 21 April 2017 at 15:59, Basques, Bob (CI-StPaul) <[hidden email]> 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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



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





_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...