Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Figure A below is the visual representation of what i have in my road data set. Line 1, Line 2, Line 3 in illustration are each represented as 1 row postgis DB inside a geometry column, some of these geometry fields are LINESTRING some of them are MULTILINESTRING. So i totally have 3 rows in database for Figure A.

What i would like to do is to convert this data set to Figure B. In Figure B Line 1 and Line 2 are each broken in to 2 lines since they intersect with Line 3.

After this operation i would like have 5 rows represented as lines in DB instead of 3. I have no clue on how to achieve this.

Figure A

Line 1----------------------------------- 
              | Line 2
Line 3----------------------------------- 

Figure B

Line 1.1 -------- --------------------------- Line 1.2
                 | Line 3
Line 2.1 -------- --------------------------- Line 2.2

Of course this can be done easily after examining the data manually. Problem is i have 30K lines which need to be converted to more lines like this.

share|improve this question
Some juggling of ST_Intersection() and ST_AddPoint() will do it. After doing ST_Intersection() to get the intersection points of a pair of lines, you'll have to check which line intersects at its endpoint (no need to add a point there) and you'll have to take into account the case where two lines intersect exactly at endpoints. No need to add any point in this case. – Micha Feb 16 '13 at 16:22
@Micha ST_Intersection returns the intersection between 2 geoms. Problem is i have 30K geoms so in total there is 30K*30K geom comparisions which i think wont be viable, and i dont know the query in postgis to generate this ST_Intersection() calls 900K times from 30K rows. Does ST_AddPoint() creates a new line? From what i read from docs it adds a point to existing line. – Numenor Feb 17 '13 at 8:32
Create a spatial index on the roads table, and it won't be a problem. – Micha Feb 18 '13 at 12:01

Your Answer


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

Browse other questions tagged or ask your own question.