Here's the structure.
Create table e
(
Parent_entity_id serial unique not null,
The_Geom geometry,
Description varchar (50)
);
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
);
Update entity
set the_geom = e.the_geom
from e
where entity.entity_id = '8'
and e.parent_entity_id = '2';
CREATE OR REPLACE FUNCTION getchild() RETURNS trigger AS $$
Begin
-- WANNABE trigger function
update entity
SET 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) ) * entity.ratio_x,
( st_ymax(p.the_geom) - st_ymin(p.the_geom) ) * entity.ratio_y,
( st_zmax(p.the_geom) - st_zmin(p.the_geom) ) * entity.ratio_z
)),
is_calculated = '1'
from entity as c
join entity as p
on c.parent_entity_id = p.entity_id
WHERE p.is_calculated = '0'
AND p.entity_id = OLD.entity_id; /* Update from the OLD trigger
record */
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
create trigger getchildren after update on entity
for each row execute procedure getchild();
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
