I have a linear layer with line type A,B,C (red,green,blue). For each linear feature I added MANUALLY sequence numbers (for line with name
A: 1,2,3,4,5; for line with name
B: 1,2,3; for line with name
How can I add sequence numbers automatically with saving sequence in line direction (from start to end line)?
I tried the next SQL query:
DROP TABLE IF EXISTS route_lines_test; CREATE TABLE route_lines_test AS SELECT t.geom, t."name", t."order", row_number() OVER (PARTITION BY t."name" ORDER BY ST_X(ST_Centroid(t.geom))) AS order_sql FROM ( SELECT geom, gid, "name", "order" FROM line_layer ORDER BY "name", ST_X(ST_Centroid(geom)) ) t
where I added line ordering by line centroid 'X' coordinate, but received incorrect result for line 'B' (green)