Beanshell script: Weighted

/** This script takes an input_table consisting of one-dimensional geometries
 *  (i.e., a road network). It then:
 *  - inserts a column of doubles named weight_column filled in by the given weight_function
 *  - sets the graph orientation to orientation
 *  - calculates connected components
 *  - does weighted graph analysis on the given connected component numbered by ccNumber.
 *  The result is a table consisting of the nodes of the graph with their corresponding:
 *  - id
 *  - closeness centrality
 *  - betweenness centrality.
 */

/** The input table name. */
input_table = "routes";
/** The name to give to the column containing only 1s */
weight_column = "weight";
/** Here we set the weights to be the length of the geometry */
weight_function = "ST_Length(the_geom)";
/** The graph orientation */
orientation = "'undirected'";
/** The number of the connected component to consider */
ccNumber = "1";
 
/** 1.  Construct the graph */
sql("EXECUTE ST_Graph(" + input_table + ", 0.01, false, '" + input_table + "');");
/**     Add edge weights */
sql("ALTER TABLE " + input_table + ".edges ADD COLUMN " + weight_column + " double;");
sql("UPDATE " + input_table + ".edges SET " + weight_column + "=" + weight_function + ";");

/** 2.  Identify connected components */
/**  A. Label each node by its connected component */
sql("CREATE TABLE connected_components AS SELECT * FROM ST_ConnectedComponents(" + input_table + ".edges);");
/**  B. Count the number of nodes in each connected component */
sql("CREATE TABLE connected_component_totals AS SELECT connected_component, COUNT() AS number_of_vertices FROM connected_components GROUP BY connected_component ORDER BY number_of_vertices DESC;");
/**  C. Recover the nodes of a connected component */
sql("CREATE TABLE " + input_table + "_" + ccNumber + ".nodes AS SELECT a.the_geom, b.id FROM " + input_table + ".nodes a, connected_components b WHERE a.id=b.id and b.connected_component=" + ccNumber + ";");
/**  D. Recover the edges of a connected component */
sql("CREATE TABLE " + input_table + "_" + ccNumber + "_start.edges AS SELECT a.* FROM " + input_table + ".edges a, " + input_table + "_" + ccNumber + ".nodes b WHERE a.start_node=b.id;");
sql("CREATE TABLE " + input_table + "_" + ccNumber + "_end.edges AS SELECT a.* FROM " + input_table + ".edges a, " + input_table + "_" + ccNumber + ".nodes b WHERE a.end_node=b.id;");
sql("CREATE TABLE " + input_table + "_" + ccNumber + ".edges AS SELECT DISTINCT a.* FROM " + input_table + "_" + ccNumber + "_start.edges a, " + input_table + "_" + ccNumber + "_end.edges b WHERE a.id=b.id;");

/** 3.  Do graph analysis */
/**  A. Execute ST_GraphAnalysis */
sql("CREATE TABLE " + input_table + "_" + ccNumber + "_tmp AS SELECT * FROM ST_GraphAnalysis(" + input_table + "_" + ccNumber + ".edges, '" + weight_column + "', " + orientation + ");");
/**  B. Recover the node geometries */
sql("CREATE TABLE " + input_table + "_" + ccNumber + "_weighted.graph_analysis AS SELECT a.the_geom, b.* FROM " + input_table + ".nodes a, " + input_table + "_" + ccNumber + "_tmp b WHERE a.id=b.id;");

/** 4. Clean up */
sql("DROP TABLE " + input_table + "_" + ccNumber + ".nodes PURGE;");
sql("DROP TABLE " + input_table + "_" + ccNumber + "_start.edges PURGE;");
sql("DROP TABLE " + input_table + "_" + ccNumber + "_end.edges PURGE;");
sql("DROP TABLE " + input_table + "_" + ccNumber + ".edges PURGE;");
sql("DROP TABLE " + input_table + "_" + ccNumber + "_tmp PURGE;");