3

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    
  • 1
    Welcome to GIS SE. It is policy here that coding questions must provide code. Please edit the question to contain descriptions of the tables and the SQL you had tried. – Vince Mar 3 '16 at 11:05
3

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.

  • Hi Tkan. I have to change my strategy. Your solution actually worked but I need something slightly different. How can I find unique geometries in my table and add a column which will contain for example letter "U"? All overlaping geometries should be blank in this column. – Voyteck Mar 4 '16 at 21:15
  • Geometries can be grouped like any other column. You'd find them by using group by geometry and having count(*) = 1. This would display all geometries which are unique in the table. And those overlapping ones can be added by using UNION ALL and having count(*) > 1. – tkan Mar 9 '16 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.