<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=Windows-1252">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.0.6619.12">
<TITLE>RE: [postgis-users] Creating a Flow Diagram</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Bob,<BR>
You need to post your table definition, and your update syntax in order to diagnose the problem.<BR>
<BR>
r.b.<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of Bob Pawley<BR>
Sent: Thu 6/5/2008 12:20 PM<BR>
To: PostGIS Users Discussion<BR>
Cc: <BR>
Subject: Re: [postgis-users] Creating a Flow Diagram<BR>
<BR>
When I make the trigger for table entity an 'after update' I get the<BR>
message, on updating, that the not null constraint is violated.<BR>
<BR>
When I make the trigger a 'before update' and the trigger is enabled the<BR>
update runs its course with zero rows affected. With the trigger disabled<BR>
the update does update one row.<BR>
<BR>
I've also set 'is_calculated' to '1' for the parent row in an attempt to<BR>
stop the recursion that happened the one time I got some action when both p<BR>
and c is_calculated rows were set to '0'.<BR>
<BR>
Any thoughts would be apprectiated.<BR>
<BR>
Bob<BR>
<BR>
----- Original Message -----<BR>
From: "Sufficool, Stanley" <ssufficool@rov.sbcounty.gov><BR>
To: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net><BR>
Sent: Wednesday, June 04, 2008 2:19 PM<BR>
Subject: RE: [postgis-users] Creating a Flow Diagram<BR>
<BR>
<BR>
> -----Original Message-----<BR>
> From: postgis-users-bounces@postgis.refractions.net<BR>
> [<A HREF="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On<BR>
> Behalf Of Bob Pawley<BR>
> Sent: Wednesday, June 04, 2008 2:07 PM<BR>
> To: PostGIS Users Discussion<BR>
> Subject: Re: [postgis-users] Creating a Flow Diagram<BR>
><BR>
> Hi Stanley<BR>
><BR>
> I have created the single table and modified the trigger to<BR>
> the point that<BR>
> the trigger goes through its paces without error even though<BR>
> it doesn't yet<BR>
> accomplish anything.<BR>
><BR>
> I'm hoping you can shed some light on a couple of points.<BR>
><BR>
> 1 - What is the column parent_entity_id expecting. I've<BR>
> attempted to add an<BR>
> id (directly and an id update from another table) and keep<BR>
> getting a foreign<BR>
> key violation?<BR>
<BR>
First add a starting entity in the table with a NULL parent id<BR>
Add any child entities with the parent id set to the first entity<BR>
<BR>
><BR>
> 2 - What form do the values in the ratio columns take? Are<BR>
> they simple<BR>
> integers? decimals?<BR>
<BR>
These values are floats (decimals). A value of 1 in ratio_x would place<BR>
the child entity immediately to the right of the parent (1 * parent<BR>
width),<BR>
Value of .5 overlapping the parent by half to the right.<BR>
<BR>
><BR>
> 3 - Is the update that triggers the trigger function an<BR>
> update on the_geom<BR>
> column?<BR>
<BR>
Depends on how you wrote your trigger. The update expression will fire<BR>
on whatever criteria you have in the trigger.<BR>
<BR>
><BR>
> Bob<BR>
><BR>
><BR>
><BR>
><BR>
> ----- Original Message -----<BR>
> From: "Sufficool, Stanley" <ssufficool@rov.sbcounty.gov><BR>
> To: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net><BR>
> Sent: Monday, June 02, 2008 11:05 AM<BR>
> Subject: RE: [postgis-users] Creating a Flow Diagram<BR>
><BR>
><BR>
> C is an alias to the entity table for child entities, P is an<BR>
> alias to the entity table for parent entities. You have one<BR>
> table with all the entities both parent and child with a<BR>
> "loop back" using entity_relation.<BR>
><BR>
> The same could be accomplished with a single table using:<BR>
><BR>
> ===========================<BR>
><BR>
> /* Your entity/diagram shapes and relationships */<BR>
> create table entity (<BR>
> entity_id serial not null primary key,<BR>
> the_geom geometry not null,<BR>
> is_calculated bit not null default 0::bit,<BR>
> parent_entity_id int null references entity(entity_id) on<BR>
> delete set null, ratio_x float4 not null default 0, ratio_y<BR>
> float4 not null default 0, ratio_z float4 not null default 0 );<BR>
><BR>
> /* WANNABE trigger function */<BR>
> update c<BR>
> SET c.the_geom =<BR>
> select st_translate(<BR>
> /* Step 1: Center the child on parent geometry */<BR>
> st_translate(<BR>
> c.the_geom,<BR>
> st_x(st_centroid(p.the_geom)) -<BR>
> st_x(st_centroid(c.the_geom)),<BR>
> st_y(st_centroid(p.the_geom)) -<BR>
> st_y(st_centroid(c.the_geom)),<BR>
> st_z(st_centroid(p.the_geom)) -<BR>
> st_z(st_centroid(c.the_geom))<BR>
> },<BR>
> /*<BR>
> Step 2: Translate to the left, right, top, bottom,<BR>
> front, back<BR>
> of the parent geometry by the ratio (x,y,z)<BR>
> stored in entity_relation of the parent entity<BR>
> width/height<BR>
> */<BR>
> ( st_xmax(p.the_geom) - st_xmin(p.the_geom) ) * er.ratio_x,<BR>
> ( st_ymax(p.the_geom) - st_ymin(p.the_geom) ) * er.ratio_y,<BR>
> ( st_zmax(p.the_geom) - st_zmin(p.the_geom) ) * er.ratio_z<BR>
> ),<BR>
> is_calculated = 1<BR>
> from entity as c<BR>
> join entity as p<BR>
> on c.parent_entity_id = p.entity_id<BR>
> WHERE is_calculated=0<BR>
> AND p.entity_id = OLD.entity_id; /* Update from the OLD<BR>
> trigger record */<BR>
><BR>
> ========================<BR>
><BR>
> > -----Original Message-----<BR>
> > From: postgis-users-bounces@postgis.refractions.net<BR>
> > [<A HREF="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On Behalf Of<BR>
> > Bob Pawley<BR>
> > Sent: Monday, June 02, 2008 10:25 AM<BR>
> > To: PostGIS Users Discussion<BR>
> > Subject: Re: [postgis-users] Creating a Flow Diagram<BR>
> ><BR>
> ><BR>
> > When you describe the Update function:<BR>
> ><BR>
> > - I take it that c is a child table which you haven't<BR>
> described. Could<BR>
> > this be a library of geometries??<BR>
> ><BR>
> > - Set c.the_geom = ? (Parent geometry??)<BR>
> ><BR>
> > Bob<BR>
> ><BR>
> ><BR>
> ><BR>
> > ----- Original Message -----<BR>
> > From: "Sufficool, Stanley" <ssufficool@rov.sbcounty.gov><BR>
> > To: "PostGIS Users Discussion"<BR>
> <postgis-users@postgis.refractions.net><BR>
> > Sent: Monday, June 02, 2008 10:06 AM<BR>
> > Subject: RE: [postgis-users] Creating a Flow Diagram<BR>
> ><BR>
> ><BR>
> > Comments below<BR>
> ><BR>
> ><BR>
> > > -----Original Message-----<BR>
> > > From: postgis-users-bounces@postgis.refractions.net<BR>
> > > [<A HREF="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On<BR>
> Behalf Of<BR>
> > > Bob Pawley<BR>
> > > Sent: Monday, June 02, 2008 9:30 AM<BR>
> > > To: PostGIS Users Discussion<BR>
> > > Subject: [postgis-users] Creating a Flow Diagram<BR>
> > ><BR>
> > ><BR>
> > > Hi Stanley<BR>
> > ><BR>
> > > I'm still in the primary learning stage with Postgis.<BR>
> > ><BR>
> > > I would appreciate you critiquing my thoughts.<BR>
> > ><BR>
> > > With the two tables - entity and entity_relationship - the<BR>
> > idea is to<BR>
> > > establish a parent geometry (perhaps a point known to be within<BR>
> > > established<BR>
> > > boundries) and establish the position of child entities (<BR>
> > in my case,<BR>
> > > process graphics) in relationship to the parent.<BR>
> ><BR>
> > That is correct. The entitity table would contain 2d/3d Polygons of<BR>
> > your diagram entities. Points in this table would not work due to<BR>
> > st_centroid in the would-be trigger function. A slight modification<BR>
> > would fix this.<BR>
> ><BR>
> > ><BR>
> > > Furthermore, you seem to indicate that any entity can be<BR>
> > both a parent<BR>
> > > and/or a child to another entity.<BR>
> > ><BR>
> ><BR>
> > Yes, this allows for one to many relations of parent to<BR>
> child (1-->2<BR>
> > &&<BR>
> > 1-->3) and for network diagrams<BR>
> ><BR>
> > > You also indicate that the placement of the child entity can be<BR>
> > > established in relation to the relative geometric sizes of the<BR>
> > > entities.<BR>
> > ><BR>
> ><BR>
> > Yes, examples:<BR>
> > For a parent entity (1) with a child (2) placed to the right of the<BR>
> > parent, you would have entity_relation (1,2,1,0,0) For a<BR>
> parent entity<BR>
> > (1) with a child (2) placed to the top of the parent, you<BR>
> would have<BR>
> > entity_relation (1,2,0,1,0) For a parent entity (1) with a<BR>
> child (2)<BR>
> > placed to the left of the parent, you would have entity_relation<BR>
> > (1,2,-1,0,0) *If anyone actually produces a 3d diagram using the<BR>
> > ratio_z, this would be a first in my book and would be very<BR>
> > interesting to see.<BR>
> ><BR>
> > > Have I been somewhat correct so far?<BR>
> > ><BR>
> > > Bob<BR>
> > ><BR>
> > > _______________________________________________<BR>
> > > postgis-users mailing list postgis-users@postgis.refractions.net<BR>
> > > <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
> > ><BR>
> > _______________________________________________<BR>
> > postgis-users mailing list postgis-users@postgis.refractions.net<BR>
> > <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
> ><BR>
> > _______________________________________________<BR>
> > postgis-users mailing list postgis-users@postgis.refractions.net<BR>
> > <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
> ><BR>
> _______________________________________________<BR>
> postgis-users mailing list postgis-users@postgis.refractions.net<BR>
> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
><BR>
> _______________________________________________<BR>
> postgis-users mailing list postgis-users@postgis.refractions.net<BR>
> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
> _______________________________________________<BR>
> postgis-users mailing list postgis-users@postgis.refractions.net<BR>
> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
><BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>