Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have shp file with 50k lines (roads). Many roads have same name like 'high road' . some of them are the same road (they intersect each other) and some are located in different places of the city.

I want to merge all lines with same name and that intersect to single road. How can I do that? I have available SQL 2012 spatial with geometries and geographic data of roads and QGIS with shp file,

Someone told me in other post to use dissolve, but it looks like dissolve works on polygons only (i don't have selection for line vector layer)

Thank you.

share|improve this question
    
So you are looking to merge all the roads with the same name and that are touching? –  Nathan W Dec 24 '12 at 5:19
    
yes , I don't care if I'll do it via QGIS or sql spatial (2012) , question it , How ... –  Alophind Dec 24 '12 at 6:18
    
The above query merges the roads ,linestrings become multilinestrings, but it does not achieve the desired result. The lines merged appear inthe table (twice, once as a section, then once as a merged multilinestring. as this I'm sure is a common problem I hope a better solution can be found, I experimented with the group by clause without much luck –  user1637477 Apr 16 at 12:28

1 Answer 1

up vote 2 down vote accepted

I can describe how to do this in spatialite. You'll probably be able to adapt it to sql server. First import the shapefile into spatialite

.loadshp "roads" roads (code page) (srid)

Suppose the roads table now has columns: id as primary key, name, and geometry. Create a duplicate, empty table for the merge:

CREATE TABLE roads_merge (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
SELECT AddGeometryColumn('roads_merge','geometry',(srid),'LINESTRING','XY');

Now insert into the new table the columns that intersect with identical names:

INSERT INTO roads_merge (name, geometry) 
SELECT r1.name, GUnion(r1.geometry, r2.geometry)
FROM roads AS r1, roads AS r2 
WHERE r1.id <> r2.id AND ST_Intersects(r1.geometry, r2.geometry) AND r1.name = r2.name;

And now insert all the single roads:

INSERT INTO roads_merge(name, geometry)
SELECT r1.name, r1.geometry
FROM roads AS r1, roads AS r2
WHERE r1.id <> r2.id AND Disjoint(r1.geometry, r2.geometry) AND r1.name <> r2.name;

I would probably first create a spatial index and use it in above the query.

HTH, Micha

share|improve this answer
    
in postgis it would be st_linemerge or st_union if you want to merge lines to one non multi geometry (if possible), maybe sql server or spatiallite has samekind function? –  simplexio Apr 16 at 12:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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