Several streets are split into different sections, sometimes this is because there is an attribute change such as a speed limit.
either way I want to join these streets together when:
Their geometry intersects ( ie one point of 1 linestring touches another linestring) AND the street name is the same)
so I uploaded the shape file into SpatialLite GUI,
set the ESRI and geom type
and ran
CREATE TABLE rds_merge (id INTEGER PRIMARY KEY AUTOINCREMENT, OSM_ID integer, name TEXT);
SELECT AddGeometryColumn(rds_merge','geometry',4326,'MULTILINESTRING','XYZ');
then:
INSERT INTO rds_merge (OSM_ID,NAME,geometry)
SELECT r1.OSM_ID, r1.name, GUnion(r1.Geometry, r2.Geometry)
FROM NZRoadCentrelines12414 AS r1, NZRoadCentrelines12414 AS r2
WHERE r1.OSM_ID <> r2.OSM_ID AND ST_Intersects(r1.Geometry, r2.Geometry) AND r1.NAME = r2.NAME;
rds_merge being the new table,
I was expecting to have a list of all roads that touched, with unique OSM_ID's and identical names
what I get is this :
clearly I have duplicate OSM_ID's with the SAME name.
any ideas where I am going wrong ?
