Dumping Points from Polygon and do st_makeline with these?

Question

I want to manipulate Points from a (Rectangle) Polygon. First, I dump the points using st_dumppoints, then aggregate the points again using st_makeline(case when ... then ... else). Then I use st_makepolygon to create a polygon again.

In some cases that gives me an error "ERROR: lwpoly_from_lwlines: shell must be closed". That tells me that the first and last point of my created line is not equal.

First I checked my input polygons. They are Good! Second I checked my condition inside st_makeline by just leaving it out => problem still exists.

Then I tried to break it down, and the problem exists with this code:

select fid from (
  select fid, st_makeline(geom) line from (
    select fid, (st_dumppoints(the_geom)).geom from index
  ) a group by fid
) b
where not st_equals(st_startpoint(line),st_endpoint(line));

I receive a list of fid's. Now the interesting part: The problem does not occur, if I add one or multiple specific fids FROM THAT LIST to the where clause anywhere in any stage.

select fid from (
  select fid, st_makeline(geom) line from (
    select fid, (st_dumppoints(the_geom)).geom from index
  ) a group by fid
) b
where not st_equals(st_startpoint(line),st_endpoint(line))
and fid = 44511
-- and fid in (44511,44524,44529)
;

(also works with multiple fids (and fid in (1,2,3))

I'm lost .... What's happening here ?

---

UPDATE

I made some further research:

select fid, point_numbers from (
  select fid, string_agg(((points).path[2])::varchar,',') point_numbers
  from (
     select fid, (st_dumppoints(the_geom)) points from index
  ) a group by fid
) b
limit 3;

 fid | point_numbers 
-----+---------------
   1 | 5,4,3,2,1
   2 | 1,2,3,4,5
   3 | 2,4,5,3,1
(3 rows)

vs.

select fid, point_numbers from (
  select fid, string_agg(((points).path[2])::varchar,',') point_numbers
  from (
    select fid, (st_dumppoints(the_geom)) points from index
  ) a group by fid
) b
where fid=3;

 fid | point_numbers 
-----+---------------
   3 | 1,2,3,4,5
(1 row)

Answer 1

I found myself a workaround Using "order by ..." within the aggregate function via this answer on stackoverflow.

select fid, point_numbers from (
  select fid, string_agg(((points).path[2])::varchar,',' order by (points).path[2]) point_numbers
  from (
     select fid, (st_dumppoints(the_geom)) points from index
  ) a group by fid
) b
limit 3;

 fid | point_numbers 
-----+---------------
   1 | 1,2,3,4,5
   2 | 1,2,3,4,5
   3 | 1,2,3,4,5
(3 rows)