Parameter (Input/Output)  Description 
geomtable  A table variable containing identifiers and geometries (i.e. polygons that tessellate the plane) 
bbox  The bounding box to apply to the tessellation.

vertices  Output vertices 
edges  Output edges 
CREATE OR REPLACE PROCEDURE GraphFromPolygons(
IN geomtable TABLE (ID INTEGER, SHAPE ST_GEOMETRY),
IN bbox ST_GEOMETRY,
OUT vertices TABLE (ID Integer, GEOM ST_GEOMETRY),
OUT edges TABLE (ID INTEGER, SRC_ID Integer, SRC_GEOM ST_GEOMETRY, SNK_ID Integer, SNK_GEOM ST_GEOMETRY, DISTANCE DOUBLE)
) AS
BEGIN
 assumptions:
 * input is a table with polygons exclusively
 * plane is tessalated by given polygons
 * uncertainties are covered by the grid snapping of the srs
 * no new points will be introduced (e.g. overlap of polygons)
DECLARE i INTEGER;
DECLARE n INTEGER := 0;
exteriorrings = SELECT id, shape.st_exteriorring() AS extring FROM :geomtable;
 max iterations = max number of points per polygon
SELECT MAX(extring.st_numpoints()) INTO n FROM :exteriorrings;
 gather all edges
FOR i IN 2..n DO
 select all edges from i1 to i, if existent
edges_forth =
SELECT
1 AS ID,
1 AS SRC_ID,
extring.st_pointn(:i1) AS SRC_GEOM,
1 AS SNK_ID,
extring.st_pointn(:i) AS SNK_GEOM,
1 AS DISTANCE
FROM :exteriorrings
WHERE extring.st_pointn(:i) IS NOT NULL;
 select the reverse edges, since we want to construct an undirected graph
edges_back =
SELECT
1 AS ID,
1 AS SRC_ID,
extring.st_pointn(:i) AS SRC_GEOM,
1 AS SNK_ID,
extring.st_pointn(:i1) AS SNK_GEOM,
1 AS DISTANCE
FROM :exteriorrings
WHERE extring.st_pointn(:i) IS NOT NULL;
 note that union will make the edges distinct
edges =
(SELECT * FROM :edges)
UNION
(SELECT * FROM :edges_back)
UNION
(SELECT * FROM :edges_forth);
END FOR;
IF bbox IS NOT NULL
THEN
edges = SELECT * FROM :edges WHERE src_geom.st_within(:bbox) > 0 or snk_geom.st_within(:bbox) > 0;
END IF;
vertices = SELECT ROW_NUMBER() OVER () AS id, geom FROM (SELECT DISTINCT src_geom AS geom FROM :edges);
edges =
SELECT ROW_NUMBER() OVER () AS id, v_src.id AS src_id, e.src_geom AS src_geom, v_snk.id AS snk_id, e.snk_geom AS snk_geom, e.src_geom.st_distance(e.snk_geom) AS distance
FROM :edges e
LEFT JOIN :vertices v_src ON v_src.geom = e.src_geom
LEFT JOIN :vertices v_snk ON v_snk.geom = e.snk_geom;
END;
CALL GRAPHFROMPOLYGONS(:somedata, NULL, geomvertices, geomedges);
vertices = SELECT ID FROM :geomvertices;
edges = SELECT ID, SRC_ID, SNK_ID, DISTANCE FROM :geomedges;
GRAPH g = Graph(:edges, "SRC_ID", "SNK_ID", "ID", :vertices, "ID");
VERTEX v_src = Vertex(:g, :src);
VERTEX v_snk = Vertex(:g, :snk);
WeightedPath<DOUBLE> p = Shortest_Path(:g, :v_src, :v_snk, (Edge e) => DOUBLE{ RETURN :e.DISTANCE; });
distance = WEIGHT(:p);
Find Your Path – With SAP HANA Graph
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User  Count 

14  
11  
11  
10  
9  
8  
8  
7  
7  
7 