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 [EMAIL PROTECTED] 804-698-4405 Open Source Modeling Tools: http://sourceforge.net/projects/npsource/ -----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? 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" <[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
