select DropGeometryColumn('public', 'houses', 'the_geom');
drop table houses;
create table houses (
oid SERIAL,
house_id integer
);
select AddGeometryColumn('public', 'houses', 'the_geom', 4326, 'POLYGON', 
2);
insert into houses(house_id, the_geom) values (389, 
   SetSRID(GeomFromText('POLYGON((-78.5308456420898 36.7957420349121,
                          -78.5308456420898 36.990234375,
                          -78.2749557495117 36.990234375,
                          -78.2749557495117 36.7957420349121,
                          -78.5308456420898 36.7957420349121))'),4326)
);
insert into houses(house_id, the_geom) values (393, 
   SetSRID(GeomFromText('POLYGON((-78.4844055175781 36.9916496276855,
                          -78.4844055175781 37.1059455871582,
                          -78.2759628295898 37.1059455871582,
                          -78.2759628295898 36.9916496276855,
                          -78.4844055175781 36.9916496276855))'),4326)
);


create table persons ( 
oid SERIAL, 
person_id SERIAL, 
house_id integer,
person_name varchar(32)
);

insert into persons (person_name, house_id) values ('Juan', 393);
insert into persons (person_name, house_id) values ('Stan', 393);
insert into persons (person_name, house_id) values ('Peter', 393);
insert into persons (person_name, house_id) values ('Gloria', 393);
insert into persons (person_name, house_id) values ('Nancy', 393);
insert into persons (person_name, house_id) values ('Gigi', 393);
insert into persons (person_name, house_id) values ('Stephen', 389);
insert into persons (person_name, house_id) values ('Roger', 389);
insert into persons (person_name, house_id) values ('Eloise', 389);


CREATE OR REPLACE VIEW gview_residents AS 
SELECT b.oid,a.house_id, b.thisnum, b.person_name, b.person_id, 
   0.05 * c.base_length * b.thisnum * cos(1.0 * b.thisnum) AS Xpos,
   0.05 * c.base_length * b.thisnum * sin(1.0 * b.thisnum) AS Ypos, 
   setSRID(GeometryFromText ( 'Point(' ||
   X(Centroid(a.the_geom)) + 0.05 * c.base_length * b.thisnum * cos(1.0 * 
b.thisnum) || ' ' ||
   Y(Centroid(a.the_geom)) + 0.05 * c.base_length * b.thisnum * sin(1.0 * 
b.thisnum) || ')'
   ), SRID(a.the_geom)) AS the_geom 
FROM (
-- number of metrics associated with this shape
SELECT a.house_id, a.the_geom, count(b.person_name)::float8 AS totalnum 
FROM houses as a, persons as b 
WHERE a.house_id = b.house_id
GROUP BY a.house_id, a.the_geom
) AS a,
(
-- generate a number for each inidividual entry in the table of metrics 
associated with this shape with a sequence ordering them
SELECT a.oid,a.person_name, a.person_id , a.house_id, 
count(b.person_name)::float8 AS thisnum 
FROM persons as a, persons as b 
WHERE a.house_id = b.house_id
and a.person_name >= b.person_name 
GROUP BY a.oid, a.person_name, a.house_id, a.person_id 
ORDER BY a.house_id, a.person_name 
) AS b, 
(
-- get and estimated appropriate radius by looking at the mean dimension 
of the bounding box and multiplying by 0.5
SELECT a.house_id, 0.15 * (abs(Xmin(a.the_geom) - Xmax(a.the_geom)) + 
abs(Ymin(a.the_geom) - Ymax(a.the_geom))) AS base_length
FROM houses AS a 
) AS c 
WHERE a.house_id = b.house_id and b.house_id = c.house_id;