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