<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16640" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=468062122-30042008><FONT face=Arial
color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=468062122-30042008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=468062122-30042008><FONT face=Arial
color=#0000ff size=2>Below is a SUM aggregate function we commonly
use. You may want to change the name sum to something else like
group_agg</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=468062122-30042008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=468062122-30042008><FONT face=Arial
color=#0000ff size=2>CREATE OR REPLACE FUNCTION catenate(text, text)<BR>
RETURNS text AS<BR>$BODY$<BR> SELECT COALESCE($1
|| $2,$1,$2,NULL)<BR> $BODY$<BR> LANGUAGE 'sql'
IMMUTABLE;</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=468062122-30042008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=468062122-30042008><SPAN
class=468062122-30042008><FONT face=Arial color=#0000ff size=2>CREATE AGGREGATE
sum(text) (<BR> SFUNC=catenate,<BR>
STYPE=text<BR>);</FONT></SPAN></SPAN></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=468062122-30042008><FONT face=Arial color=#0000ff size=2>Then
create view;</FONT></SPAN></DIV>
<DIV><SPAN class=468062122-30042008><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=468062122-30042008><FONT face=Arial color=#0000ff size=2>CREATE
VIEW vwhouseholdind AS</FONT></SPAN></DIV>
<DIV><SPAN class=468062122-30042008><FONT face=Arial color=#0000ff size=2>SELECT
hh.gid, hh.the_geom, SUM(i.first_name || ' ' || i.last_name || E'\r\n') )
as familymembers</FONT></SPAN></DIV>
<DIV><SPAN class=468062122-30042008><FONT face=Arial color=#0000ff size=2>FROM
households hh LEFT JOIN </FONT></SPAN></DIV>
<DIV><SPAN class=468062122-30042008><FONT face=Arial color=#0000ff
size=2> (SELECT household_id, first_name, last_name
</FONT></SPAN></DIV>
<DIV><SPAN class=468062122-30042008><FONT face=Arial color=#0000ff
size=2> FROM
individuals ORDER BY household_id, last_name, first_name) i
</FONT></SPAN></DIV>
<DIV><SPAN class=468062122-30042008><FONT face=Arial color=#0000ff
size=2> ON hh.house_holdid =
i.household_id</FONT></SPAN></DIV>
<DIV><SPAN class=468062122-30042008><FONT face=Arial color=#0000ff size=2>GROUP
BY hh.household_id;</FONT></SPAN></DIV>
<DIV><SPAN class=468062122-30042008><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=468062122-30042008><FONT face=Arial color=#0000ff size=2>Then
just use the view in Quantum GIS. All the family members will show in the
familymembers column broken out by carriage returns.</FONT></SPAN></DIV>
<DIV><SPAN class=468062122-30042008><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=468062122-30042008><FONT face=Arial color=#0000ff size=2>Hope
that helps,</FONT></SPAN></DIV>
<DIV><SPAN class=468062122-30042008><FONT face=Arial color=#0000ff
size=2>Regina</FONT></SPAN></DIV>
<DIV><SPAN class=468062122-30042008><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=468062122-30042008><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=468062122-30042008> </SPAN></DIV>
<DIV><BR></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Anand
Akmanchi<BR><B>Sent:</B> Wednesday, April 30, 2008 2:23 AM<BR><B>To:</B>
postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users] one to
many join<BR></FONT><BR></DIV>
<DIV></DIV>Hi people<BR><BR>has anyone in the list tried a one to many
join?<BR>is it possible to do it and visualise it too, in QGIS or
UdiG<BR><BR>what i am trying to do is:<BR>i have households data in
polygons<BR>individual data in table<BR>one house contains many
individuals<BR><BR>what i need to do is:<BR>identify a house polygon and it
should list all the individuals who reside in that house.<BR><BR>has anyone
tried visualising such a join in Udig or QGIS?<BR><BR>regards<BR
clear=all><BR>-- <BR>Dr. Anand Akmanchi<BR>Lecturer in
Geoinformatics<BR>Department of Geography<BR>University of
Pune<BR>========================================<BR>"Man's mind, once stretched
by a new idea, never regains its original dimensions." - Oliver Wendell
Holmes<BR>======================================== </BODY></HTML>