[postgis-users] Creating a Flow Diagram

Wed Jun 4 11:22:45 PDT 2008

Bob,
In his example, Stanley's table expects the parent_entity_id column to
contain a reference to an "entity_id" value from some other object in
the "entity" table.  Thus, if you are inserting a parent object, or a
parentless object, you put nothing, or NULL in that column (if you put
nothing, it automatically puts in a NULL as per Stanleys table
definition rules).  If you want to join a parent and child entity, you
would put some valid entity_id into the parent_entity_id column for the
child object.

HTH,
r.b.

Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
rwburgholzer at deq.virginia.gov
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Bob
Pawley
Sent: Wednesday, June 04, 2008 2:07 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Creating a Flow Diagram

Hi Stanley

I have created the single table and modified the trigger to the point
that 
the trigger goes through its paces without error even though it doesn't
yet 
accomplish anything.

I'm hoping you can shed some light on a couple of points.

1 - What is the column parent_entity_id expecting. I've attempted to add
an 
id (directly and an id update from another table) and keep getting a
foreign 
key violation?

2 - What form do the values in the ratio columns take? Are they simple 
integers? decimals?

3 - Is the update that triggers the trigger function an update on
the_geom 
column?

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 11:05 AM
Subject: RE: [postgis-users] Creating a Flow Diagram

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
>
_______________________________________________
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