Postgis Query Construction - postgresql

Question

I'm a newbie and I need a little bit help with postgis query construction. My situation looks like this. I have a table in postgresql with ways converted from bicycle routes (take from Open Street Map). If there are 2 or more routes on the same way, the lines are overlapping on each other and have exactly the same geometry but have different tags (name, ref etc) I would like to add another column with a counter for these duplicated lines. Let's say that I have 3 bicycle routes on the same way, this equals to 3 lines overlapping each other. These should get an extra tag with a number 1, 2 and 3. This should happend only if there are more than 2 overlapping lines. If there is only one, no number should be added.

I tried ST_Equals but can't get it to work....

Here's my code which is not exactly for this purpose but for merging lines with the same attributes. I would like to add the counters into it.

DROP TABLE  IF EXISTS  public.bicycle_merge;
CREATE TABLE public.bicycle_merge AS
SELECT ST_LineMerge(ST_Collect(way)) AS way,reversed,route, osmc_color,name, network, state, "instance:cycle", "relation:id", ref, description, distance
  FROM planet_osm_line
  WHERE route IN ('bicycle')
  GROUP BY   reversed, route, osmc_color,name, network, state, "instance:cycle", "relation:id", ref, description, distance    

Answer 1

Without having actual data, I'd guess you could use a window function (+ interesting blog post on the topic). Something like select row_number() over (partition by ST_LineMerge(ST_Collect(way))) as overlap_count could work.