<html>
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 10 (filtered)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
pre
{margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.EmailStyle17
{font-family:Arial;
color:navy;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Certainly this is possible. I suppose the
question is, how?</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Off the top of my head I can think of a
couple of ways. IN these examples I will assume that the house table has an ID
column called “address” that is shared by each of the corresponding
entries in the persons table. :</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>1. Create a view of a simple one to many join,
that in effect produces an overlapping copy of the house shape for every person
in the house, a la:<br>
SELECT b.oid, a.the_geom, a.house_address, b.person_name FROM
houses as a, persons as b WHERE a.address = b.address</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>2. Create a view of the persons table as a
point layer, by generating a point location relative to the house that they
belong to. In this example I will use a formula to generate the points in an
expanding helix around the centroid of the house shape. This may be a little
messy, but should give you the basic idea, a full text of this VIEW and valid
POSTGIS/POstgreSQL to create the tables for it can be downloaded from:</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><a
href="http://soulswimmer.dynalias.net/gis/psql/visualize_demo.sql.txt">http://soulswimmer.dynalias.net/gis/psql/visualize_demo.sql.txt</a>
<br>
<br>
</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<pre><font size=2 face="Courier New"><span style='font-size:10.0pt'>CREATE OR REPLACE VIEW gview_residents AS </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>SELECT b.oid,a.house_id, b.thisnum, b.person_name, b.person_id, </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> 0.05 * c.base_length * b.thisnum * cos(1.0 * b.thisnum) AS Xpos,</span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> 0.05 * c.base_length * b.thisnum * sin(1.0 * b.thisnum) AS Ypos, </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> setSRID(GeometryFromText ( 'Point(' ||</span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> X(Centroid(a.the_geom)) + 0.05 * c.base_length * b.thisnum * cos(1.0 * </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>b.thisnum) || ' ' ||</span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> Y(Centroid(a.the_geom)) + 0.05 * c.base_length * b.thisnum * sin(1.0 * </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>b.thisnum) || ')'</span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> ), SRID(a.the_geom)) AS the_geom </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>FROM (</span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>-- number of metrics associated with this shape</span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>SELECT a.house_id, a.the_geom, count(b.person_name)::float8 AS totalnum </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>FROM houses as a, persons as b </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>WHERE a.house_id = b.house_id</span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>GROUP BY a.house_id, a.the_geom</span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>) AS a,</span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>(</span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>-- generate a number for each inidividual entry in the table of metrics </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>associated with this shape with a sequence ordering them</span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>SELECT a.oid,a.person_name, a.person_id , a.house_id, </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>count(b.person_name)::float8 AS thisnum </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>FROM persons as a, persons as b </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>WHERE a.house_id = b.house_id</span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>and a.person_name >= b.person_name </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>GROUP BY a.oid, a.person_name, a.house_id, a.person_id </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>ORDER BY a.house_id, a.person_name </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>) AS b, </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>(</span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>-- get and estimated appropriate radius by looking at the mean dimension </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>of the bounding box and multiplying by 0.5</span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>SELECT a.house_id, 0.15 * (abs(Xmin(a.the_geom) - Xmax(a.the_geom)) + </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>abs(Ymin(a.the_geom) - Ymax(a.the_geom))) AS base_length</span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>FROM houses AS a </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>) AS c </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>WHERE a.house_id = b.house_id and b.house_id = c.house_id;</span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> </span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> </span></font></pre>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>All of these examples rely on creating a
VIEW to visualize the data. In each of my examples I included that “oid”
column from the persons table in the VIEW because QGis requires a unique index
column, so provided that you have one of those, you should be OK. I did NOT
use the oid column from the house table in them, because that would create
multiple copies in some of my examples.</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<div>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Robert W. Burgholzer</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Surface Water Modeler</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Office of Water Supply and Planning</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Virginia Department of Environmental
Quality</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><a
href="mailto:rwburgholzer@deq.virginia.gov">rwburgholzer@deq.virginia.gov</a></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>804-698-4405</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Open Source Modeling Tools:</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><a
href="http://sourceforge.net/projects/npsource/">http://sourceforge.net/projects/npsource/</a></span></font></p>
</div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>-----Original Message-----<br>
<b><span style='font-weight:bold'>From:</span></b>
postgis-users-bounces@postgis.refractions.net [mailto:postgis-users-bounces@postgis.refractions.net]
<b><span style='font-weight:bold'>On Behalf Of </span></b>Anand Akmanchi<br>
<b><span style='font-weight:bold'>Sent:</span></b> Wednesday, April 30, 2008
2:23 AM<br>
<b><span style='font-weight:bold'>To:</span></b>
postgis-users@postgis.refractions.net<br>
<b><span style='font-weight:bold'>Subject:</span></b> [postgis-users] one to
many join</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>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>
======================================== </span></font></p>
</div>
</body>
</html>