[postgis-users] one to many join

[Paragon Corporation]

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: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Anand
Akmanchi
Sent: Wednesday, April 30, 2008 2:23 AM
To: postgis-users at 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
======================================== 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080430/29a1d9eb/attachment.html>