Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

I am editing a road network on one layer, and am looking for a quick way to show the roads that don't intersect (don't meet or overlap). Is SQL the best way to do it? If so I am looking for how to produce the correct code in the 'where Condition' step in the SQL Select Window.

share|improve this question

You probably mean "almost intersect", because in a road network there will be a lot of roads which don't intersect at all. The SQL below works on the original + a copy of the table, where you have a unique ID field and a dummy field with 0 for all records. But it gets really slow, even on small tables. The idea is to find objects which don't intersect, but where the buffered objects (5 meter buffer) do intersect.

select link1.id,link2.id
from link1,link2
where link1.dummy=link2.dummy and
link1.id<>link2.id and
not (Link1.obj intersects link2.obj) and
buffer(Link1.obj,20,5,"m") intersects link2.obj

I would recommend that you turn to software specialized in this, such as RouteFinder from www.routeware.dk, which has been developed by me and some colleagues.

share|improve this answer
    
Nice suggestion, Uffe, maybe he want to find the records that don't intersect any. But you are right, using a specialised tool like yours would make a lot of sense. – Peter Horsbøll Møller Feb 29 at 9:38
    
Thanks for your reply Uffe. I had 5000 lines (& PC is 8GB RAM) so probably don't need the specialised software. Thanks anyway. – GISlearner Mar 1 at 22:57

You can do this with SQL.

  1. Make sure your table has an ID column with unique ID's for each record
  2. Make a copy of your table
  3. Open the copy table
  4. Run this query to get a list of ID's that do intersect:
Select ROADS.ID 
   From ROADS, ROADS_COPY
   Where ROADS.OBJ Intersects ROADS_COPY.OBJ
   And ROADS.ID <> ROADS_COPY.ID
   Group By ROADS.ID
   Into __INTERSECTIONS NoSelect

Now you need to find the records that don't intersect by comparing the ID's to the ID's in the list:

Select * From ROADS
   Where Not ID In (Select ID From __INTERSECTIONS)

Depending on the amount of data this query might be slow and you might get a better performance by selecting the records that do intersects and then use the Invert Selection tool to select those that don't:

Select * From ROADS
   Where ID In (Select ID From __INTERSECTIONS)

Now click the Invert Selection tool.

This will however only give you a list of roads that don't intersect. There are a number of other possible issues which you really would need a Routing tool to bring forward. RouteFinder (the Pro version) has tools for this built-in.

share|improve this answer
1  
Thanks Peter - your answer really worked. It narrowed down some 5000 lines of road to 470 lines of road that don't intersect. That will more easily isolate the roads of interest (not intersecting) and save significant time for me in the project. Although I don't completely understand the code - your answer made the layout of code simpler and feasible for me to apply. Thanks again - much appreciated....:) – GISlearner Mar 1 at 23:01

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.