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" <[email protected]>
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" <[email protected]>
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"
<[email protected]>
> 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 [email protected]
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> _______________________________________________
> postgis-users mailing list [email protected]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list [email protected]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
_______________________________________________
postgis-users mailing list [email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list [email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list [email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
