I have a table of survey locations as
id,from,to,azimuth,x,y
'L1',0,5,120,508776,7098873
'L1',5,10,141,null,null
'L1',10,24,121,null,null
'L2',0,12,135,507882,8020098
'L2',12,15,121,null,null
'L2',15,25,null,null
Each line "id" can have 2 or more records defining their geometry.
Using a postgis query, how can I create line segments for each of these records, assuming the x and y values for the line starts are in EPSG:3578? I've tried LAG and LEAD OVER (Partition BY "id" order by "from_m"), but I get lost in the recursion needed. Is what I'm attempting possible?
If you select the whole table the database will run the LAG/LEAD OVER for every selected row. You can return a new column with the result. I think this is already the recursion you need.
I once did this with time based positions and created a line from the temporal latest point to his ancestor. I ordered by time - in your case you need to order by 'from' or 'to'. Sometimes running an independent query for every id is easier than doing the whole thing at once.
Have a look at this thread, they are trying pretty much the opposite of what you want, but maybe it will help you clarify things.