pggeodb.nancy.inra.fr/db_robot - db_robot on pggeodb.nancy.inra.fr

Function: st_createtopogeo

Schema

topology

Owner

albenard

Descriptions

args: atopology, acollection - Adds a collection of geometries to a given empty topology and returns a message detailing success.

Options

Definition

CREATE OR REPLACE FUNCTION topology.st_createtopogeo (
 atopology varchar,
 acollection public.geometry
)
RETURNS text AS
$span$
DECLARE
 typ char(4);
 rec RECORD;
 ret int;
 nodededges GEOMETRY;
 points GEOMETRY;
 snode_id int;
 enode_id int;
 tolerance FLOAT8;
 topoinfo RECORD;
BEGIN

 IF atopology IS NULL OR acollection IS NULL THEN
   RAISE EXCEPTION 'SQL/MM Spatial exception - null argument';
 END IF;

 -- Get topology information
 BEGIN
   SELECT * FROM topology.topology
     INTO STRICT topoinfo WHERE name = atopology;
 EXCEPTION
   WHEN NO_DATA_FOUND THEN
     RAISE EXCEPTION 'SQL/MM Spatial exception - invalid topology name';
 END;

 -- Check SRID compatibility
 IF ST_SRID(acollection) != topoinfo.SRID THEN
   RAISE EXCEPTION 'Geometry SRID (%) does not match topology SRID (%)',
     ST_SRID(acollection), topoinfo.SRID;
 END IF;

 -- Verify pre-conditions (valid, empty topology schema exists)
 BEGIN -- {

   -- Verify the topology views in the topology schema to be empty
   FOR rec in EXECUTE
     'SELECT count(*) FROM '
     || quote_ident(atopology) || '.edge_data '
     || ' UNION ' ||
     'SELECT count(*) FROM '
     || quote_ident(atopology) || '.node '
   LOOP
     IF rec.count > 0 THEN
   RAISE EXCEPTION 'SQL/MM Spatial exception - non-empty view';
     END IF;
   END LOOP;

   -- face check is separated as it will contain a single (world)
   -- face record
   FOR rec in EXECUTE
     'SELECT count(*) FROM '
     || quote_ident(atopology) || '.face '
   LOOP
     IF rec.count != 1 THEN
   RAISE EXCEPTION 'SQL/MM Spatial exception - non-empty face view';
     END IF;
   END LOOP;

 EXCEPTION
   WHEN INVALID_SCHEMA_NAME THEN
     RAISE EXCEPTION 'SQL/MM Spatial exception - invalid topology name';
   WHEN UNDEFINED_TABLE THEN
     RAISE EXCEPTION 'SQL/MM Spatial exception - non-existent view';

 END; -- }


 --
 -- Node input linework with itself
 --
 WITH components AS ( SELECT geom FROM ST_Dump(acollection) )
 SELECT ST_UnaryUnion(ST_Collect(geom)) FROM (
   SELECT geom FROM components
     WHERE ST_Dimension(geom) = 1
   UNION ALL
   SELECT ST_Boundary(geom) FROM components
     WHERE ST_Dimension(geom) = 2
 ) as linework INTO STRICT nodededges;


 --
 -- Linemerge the resulting edges, to reduce the working set
 -- NOTE: this is more of a workaround for GEOS splitting overlapping
 --       lines to each of the segments.
 --
 SELECT ST_LineMerge(nodededges) INTO STRICT nodededges;



 --
 -- Collect input points and input lines endpoints
 --
 WITH components AS ( SELECT geom FROM ST_Dump(acollection) )
 SELECT ST_Union(geom) FROM (
   SELECT geom FROM components
     WHERE ST_Dimension(geom) = 0
   UNION ALL
   SELECT ST_Boundary(geom) FROM components
     WHERE ST_Dimension(geom) = 1
 ) as nodes INTO STRICT points;


 --
 -- Further split edges by points
 -- TODO: optimize this adding ST_Split support for multiline/multipoint
 --
 FOR rec IN SELECT geom FROM ST_Dump(points)
 LOOP
   -- Use the node to split edges
   SELECT ST_Collect(geom)
   FROM ST_Dump(ST_Split(nodededges, rec.geom))
   INTO STRICT nodededges;
 END LOOP;
 SELECT ST_UnaryUnion(nodededges) INTO STRICT nodededges;


 --
 -- Collect all nodes (from points and noded linework endpoints)
 --

 WITH edges AS ( SELECT geom FROM ST_Dump(nodededges) )
 SELECT ST_Union( -- TODO: ST_UnaryUnion ?
         COALESCE(ST_UnaryUnion(ST_Collect(geom)),
           ST_SetSRID('POINT EMPTY'::geometry, topoinfo.SRID)),
         COALESCE(points,
           ST_SetSRID('POINT EMPTY'::geometry, topoinfo.SRID))
        )
 FROM (
   SELECT ST_StartPoint(geom) as geom FROM edges
     UNION ALL
   SELECT ST_EndPoint(geom) FROM edges
 ) as endpoints INTO points;


 --
 -- Add all nodes as isolated so that
 -- later calls to AddEdgeModFace will tweak their being
 -- isolated or not...
 --
 FOR rec IN SELECT geom FROM ST_Dump(points)
 LOOP
   PERFORM topology.ST_AddIsoNode(atopology, 0, rec.geom);
 END LOOP;


 FOR rec IN SELECT geom FROM ST_Dump(nodededges)
 LOOP
   SELECT topology.GetNodeByPoint(atopology, st_startpoint(rec.geom), 0)
     INTO STRICT snode_id;
   SELECT topology.GetNodeByPoint(atopology, st_endpoint(rec.geom), 0)
     INTO STRICT enode_id;
   PERFORM topology.ST_AddEdgeModFace(atopology, snode_id, enode_id, rec.geom);
 END LOOP;

 RETURN 'Topology ' || atopology || ' populated';

END
$span$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

COMMENT ON FUNCTION topology.st_createtopogeo(atopology varchar, acollection public.geometry)
IS 'args: atopology, acollection - Adds a collection of geometries to a given empty topology and returns a message detailing success.';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 26/02/2014 11:51