# 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[].

```-- node table DDL
CREATE TABLE nodes (
the_geom geometry,
);
```

Note: This step made use of the aggregate function 'array_accum', which for some reason is not defined by default in postgresql, but can be added to any database easy enough.

1. 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);

-- Sample data in table
---------+-----------------+----------------
1764 | {712,NULL,NULL} | {NULL,781,745}
(1 row)
```
1. 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 make 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
SET to_node = b.node_id
FROM nodes_expanded b
```

```-- Sample data from the new roads table