[postgis-users] Creating a Flow Diagram

Thu Jun 5 09:44:41 PDT 2008

Bob,
You need to post your table definition, and your update syntax in order to diagnose the problem.

r.b.

-----Original Message-----
From:	postgis-users-bounces at postgis.refractions.net on behalf of Bob Pawley
Sent:	Thu 6/5/2008 12:20 PM
To:	PostGIS Users Discussion
Cc:	
Subject:	Re: [postgis-users] Creating a Flow Diagram

When I make the trigger for table entity an 'after update' I get the 
message, on updating, that the not null constraint is violated.

When I make the trigger a 'before update' and the trigger is enabled the 
update runs its course with zero rows affected. With the trigger disabled 
the update does update one row.

I've also set 'is_calculated' to '1' for the parent row in an attempt to 
stop the recursion that  happened the one time I got some action when both p 
and c is_calculated rows were set to '0'.

Any thoughts would be apprectiated.

Bob

----- Original Message ----- 
From: "Sufficool, Stanley" <ssufficool at rov.sbcounty.gov>
To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
Sent: Wednesday, June 04, 2008 2:19 PM
Subject: RE: [postgis-users] Creating a Flow Diagram

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

First add a starting entity in the table with a NULL parent id
Add any child entities with the parent id set to the first entity

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

These values are floats (decimals). A value of 1 in ratio_x would place
the child entity immediately to the right of the parent (1 * parent
width),
Value of .5 overlapping the parent by half to the right.

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

Depends on how you wrote your trigger. The update expression will fire
on whatever criteria you have in the trigger.

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080605/2ce3e455/attachment.html>