[postgis-users] [Solved] Linemerge roads

[Peter]

FTR here is my adopted solution to labeling a roading layer. Its long winded but works. As im just learning id welcome any improvements/suggestions.

Nb: the unique road 'id' in the dataset is called road_name_, and there are between 1 and lots of records per id, as the road surface changes or has oddities like small Ys, or Ps.

-- remove all those roads with no names to r2
create table r2 as
select gid,road_name_,name,road_highw,the_geom
from nzroads
where name is null and road_name_ is null

-- load the rest to r3
create table r3 as
select gid,road_name_,name,road_highw,road_surfa,the_geom
from nzroads
where not ( name is null and road_name_ is null)

-- take out oddities to r4  (20 or so)
create table r4 as
select gid,road_name_,name,road_highw,road_surfa,the_geom
from r3
where  name is null  or road_name_ is null or road_name_ <'1000000000000' or road_name_ ~* '[a-z]'

delete from r3
where  name is null  or road_name_ is null or road_name_ <'1000000000000' or road_name_ ~* '[a-z]'

-- multipart the multilinestrings from r3
create table r5 as
select
   row_number() over (order by road_name_)::int AS gid,
   count(gid) as nlines,
   road_name_,
   (st_collect(the_geom)) as the_geom
from r3
group by road_name_

-- then linemerge them so they can be labelled as one
create table r6 as
select
   row_number() over (order by road_name_)::int AS gid,
   road_name_,
   (st_linemerge(the_geom)) as the_geom
from r5

-- but first separate the resulting single and multis to r7 and r8
-- to save cpu on the much larger set of simple cases
create table r8 as
select
   row_number() over (order by road_name_)::int AS gid,
   road_name_,
  the_geom
from r6
where GeometryType("the_geom") = 'MULTILINESTRING'

create table r7 as
select
   row_number() over (order by road_name_)::int AS gid,
   road_name_,
  the_geom
from r6
where GeometryType("the_geom") = 'LINESTRING'

-- the multis are the trickiest
-- buffer to 1m, and merge by id
create table r9 as
select
   row_number() over (order by road_name_)::int AS gid,
   road_name_,
   ST_Buffer(the_geom, 1) as the_geom
from r8

--  then re-multipart so we can label the sep parts indepedantly
create table r10 as
select
   row_number() over (order by road_name_)::int AS gid,
   road_name_,
   (st_dump(the_geom)).geom as the_geom
from r9

-- label the resultant polys using pointonsurface, a close enough approximation to line mid point
create table r11 as
select
   row_number() over (order by road_name_)::int AS gid,
   road_name_,
   st_pointonsurface(the_geom) as the_geom
from r10

-- label the easier single linestring midpoints
create table r12 as
select
   row_number() over (order by road_name_)::int AS gid,
   road_name_,
   ST_Line_Interpolate_Point(the_geom,0.5) as the_geom
from r7

-- merge both sets of points, r11 and r12
create table r13 as
SELECT * FROM r11
UNION ALL
SELECT * FROM r12

-- join road names back in from r3
-- is there a way to do this join in one step?
create table r14 as
select distinct on (road_name_) * from r3
order by road_name_

create table  r15 as
SELECT r13.gid, r14.name, r14.road_name_, r14.road_highw, r14.road_surfa, r13.the_geom
FROM r13 LEFT JOIN r14   ON r14.road_name_ = r13.road_name_
order by r13.road_name_

Peter