<!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 with PostGIS</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Stanley,<BR>
Do I read this right that you have a facility in here to scale the child object based on some factor (ratio_x)?<BR>
<BR>
r.b.<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of Sufficool, Stanley<BR>
Sent: Mon 6/2/2008 10:53 AM<BR>
To: PostGIS Users Discussion<BR>
Cc: <BR>
Subject: RE: [postgis-users] Creating a Flow Diagram with PostGIS<BR>
<BR>
Here's my possibly buggy 2 cents:<BR>
<BR>
This stores all geometry as geometry, not x,y attribute values. The<BR>
intent is to allow moving entities and have the database handle the<BR>
updates to related entities.<BR>
<BR>
=========================================<BR>
<BR>
create table entity(<BR>
entity_id serial not null primary key,<BR>
the_geom geometry not null,<BR>
is_calculated bit not null<BR>
);<BR>
<BR>
-- Your Entity to Entity relationships with offset multipliers for<BR>
-- placement relative to the parent entity extents and position which<BR>
-- is also relative to it's parent level entity position<BR>
create table entity_relation (<BR>
parent_entity_id int not null,<BR>
child_entity_id int not null,<BR>
ratio_x float4 not null,<BR>
ratio_y float4 not null,<BR>
ratio_z float4 not null,<BR>
primary key(parent_entity_id,child_entity_id )<BR>
);<BR>
<BR>
<BR>
/*<BR>
I havent done triggers, but this should be a trigger function on<BR>
UPDATE of parent geomentry<BR>
<BR>
PURPOSE: placement of child entities in relation to the parent<BR>
entity,<BR>
Watch for Recursion!! 1-->2-->3-->1<BR>
Step 1: Center child on parent<BR>
Step 2: Offset by er.offset_N of parent extents<BR>
Step 3: Mark child as updated to prevent recursion<BR>
*/<BR>
update c<BR>
SET c.the_geom =<BR>
select st_translate(<BR>
-- 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>
--Translate to the left, right, top, bottom, front, back of the<BR>
parent geometry by the ratio (x,y,x) stored in entity_relation of the<BR>
parent entity width/height<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_relation as er<BR>
on c.entity_id = er.child_entity_id<BR>
join entity as p<BR>
on er.parent_entity_id = p.entity_id<BR>
WHERE is_calculated=0 --To prevent recursive updates<BR>
AND p.entity_id = OLD.entity_id --Update from the OLD trigger record<BR>
<BR>
<BR>
<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 Bob<BR>
Pawley<BR>
Sent: Sunday, June 01, 2008 2:17 PM<BR>
To: PostGIS Users Discussion<BR>
Subject: Re: [postgis-users] Creating a Flow Diagram with PostGIS<BR>
<BR>
<BR>
Hi Robert<BR>
<BR>
I managed to get the function working - in part.<BR>
<BR>
UPDATE graphics.process_dgm<BR>
Set the_geom = translate(the_geom, (x1 - st_x(the_geom)), (y1 -<BR>
st_y(the_geom)))<BR>
where graphics.process_dgm.id = '178'<BR>
and graphics.process_dgm.id = '181';<BR>
<BR>
This will move a point from one location to another depending on what I<BR>
insert into the x and y column.<BR>
<BR>
But when I attempt to move the geometry that I want moved I get a<BR>
message<BR>
"Argument to X() must be a point".<BR>
<BR>
What am I doing wrong??<BR>
<BR>
Bob<BR>
<BR>
<BR>
<BR>
<BR>
----- Original Message -----<BR>
From: "Burgholzer,Robert" <rwburgholzer@deq.virginia.gov><BR>
To: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net><BR>
Sent: Friday, May 30, 2008 11:54 AM<BR>
Subject: [postgis-users] Creating a Flow Diagram with PostGIS<BR>
<BR>
<BR>
Bob,<BR>
I am taking this online, since it is relevant to PostGIS, and I want to<BR>
make<BR>
sure that others review my comments for veracity<BR>
<BR>
Original Question:<BR>
> At the moment I am importing dxf files, representing process and<BR>
> devices, into Postgis.<BR>
><BR>
> Say I want to make two processes A & B.<BR>
><BR>
> I import the DXF graphic representing A and B into Postgis.<BR>
><BR>
> I want B to be the first process and the output of B goes to A which<BR>
> is situated to the right of or below B.<BR>
><BR>
> Using ST_Translate I need to know the distance in both x and y from<BR>
> the library to where I want to place the images and plug thos values<BR>
> into the Transform function. Perhaps there is a method of building a<BR>
> function to do this?<BR>
><BR>
> Bob<BR>
><BR>
><BR>
<BR>
My response:<BR>
You do not want to use transform for the location. It has nothing to do<BR>
<BR>
with the location, only to the projection, i.e., spatial coordinate<BR>
system.<BR>
You DO want translate for location, however. Transform might be useful<BR>
if<BR>
your objects are not imported from a standard library that you generate,<BR>
or<BR>
are not in the projection that you wish to use for your interface.<BR>
<BR>
CASE 1 (Objects are in same projection as your "Workspace"):<BR>
In this case, we don't really have to "know" where the starting location<BR>
is,<BR>
since we have functions that can derive this. Also, since our shapes<BR>
are in<BR>
the same projection as the workspace, we only need the translate<BR>
function.<BR>
For this example, we are storing our components in a table called<BR>
"widget_table", and the geometry column is "the_geom". Let's assume<BR>
that<BR>
your original shape location coordinates are (x0, y0, z0), which can be<BR>
obtained from the geometry column by using the function st_x, st_y, and<BR>
st_z, and you want to move them to be located at (x1,y1,z1). For this,<BR>
the<BR>
following translate call would work:<BR>
<BR>
UPDATE widget_table SET the_geom = translate(the_geom, (x1 -<BR>
st_x(the_geom)), (y1 - st_y(the_geom)), (z1 - st_z(the_geom))) ;<BR>
<BR>
CASE 2 (you are importing user defined shapes, or shapes in disparate<BR>
projections):<BR>
In this case you WOULD need transform(), to take them from whatever<BR>
their<BR>
source projection is, into whatever their base projection is, as follows<BR>
<BR>
(assuming that the workspace coordinate system is decimal degrees):<BR>
<BR>
UPDATE widget_table SET the_geom = transform(the_geom, 4326);<BR>
<BR>
Then, you would need to relocate them to some other point by using the<BR>
function above. That function could be encapsulated into its own PG<BR>
function of course, something like relocate(x1,y1,z1) which would hide<BR>
all<BR>
of the calls to st_x,y and z.<BR>
<BR>
<BR>
The catch of course, is that it is essential that we KNOW the projection<BR>
of<BR>
our shapes before importing them. That is actually the real sticky part<BR>
<BR>
here, not the movement of them.<BR>
<BR>
HTH,<BR>
r.b.<BR>
<BR>
<BR>
Quoting Bob Pawley <rjpawley@shaw.ca>:<BR>
<BR>
> Robert<BR>
><BR>
<BR>
<BR>
<BR>
<BR>
------------------------------------------------------------------------<BR>
--------<BR>
<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>
_______________________________________________<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<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>