[postgis-users] one to many join

[Burgholzer,Robert]

Certainly this is possible. I suppose the question is, how?

 

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.  :

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:
            SELECT b.oid, a.the_geom, a.house_address, b.person_name
FROM houses as a, persons as b WHERE a.address = b.address

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:

http://soulswimmer.dynalias.net/gis/psql/visualize_demo.sql.txt 



 

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;
 
 

 

 

 

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.

 

Robert W. Burgholzer

Surface Water Modeler

Office of Water Supply and Planning

Virginia Department of Environmental Quality

rwburgholzer at deq.virginia.gov

804-698-4405

Open Source Modeling Tools:

http://sourceforge.net/projects/npsource/

-----Original Message-----
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/1c55afa0/attachment.html>