Version 2 (modified by kneufeld, 4 years ago)

--

# Examples Network Topology

Suppose you had a table of linestrings that represented a linear network (i.e. roads).

Here's one approach to linking the line segments together via to_node / from_node attributes.

1. First, generate a table of distinct nodes, that is, a distinct collection of all the start and end points of the line segments.

```CREATE TABLE nodes AS
SELECT
the_geom,
FROM (
SELECT
ST_StartPoint(the_geom) AS the_geom,
UNION ALL
SELECT
ST_EndPoint(the_geom) AS the_geom,
) AS foo
GROUP BY the_geom;
```

The above query creates a node table from a roads table by selecting all the start and end points from the road segments while maintaining the link between the node geometry and the line segment id. Then nodes are grouped into a single topologically distinct set where every POINT references an array of line segment ids as integer[].

2. The next step is to assign a unique id for each node and to add two new attributes to the roads table to hold the node references.

```ALTER TABLE nodes ADD COLUMN node_id serial;
ALTER TABLE nodes ADD PRIMARY KEY (node_id);

```

3. Now that we have our node table with line segment references, we want to update the from_node and to_node attributes of the roads table to reference the appropriate node. The obvious first approach would be to simply update the roads table using the appropriate reference in the nodes table:

```UPDATE roads a
SET from_node = b.node_id
FROM nodes b
SET to_node = b.node_id
FROM nodes b
```

Unfortunately, PostgreSQL does not seem to use any btree indexes we might have placed on the id arrays, road_leaving and road_entering. Such queries estimated to take many hours to execute on a small table of 50000 roads. Subsequently, the following approach can be taken:

```CREATE TABLE nodes_expanded AS
SELECT
node_id,
FROM (
SELECT
node_id,
FROM nodes
) AS foo;

CREATE UNIQUE INDEX nodes_expanded_leav_idx ON nodes_expanded (road_leaving);
CREATE UNIQUE INDEX nodes_expanded_ent_idx ON nodes_expanded (road_entering);
ANALYZE nodes_expanded;
```

The above query expands the node table, essentially removing the integer array field we created earlier.

Now we can update our roads table with appropriate from_node / to_node values taking mere seconds to process.

```UPDATE roads a
SET from_node = b.node_id
FROM nodes_expanded b