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

Function: totopogeom

Schema

topology

Owner

albenard

Descriptions

args: geom, topogeom, tolerance - Converts a simple Geometry into a topo geometry

Options

Definition

CREATE OR REPLACE FUNCTION topology.totopogeom (
 ageom public.geometry,
 tg topology.topogeometry,
 atolerance double precision = 0
)
RETURNS topology.topogeometry AS
$span$
DECLARE
 layer_info RECORD;
 topology_info RECORD;
 rec RECORD;
 rec2 RECORD;
 elem TEXT;
 elems TEXT[];
 sql TEXT;
 typ TEXT;
 tolerance FLOAT8;
 alayer INT;
 atopology TEXT;
BEGIN


 -- Get topology information
 SELECT id, name FROM topology.topology
   INTO topology_info
   WHERE id = topology_id(tg);
 IF NOT FOUND THEN
   RAISE EXCEPTION 'No topology with id "%" in topology.topology',
                   topology_id(tg);
 END IF;

 alayer := layer_id(tg);
 atopology := topology_info.name;


 -- Get tolerance, if 0 was given
 tolerance := COALESCE( NULLIF(atolerance, 0), topology._st_mintolerance(topology_info.name, ageom) );

 -- Get layer information
 BEGIN
   SELECT *, CASE
     WHEN feature_type = 1 THEN 'puntal'
     WHEN feature_type = 2 THEN 'lineal'
     WHEN feature_type = 3 THEN 'areal'
     WHEN feature_type = 4 THEN 'mixed'
     ELSE 'unexpected_'||feature_type
     END as typename
   FROM topology.layer l
     INTO STRICT layer_info
     WHERE l.layer_id = layer_id(tg)
     AND l.topology_id = topology_info.id;
 EXCEPTION
   WHEN NO_DATA_FOUND THEN
     RAISE EXCEPTION 'No layer with id "%" in topology "%"',
       alayer, atopology;
 END;

 -- Can't convert to a hierarchical topogeometry
 IF layer_info.level > 0 THEN
     RAISE EXCEPTION 'Layer "%" of topology "%" is hierarchical, cannot convert a simple geometry to it.',
       alayer, atopology;
 END IF;


 --
 -- Check type compatibility and set TopoGeometry type
 -- 1:puntal, 2:lineal, 3:areal, 4:collection
 --
 typ = geometrytype(ageom);
 IF typ = 'GEOMETRYCOLLECTION' THEN
   --  A collection can only go to collection layer
   IF layer_info.feature_type != 4 THEN
     RAISE EXCEPTION
       'Layer "%" of topology "%" is %, cannot hold a collection feature.',
       layer_info.layer_id, topology_info.name, layer_info.typename;
   END IF;
   tg.type := 4;
 ELSIF typ = 'POINT' OR typ = 'MULTIPOINT' THEN -- puntal
   --  A point can go in puntal or collection layer
   IF layer_info.feature_type != 4 and layer_info.feature_type != 1 THEN
     RAISE EXCEPTION
       'Layer "%" of topology "%" is %, cannot hold a puntal feature.',
       layer_info.layer_id, topology_info.name, layer_info.typename;
   END IF;
   tg.type := 1;
 ELSIF typ = 'LINESTRING' or typ = 'MULTILINESTRING' THEN -- lineal
   --  A line can go in lineal or collection layer
   IF layer_info.feature_type != 4 and layer_info.feature_type != 2 THEN
     RAISE EXCEPTION
       'Layer "%" of topology "%" is %, cannot hold a lineal feature.',
       layer_info.layer_id, topology_info.name, layer_info.typename;
   END IF;
   tg.type := 2;
 ELSIF typ = 'POLYGON' OR typ = 'MULTIPOLYGON' THEN -- areal
   --  An area can go in areal or collection layer
   IF layer_info.feature_type != 4 and layer_info.feature_type != 3 THEN
     RAISE EXCEPTION
       'Layer "%" of topology "%" is %, cannot hold an areal feature.',
       layer_info.layer_id, topology_info.name, layer_info.typename;
   END IF;
   tg.type := 3;
 ELSE
     -- Should never happen
     RAISE EXCEPTION
       'Unexpected feature dimension %', ST_Dimension(ageom);
 END IF;

 -- Now that we have an empty topogeometry, we loop over distinct components
 -- and add them to the definition of it. We add them as soon
 -- as possible so that each element can further edit the
 -- definition by splitting
 FOR rec IN SELECT id(tg), alayer as lyr,
   geom, ST_Dimension(geom) as dims
   FROM (SELECT (ST_Dump(ageom)).geom) as f
   WHERE NOT ST_IsEmpty(geom)
 LOOP
   FOR rec2 IN SELECT CASE
      WHEN rec.dims = 0 THEN
        topology.topogeo_addPoint(atopology, rec.geom, tolerance)
      WHEN rec.dims = 1 THEN
        topology.topogeo_addLineString(atopology, rec.geom, tolerance)
      WHEN rec.dims = 2 THEN
        topology.topogeo_addPolygon(atopology, rec.geom, tolerance)
      END as primitive
   LOOP
     elem := ARRAY[rec.dims+1, rec2.primitive]::text;
     IF elems @> ARRAY[elem] THEN
     ELSE
       elems := elems || elem;
       -- TODO: consider use a single INSERT statement for the whole thing
       sql := 'INSERT INTO ' || quote_ident(atopology)
           || '.relation(topogeo_id, layer_id, element_type, element_id) VALUES ('
           || rec.id || ',' || rec.lyr || ',' || rec.dims+1
           || ',' || rec2.primitive || ')'
           -- NOTE: we're avoiding duplicated rows here
           || ' EXCEPT SELECT ' || rec.id || ', ' || rec.lyr
           || ', element_type, element_id FROM '
           || quote_ident(topology_info.name)
           || '.relation WHERE layer_id = ' || rec.lyr
           || ' AND topogeo_id = ' || rec.id;
       EXECUTE sql;
     END IF;
   END LOOP;
 END LOOP;

 RETURN tg;

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

COMMENT ON FUNCTION topology.totopogeom(ageom public.geometry, tg topology.topogeometry, atolerance double precision)
IS 'args: geom, topogeom, tolerance - Converts a simple Geometry into a topo geometry';

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