PostGIS query for selecting features not connected to rest of road network?

Question

I have a dataset of roads in a city stored in a PostGIS enabled database. I would like to identify and remove any feature which is not connected to the rest of the road network. This behavior is common around the edges of networks, where the data was clipped.

I have found other answers on the GIS SE that do this, but they all appear to use ArcGIS, and I would like to solve the problem using PostGIS, QGIS, or a Python script.

My datasets can include over a million features, so performance is a concern.

Answer 1

You can easily use PostGIS to select roads that don't intersect any other road:

SELECT id, geom FROM roads a
WHERE NOT EXISTS 
     (SELECT 1 FROM roads b 
      WHERE a.id != b.id
      AND   ST_Intersects(a.geom, b.geom))

Answer 2

You can use this QGIS Python script to detect lines that are not connected to anything:

from qgis.utils import iface

layer = iface.mapCanvas().currentLayer() # Selected layer

featureList = list( layer.getFeatures() ) # Gets all features
allFeatures = { feature.id(): feature for feature in featureList }

# Spatial indexing
spatialIdx = QgsSpatialIndex()
map( spatialIdx.insertFeature, featureList )

resList = [] # ids of features not connected to anything

for f in featureList:

    # List of potentially connected features from spatial indexing
    ids = spatialIdx.intersects( f.geometry().boundingBox() )

    hasNeighbor = False

    for id in ids:
        ifeature = allFeatures[id]

        if ifeature.id() == f.id():
            continue

        # Checks if f is really intersecting with ifeature
        if f.geometry().intersects(ifeature.geometry()):
            hasNeighbor = True
            break # Exit current for loop

    if (not hasNeighbor) and (not f.id() in resList):
        resList.append( f.id() )

print resList

Note that this won't work on multipart lines. I don't think it could be made much faster...

Answer 3

I had a similar problem, so I developed a QGIS plugin which is now available in the official QGIS plugins repository and can be found in the QGIS menu: Plugins / Manage and Install Plugins... and search for Disconnected Islands.

http://plugins.qgis.org/plugins/disconnected-islands/

This plugin runs on a polyline layer, building up a road (or rail, etc.) network graph of connected links. It then analyses connected subgraphs, ones that are connected to each other, but not connected to isolated or floating links. It creates an additional attribute containing the group ID of the subgraph. This can then be used to style the layer with Categorised styles, or Zoom to selection. The disconnected links can then be fixed or removed.

Source code can be forked from: https://github.com/AfriGIS-South-Africa/disconnected-islands

My dataset has about 2 million links and it was analysed in less than 15 minutes, using 55 GB of RAM.

Answer 4

You can strip out the easy roads with @dbaston's method first, then use pgRouting to find the more complicated cases, such as when you have a network of roads that is not connected to some other network.

Choose a road segment which is definitively within the main network, then try to find a route from each other segment to that one. If no route exists, delete the segment.