Generate and merging results of geometry function into one column in Postgis

Question

I need to create a table of points based on linestring.

Basically I have a linestring table (l_table) and I will generate start, end, centroid of each line. It's necessary to merge these 3 columns into one geometry points column.I desire to keep the original "ID" column associated with the new geometry point. Finally, One more column to describe the new point geometry: start, end or centroid.

Something like this:

 create table point_from_line as (
     select l.id, st_startpoint(l.geom), st_centroid(l.geom), st_endpoint(l.geom)
     case st_startpoint ... then 'start'
     case st_centroid ... then 'centroid'
     case st_endpoint ... then 'end'
     end as geom_origin
     from linestring_table  
     where l.id any condition... 

Note: the geom column result need to be associated with the original id of linestring and one more column to describe if is start, center or end point.

Input columns: l.id, l.geom (linestring);

Output columns: l.id, p.geom (the unified result of st_start, st_centroid, st_endpoint), _p.geometry_origin_ (classification in: start,end, centroid for each row of p.geom)

Could someone help me?

Answer 1

Complex and good question, I will try to collaborate. (haha)

after several hours I think I get a direction to my problem. So, I will explain by steps:

1 - create 3 recursive cte's: Start, Center, endpoint + the respective classification

  with Recursive cte as
     start point of linestring
     select l.id, st_startpoint(l.geom),
         case when
         l.geom is not null then 'start point'
         end as geom_origin
     where l.somecolumn = 'somefilter'...

2 - repeat the same above query modifying the parameters for centroid and endpoint

3 - Union all for the 3 generated cte's yes the id will be repeated, but that is the intention.

4 - Allocate the 3 ctes in a subquery for generate a unique id value, called by "gid"

So, the final query seems like this:

select row_number() over () as gid, cte.id, cte.geom, cte.geom_origin
from (
  with Recursive cte as ( select...),
  cte_2 (select...),
  cte_3 (select...),
)

select * from cte
union all 
select * from cte_2
union all
select * from cte_3
);

If someone know another way to reach the same results, please share, but for now, that's solve my doubt.

Regards