I am trying to find the common intersections between several multi line segments in postgis. I have a solution, it seems to work for small data networks,but it takes its time for large networks. My set up is

create table test(id serial, name text );

ALTER TABLE test ADD PRIMARY KEY (id); select addGeometryColumn('test','the_geom',3857,'MULTILINESTRING',2); create table test_results(id serial, name text ); ALTER TABLE test_results ADD PRIMARY KEY (id); select addGeometryColumn('test_results','the_geom',3857,'MULTILINESTRING',2); Load some data

insert into test (name,the_geom) values('Line_1',st_setsrid(ST_LineMerge( ST_GeomFromText('MULTILINESTRING((1 1,2 2,3 3),(4 4 ,5 5))')),3857)); insert into test (name,the_geom) values('Line_2',st_setsrid(ST_LineMerge( ST_GeomFromText('MULTILINESTRING((1 1,2 2,3 0,0 0,0 4),(4 4 ,5 5))')),3857));insert into test (name,the_geom) values('Line_3',st_setsrid(ST_LineMerge( ST_GeomFromText('MULTILINESTRING((1 1,2 2,4 4,0 0,0 4),(4 4 ,5 5))')),3857));

Compute all matching segments

insert into test_results(name,the_geom) select t1.name|| ' '|| t2.name as name  ,st_intersection(t1.the_geom,t2.the_geom) as  the_geom from test t1, test t2 where st_intersects(t1.the_geom,t2.the_geom) and  st_equals(t1.the_geom ,t2.the_geom)=false;

i.e. find every intersection in every line where the lines are not same as each other. It gives the following results in qgis

where Line 1 green Line 2 red Line 3 yellow Combine Black

Is there a faster way of getting this to work ? enter image description here

Your Answer

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

Browse other questions tagged or ask your own question.