[postgis-users] Creating a Flow Diagram

[Bob Pawley]

Here's the structure.

Create table e
 (
 Parent_entity_id serial unique not null,
 The_Geom geometry,
 Description varchar (50)
 );

create table entity (
entity_id serial not null primary key, 
the_geom geometry not null, 
is_calculated bit not null default 0::bit,
parent_entity_id int null references entity(entity_id) on delete
set null, 
ratio_x float4 not null default 0, 
ratio_y float4 not null default 0, 
ratio_z float4 not null default 0
);

 Update entity
 set the_geom = e.the_geom
 from  e
 where entity.entity_id = '8'
 and e.parent_entity_id = '2';


 CREATE OR REPLACE FUNCTION getchild() RETURNS trigger AS $$
 Begin
-- WANNABE trigger function
 update entity
 SET the_geom = 
 (select st_translate(
 /* Step 1: Center the child on parent geometry */
 st_translate( 
 c.the_geom, 
 st_x(st_centroid(p.the_geom)) -
 st_x(st_centroid(c.the_geom)),  
 st_y(st_centroid(p.the_geom)) -
 st_y(st_centroid(c.the_geom)),
 st_z(st_centroid(p.the_geom)) -
 st_z(st_centroid(c.the_geom)) 
 ),
 /* 
 Step 2: Translate to the left, right, top, bottom,
 front, back 
 of the parent geometry by the ratio (x,y,z)  
 stored in entity_relation of the parent entity
 width/height 
 */
 ( st_xmax(p.the_geom) - st_xmin(p.the_geom) ) * entity.ratio_x,
 ( st_ymax(p.the_geom) - st_ymin(p.the_geom) ) * entity.ratio_y,
 ( st_zmax(p.the_geom) - st_zmin(p.the_geom) ) * entity.ratio_z
 )),
 is_calculated = '1' 
 from entity as c
 join entity as p
 on c.parent_entity_id = p.entity_id
 WHERE p.is_calculated = '0'
 AND p.entity_id = OLD.entity_id; /* Update from the OLD trigger
 record */
 
 RETURN NULL;
 END;
 $$ LANGUAGE plpgsql;

 create trigger getchildren after update on entity
 for each row execute procedure getchild();