Thank you everyone for your responses. Link provided by JGH was very helpful.
It could be done in a very simple with a subquery.
Query bellow picks one OSM way and return its osm id and the array of distances between its nodes.
select ln.osm_id, (with pt as (select st_dumppoints(ln.way) as p)
select array_agg(st_distance((p1.p).geom, (p2.p).geom) :: ...
The algorithm might be looking like this one
WITH RECURSIVE generate_points(sec) AS (
SELECT sec + conf.step
FROM generate_points, conf
WHERE sec + conf.step <= conf.num_segments
conf AS (
SELECT SUM(ST_NPoints(geometry)) AS num_points,
SUM(ST_NPoints(geometry)) - 1 AS num_segments,
1 AS starting_point,
When querying the table of a versioned (or archive-enabled) feature class directly using SQL, your results would not be expected to reflect the current state of the feature class. You should use the versioned view instead. The table could contain records that have been deleted, or records that have been changed, or may be missing records that have been ...
The point is, if you GROUP BY the row id column, you will get one result row per input row (this is equal to grouping by the actual date column)! And since a Linestring is only valid with a minimum of two points, ST_MakeLine adds the same point twice.
SELECT ST_MakeLine(geom ORDER BY date) AS geom
to get one line for all ...
You could wrap everything in another query:
SELECT * FROM (select ...original query...) GROUP BY geom;
But it would be easier to put a filter clause on the count:
count(*) FILTER (WHERE var = 'a'),
count(*) FILTER (WHERE var = 'b'),
count(*) FILTER (WHERE var = 'c')
GROUP BY geom;
If the format is as consistent as your question suggests an advanced python expression alone might work where [LOC_DATA] is replaced with your actual column name.
def FindLabel ( [LOC_DATA] ):
This is really going to boil down to how consistent the data is when it comes to colons, commas, and spaces.