If I understand you correctly, then I think the best way is to collapse the
list of family members into one field using an aggregate glue function that
will glue all the names together in a single field.
Below is a SUM aggregate function we commonly use. You may want to change
the name sum to something else like group_agg
CREATE OR REPLACE FUNCTION catenate(text, text)
RETURNS text AS
$BODY$
SELECT COALESCE($1 || $2,$1,$2,NULL)
$BODY$
LANGUAGE 'sql' IMMUTABLE;
CREATE AGGREGATE sum(text) (
SFUNC=catenate,
STYPE=text
);
Then create view;
CREATE VIEW vwhouseholdind AS
SELECT hh.gid, hh.the_geom, SUM(i.first_name || ' ' || i.last_name ||
E'\r\n') ) as familymembers
FROM households hh LEFT JOIN
(SELECT household_id, first_name, last_name
FROM individuals ORDER BY household_id, last_name, first_name)
i
ON hh.house_holdid = i.household_id
GROUP BY hh.household_id;
Then just use the view in Quantum GIS. All the family members will show in
the familymembers column broken out by carriage returns.
Hope that helps,
Regina
_____
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Anand
Akmanchi
Sent: Wednesday, April 30, 2008 2:23 AM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] one to many join
Hi people
has anyone in the list tried a one to many join?
is it possible to do it and visualise it too, in QGIS or UdiG
what i am trying to do is:
i have households data in polygons
individual data in table
one house contains many individuals
what i need to do is:
identify a house polygon and it should list all the individuals who reside
in that house.
has anyone tried visualising such a join in Udig or QGIS?
regards
--
Dr. Anand Akmanchi
Lecturer in Geoinformatics
Department of Geography
University of Pune
========================================
"Man's mind, once stretched by a new idea, never regains its original
dimensions." - Oliver Wendell Holmes
========================================
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
