4

I am looking for a way to join all lines that are connected, have the same direction, i.e. start point of one is end point of the other, and share some common attributes. In fact, I want to connect oneway streets having an common start/end point and the same oneway direction. I found a way to identify pairs of lines that fulfill these condition:

select a.osm_id, a.name,a.oneway,
       b.osm_id, b.name,b.oneway, from planet_osm_line a, planet_osm_line b 
   WHERE (st_startpoint(a.way)=st_endpoint(b.way) or st_endpoint(a.way)=st_startpoint(b.way))
         and a.oneway=b.oneway ;

I am now looking for a way to merge the geometries of these lines.

  • That query will give you doubles. Since you're doing a self-join you'll get pairs (road1,road2) and (road2,road1). – Jakub Kania May 25 '13 at 9:46
  • Simplifying the where condition to WHERE st_startpoint(a.way)=st_endpoint(b.way) solves this issue – user17607 May 25 '13 at 10:48
  • You can merge the linestrings with ST_LineMerge(a.way, b.way), but you'll have to insert this new geometry into a new table. And you'll have to decid what to do with the 'name' and 'osm_id' columns... – Micha May 25 '13 at 15:21
  • ST_LineMerge(st_union(a.way, b.way)) works. However, I thought more about an aggregate query with a group clause. In addition, the query is extremely slow, I suppose due to the fact that the extremities of all lines are compared against each other. – user17607 May 25 '13 at 16:13
1

I found a solution, based on a function:

CREATE OR REPLACE FUNCTION joinOneways() RETURNS SETOF planet_osm_line AS
   $BODY$
   DECLARE a record;
   DECLARE b record;
   DECLARE c integer;    
   BEGIN
      drop table if exists pool;
      create table pool as select * from planet_osm_line where coalesce(oneway,'') not in ('','no');
      LOOP
         select count(*) from pool into c;
         IF c = 0 THEN
            RETURN;
         END IF;
         FOR a IN EXECUTE 'select * from pool  order by st_length(way) DESC;'
         LOOP
            FOR b IN SELECT *  FROM pool  order by st_length(way) DESC
            LOOP
               IF st_touches(a.way,b.way) AND (st_startpoint(a.way)=st_endpoint(b.way)  or st_startpoint(b.way)=st_endpoint(a.way))
                  AND a.name=b.name THEN
                  a.way = st_linemerge(st_union(a.way,b.way));
                  EXECUTE 'delete from pool where osm_id=$1' USING b.osm_id;
               END IF;            
            END LOOP;
            EXECUTE 'delete from pool where pool.osm_id=$1' USING a.osm_id;
            RETURN NEXT a; 
            EXIT;
         END LOOP;
      END LOOP;
      RETURN;
   END
   $BODY$
   LANGUAGE 'plpgsql';

   CREATE TABLE oneways as select * from joinOneways();

The function is not perfect as it is not looking for the longest contiguous oneway path. It may be not very performant neither .

| improve this answer | |

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.