Bob, You need to post your table definition, and your update syntax in order to diagnose the problem.
r.b. -----Original Message----- From: [EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> To: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net> Sent: Wednesday, June 04, 2008 2:19 PM Subject: RE: [postgis-users] Creating a Flow Diagram > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> > To: "PostGIS Users Discussion" <postgis-users@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: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> > > To: "PostGIS Users Discussion" > <postgis-users@postgis.refractions.net> > > Sent: Monday, June 02, 2008 10:06 AM > > Subject: RE: [postgis-users] Creating a Flow Diagram > > > > > > Comments below > > > > > > > -----Original Message----- > > > From: [EMAIL PROTECTED] > > > [mailto:[EMAIL PROTECTED] 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@postgis.refractions.net > > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > _______________________________________________ > > postgis-users mailing list postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > _______________________________________________ > > postgis-users mailing list postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > _______________________________________________ > postgis-users mailing list postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > _______________________________________________ > postgis-users mailing list postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ > postgis-users mailing list postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
