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?

Welcome to SO. Please share input records and expected output in text format by editing original post. — Digvijay S

Caio Mattos Caio Mattos · Accepted Answer · 2020-05-12T20:10:24

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

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

1 Answers