1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| CREATE OR REPLACE FUNCTION map3d_geom2node(table_geom character varying, table_node character varying) RETURNS void AS
$$
DECLARE
row_node_modify record;
row_edge_to_node record;
BEGIN
-- If the column the geom do not exist on table_node, it must be added
IF NOT EXISTS(SELECT the_geom FROM table_node DESC LIMIT 1) THEN
EXECUTE 'SELECT AddGeometryColumn(' || quote_ident() || ', ' || quote_ident(table_node) || ',' || quote_ident(the_geom) || ',' || quote_ident(-1) || ',' || quote_ident(POINT) || ',2);'
END IF;
-- for each table_node row, we get the starting point of the edge
FOR row_node_modify IN EXECUTE 'SELECT * FROM ' || quote_ident(table_from) LOOP
EXECUTE 'SELECT INTO row_edge_to_node startPoint(geometryN(the_geom, 1)) FROM ' || quote_ident(table_geom) || ' WHERE source_id=' || quote_ident(row_node_modify.id) || ' DESC LIMIT 1;'
EXECUTE 'UPDATE table_node SET the_geom=' || quote_ident(SELECT startPoint(row_edge_to_node.the_geom)) || ' WHERE id=' || quote_ident(row_node_modify.id) || ';';
END LOOP;
END;
$$
LANGUAGE plpgsql; |
Partager