1

Presently these linesegments are in different rows but in the same table and numbers shown in the image are the ids of respective linestrings. I want to copy linesegment 350, 333, 352,4,.. in one table while 1601, 1602,356, 576,580,344,.. in another table. In terms of algebra set A members in one table and set B in another though there is intersection seen visually. This is a representative sample and these sets have more ids than mentioned here and have more sets than two A and B. Can we do using queries in postgis or using python script

Postgres table shown in QGIS for linestring

1

If I understand translat your question correctly, I would go in this direction:

1) collect all the lines, for example by street name or by condition if they (segments) touch each other;

2) create two copies of them:

SELECT * INTO roads_1 FROM source_table; SELECT * INTO roads_2 FROM source_table;

3) remove intersecting streets:

DELETE FROM roads_1 as t1 WHERE EXISTS (SELECT 1 FROM roads_1 as t2 WHERE t2.id<t1.id AND ST_Intersects(t2.geom, t1.geom));

and

DELETE FROM roads_2 as t1 WHERE NOT EXISTS (SELECT 1 FROM roads_2 as t2 WHERE t2.id<t1.id AND ST_Intersects(t2.geom, t1.geom));

edit - addition:

variant 1 -

1.1) create table table_a as SELECT a.id, a.geom as geom FROM source_table a JOIN source_table b ON ST_Intersects (a.geom,b.geom) WHERE a.id=350 or a.id=333 or a.id=352 or a.id=4 GROUP BY a.id, a.geom;

1.2) create table table_b as SELECT a.id, a.geom as geom FROM source_table a JOIN source_table b ON ST_Intersects (a.geom,b.geom) WHERE a.id=1601 or a.id=1602 or a.id=356 or a.id=576 or a.id=580 or a.id=344 GROUP BY a.id, a.geom;

variant 2 -

2.1) create table table_a as SELECT ST_Union(a.geom) as geom FROM source_table a JOIN source_table b ON ST_Intersects (a.geom,b.geom) WHERE a.id=350 or a.id=333 or a.id=352 or a.id=4;

2.2) create table table_b as SELECT ST_Union(a.geom) as geom FROM source_table a JOIN source_table b ON ST_Intersects (a.geom,b.geom) WHERE a.id=1601 or a.id=1602 or a.id=356 or a.id=576 or a.id=580 or a.id=344;

  • What if we have more linestrings, I anticipate that we can use union and intersection or something from available functions in Postgres that can take care of line segments connected to form single multilinestring. Take a scenario of Road networks having square and is main long road passing through big city e.g. Oxford street of london which has several line segments. – Aaron Jul 4 at 17:40
  • In that case, merging is not the goal but want to remove the unnecessary nodes. while I just want to merge and address the continuous (visually at the moment) line segments probably using any existing available functions – Aaron Jul 4 at 18:05
  • Now it's clear to me that you want to divide the GEODATA into two tables, with the condition that there are no intersections between them... – Cyril Jul 5 at 7:58
  • Yes, you got that. Let me try your hints to see what happens there. Just one issue that I sense is, the linesegments have visual similarity at this moment for example from fig., id 580 and 344 cannot be put into one table using any column name (street name or other_tags ).Thank you. – Aaron Jul 5 at 10:04

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.