[postgis-users] Creating a Flow Diagram

[Sufficool, Stanley]

C is an alias to the entity table for child entities, P is an alias to
the entity table for parent entities. 
You have one table with all the entities both parent and child with a
"loop back" using entity_relation.

The same could be accomplished with a single table using:

===========================

/* Your entity/diagram shapes and relationships */
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
);

/* WANNABE trigger function */
update c 
SET c.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) ) * er.ratio_x,
	( st_ymax(p.the_geom) - st_ymin(p.the_geom) ) * er.ratio_y,
	( st_zmax(p.the_geom) - st_zmin(p.the_geom) ) * er.ratio_z
),
is_calculated = 1 
from entity as c
join entity as p
	on c.parent_entity_id = p.entity_id
WHERE is_calculated=0
AND p.entity_id = OLD.entity_id;	/* Update from the OLD trigger
record */

========================

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Bob Pawley
> Sent: Monday, June 02, 2008 10:25 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Creating a Flow Diagram
> 
> 
> When you describe the Update function:
> 
> - I take it that c is a child table which you haven't 
> described. Could this 
> be a library of geometries??
> 
> - Set c.the_geom = ? (Parent geometry??)
> 
> Bob
> 
> 
> 
> ----- Original Message ----- 
> From: "Sufficool, Stanley" <ssufficool at rov.sbcounty.gov>
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Sent: Monday, June 02, 2008 10:06 AM
> Subject: RE: [postgis-users] Creating a Flow Diagram
> 
> 
> Comments below
> 
> 
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of 
> > Bob Pawley
> > Sent: Monday, June 02, 2008 9:30 AM
> > To: PostGIS Users Discussion
> > Subject: [postgis-users] Creating a Flow Diagram
> >
> >
> > Hi Stanley
> >
> > I'm still in the primary learning stage with Postgis.
> >
> > I would appreciate you critiquing my thoughts.
> >
> > With the two tables - entity and entity_relationship - the 
> idea is to 
> > establish a parent geometry (perhaps a point known to be within 
> > established
> > boundries) and establish the position of  child entities ( 
> in my case,
> > process graphics) in relationship to the parent.
> 
> That is correct. The entitity table would contain 2d/3d 
> Polygons of your diagram entities. Points in this table would 
> not work due to st_centroid in the would-be trigger function. 
> A slight modification would fix this.
> 
> >
> > Furthermore, you seem to indicate that any entity can be 
> both a parent
> > and/or a child to another entity.
> >
> 
> Yes, this allows for one to many relations of parent to child 
> (1-->2 &&
> 1-->3) and for network diagrams
> 
> > You also indicate that the placement of the child entity can be 
> > established in relation to the relative geometric sizes of the 
> > entities.
> >
> 
> Yes, examples:
> For a parent entity (1) with a child (2) placed to the right 
> of the parent, you would have entity_relation (1,2,1,0,0) For 
> a parent entity (1) with a child (2) placed to the top of the 
> parent, you would have entity_relation (1,2,0,1,0) For a 
> parent entity (1) with a child (2) placed to the left of the 
> parent, you would have entity_relation (1,2,-1,0,0) *If 
> anyone actually produces a 3d diagram using the ratio_z, this 
> would be a first in my book and would be very interesting to see.
> 
> > Have I been somewhat correct so far?
> >
> > Bob
> >
> > _______________________________________________
> > postgis-users mailing list postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users 
> 
> _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>