Beanshell script: Strahler stream order

Download

• Sample data: test_hydro.gdms

Usage

For this script to execute correctly, your input network must be a mathematical tree. That is, it must be an undirected graph in which any two vertices are connected by exactly one simple path. In particular, it must not contain any loops. If your network it not a tree, the script should execute, but the results will be incorrect.

In addition, you must specify which vertex you want to consider to be the root of the tree.

/** This script takes an input_table consisting of one-dimensional geometries
 *  (i.e., a hydrological network). It is assumed that the network is a 
 *  mathematical tree. It is up to the user to specify the root node.
 *  The script:
 *  - creates a graph from the network.
 *  - calculates the Strahler numbers on the nodes.
 *  The result:
 *  - A table of nodes and a table of edges, each with their
 *    : geometries
 *    : ids
 *    : Strahler numbers
 */

// The input table.
input = "test_hydro";
// The root node id.
root = 84;

// 0. Construct the graph.
sql("EXECUTE ST_Graph(" + input + ", 0.01, false, '" + input + "');");

// 1. Do the Strahler number calculation.
sql("CREATE TABLE strahler AS SELECT * FROM ST_STRAHLERSTREAMORDER(" + input + ".edges, " + root + ");");
// 2. Create the Strahler nodes table.
sql("CREATE TABLE " + input + ".strahler_nodes AS SELECT a.the_geom, b.* FROM " + input + ".nodes a, strahler b WHERE a.id=b.id;");
// 3. Create the Strahler edges table.
//    A. First get the Strahler number of the start and end nodes.
sql("CREATE TABLE tmp1 AS SELECT a.id, a.start_node, b.strahler_number AS start_order, a.end_node FROM " + input + ".edges a, strahler b WHERE a.start_node=b.id;");
sql("CREATE TABLE tmp2 AS SELECT a.*, b.strahler_number AS end_order FROM tmp1 a, strahler b WHERE a.end_node=b.id;");
//    B. Calculate the minimum of the start and end node Strahler numbers.
sql("ALTER TABLE tmp2 ADD COLUMN min_strahler int;");
sql("UPDATE tmp2 SET min_strahler=end_order WHERE (start_order-end_order)>0;");
sql("UPDATE tmp2 SET min_strahler=start_order WHERE (start_order-end_order)<=0;");
//    C. Create the node table.
sql("CREATE TABLE " + input + ".strahler_edges AS SELECT a.the_geom, a.id, b.min_strahler AS strahler_number FROM " + input + ".edges a, tmp2 b WHERE a.id=b.id;");
// 4. Clean up.
sql("DROP TABLE tmp1 PURGE;");
sql("DROP TABLE tmp2 PURGE;");
sql("DROP TABLE strahler PURGE;");

Visualizing the results