Adding a new column with value in a virtual layer query

Question

I've created start and end points from a LineString layer using this answer from another question:

select id, sts as route_id, st_endpoint(geometry) as geometry from pointclasstest
union
select id, sts as route_id, st_startpoint(geometry) as geometry from pointclasstest

According to the comments to that post, using union is the only option to show both start and endpoints at the same time.

Now I am looking for a way to still seperate start and end points from each other within the layer via an added attribute, e. g. start=1, end=2

Is there a possibility to create a new field via SQL query when creating the layer and writing a given value into it?

I've read into adding columns via SQL, but they all refer to an already existing table (ALTER TABLE, ADD, etc.)

score 4 · Accepted Answer · answered Sep 05 '23 at 07:42

4

Just add it with a fixed value and give it a name:

select id, sts as route_id, st_endpoint(geometry) as geometry, 1 as something from pointclasstest
union
select id, sts as route_id, st_startpoint(geometry) as geometry, 2 as something from pointclasstest

answered Sep 05 '23 at 07:42

MrXsquared

34,292
21
67
117

You were so fast ^_^. Great answer anyway! – Taras Sep 05 '23 at 07:43
worked like a charm, thank you! sometimes I just don't see it even though it is so obvious – e-shirt Sep 05 '23 at 10:16

Adding a new column with value in a virtual layer query

1 Answers1

Linked