Re: [postgis-users] One-to-many join
Andy, I got this working. You tip to use the aggregate function was what I was looking for. However, as I am only on version 8 postgresql I ended up using array_to_string but it worked just the same. Cheers and thanks, Derek On Mon, Jan 30, 2012 at 10:54 PM, Andy Colson wrote: > On 01/30/2012 09:00 PM, John Morgan wrote: > >> Hello, >> I am attempting to get a one (polys) to many (table) to load postgis data >> within a mapserver wms. I have the following defined in the .map file. >> >> DATA "the_geom FROM (SELECT polys.gid AS gid, polys.the_g >> eom AS the_geom, table.pt_id AS pt_id, table.agent AS agent FROM polys >> RIGHT OUTER JOIN >> table ON polys.pt_id = table.pt_id) as new_table USING UNIQUE gid USING >> SRID=4326" >> >> It does load, the layer, however, it doesn't seem to be performing the >> one-to-many for the attributes on identify. Thanks for any feedback. >> >> Cheers, >> Derek >> >> > But that would return the exact same the_geom multiple times. Which > would draw all on top of each other (including the label?), so it would > only look like one. > > What is it you are trying to do? Looks like get all the agent's to > display for one area? > If you are using PG 9, how about something like: > > DATA "the_geom FROM (SELECT polys.gid AS gid, polys.the_geom AS the_geom, > (select string_agg(agent, E'\n') from table where polys.pt_id = > table.pt_id) AS agents FROM polys) as new_table USING UNIQUE gid USING > SRID=4326" > > That'll return all the agents in a single string separated by carage > return. > > -Andy > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] One-to-many join
Hi Andy, My postgis version check returns POSTGIS="1.5.3" I found from SELECT PostGIS_version(); I think what you have provided make sense with what I am trying to do. Essentially, I have a table of polygons with a primary key pt_id. And I have another table of just data values with the foreign key pt_id corresponding to polygons primary key. Also, on the data table are the string values called agents. Essentially, we have a flex application that uses and identify function and we are trying to return the one-to-many agents per polygon. I think this answers Ben's questions. Thanks for your feedback guys. Derek On Mon, Jan 30, 2012 at 10:54 PM, Andy Colson wrote: > On 01/30/2012 09:00 PM, John Morgan wrote: > >> Hello, >> I am attempting to get a one (polys) to many (table) to load postgis data >> within a mapserver wms. I have the following defined in the .map file. >> >> DATA "the_geom FROM (SELECT polys.gid AS gid, polys.the_g >> eom AS the_geom, table.pt_id AS pt_id, table.agent AS agent FROM polys >> RIGHT OUTER JOIN >> table ON polys.pt_id = table.pt_id) as new_table USING UNIQUE gid USING >> SRID=4326" >> >> It does load, the layer, however, it doesn't seem to be performing the >> one-to-many for the attributes on identify. Thanks for any feedback. >> >> Cheers, >> Derek >> >> > But that would return the exact same the_geom multiple times. Which > would draw all on top of each other (including the label?), so it would > only look like one. > > What is it you are trying to do? Looks like get all the agent's to > display for one area? > If you are using PG 9, how about something like: > > DATA "the_geom FROM (SELECT polys.gid AS gid, polys.the_geom AS the_geom, > (select string_agg(agent, E'\n') from table where polys.pt_id = > table.pt_id) AS agents FROM polys) as new_table USING UNIQUE gid USING > SRID=4326" > > That'll return all the agents in a single string separated by carage > return. > > -Andy > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] One-to-many join
John, I'm no expert at this, but assuming that your query returns multiple rows, I suspect this is to do with the WMS layer only identifying one feature (which then only returns one of row). This would seem like standard WMS behaviour. If you have multiple features at the same point, you might need to use WFS. If the query returns multiple rows, you are probably better to ask this on the MapServer list. There may be better ways to do this, but you may need to find the location and provide the multiple rows of data through a secondary query into another window or layer in the window. What mechanism are you using for displaying the maps and identifying - ie qgis, web template, openlayers?? cheers Ben On 31/01/2012, at 11:00 AM, John Morgan wrote: > Hello, > I am attempting to get a one (polys) to many (table) to load postgis data > within a mapserver wms. I have the following defined in the .map file. > > DATA "the_geom FROM (SELECT polys.gid AS gid, polys.the_g > eom AS the_geom, table.pt_id AS pt_id, table.agent AS agent FROM polys RIGHT > OUTER JOIN > table ON polys.pt_id = table.pt_id) as new_table USING UNIQUE gid USING > SRID=4326" > > It does load, the layer, however, it doesn't seem to be performing the > one-to-many for the attributes on identify. Thanks for any feedback. > > Cheers, > Derek > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] One-to-many join
On 01/30/2012 09:00 PM, John Morgan wrote: Hello, I am attempting to get a one (polys) to many (table) to load postgis data within a mapserver wms. I have the following defined in the .map file. DATA "the_geom FROM (SELECT polys.gid AS gid, polys.the_g eom AS the_geom, table.pt_id AS pt_id, table.agent AS agent FROM polys RIGHT OUTER JOIN table ON polys.pt_id = table.pt_id) as new_table USING UNIQUE gid USING SRID=4326" It does load, the layer, however, it doesn't seem to be performing the one-to-many for the attributes on identify. Thanks for any feedback. Cheers, Derek But that would return the exact same the_geom multiple times. Which would draw all on top of each other (including the label?), so it would only look like one. What is it you are trying to do? Looks like get all the agent's to display for one area? If you are using PG 9, how about something like: DATA "the_geom FROM (SELECT polys.gid AS gid, polys.the_geom AS the_geom, (select string_agg(agent, E'\n') from table where polys.pt_id = table.pt_id) AS agents FROM polys) as new_table USING UNIQUE gid USING SRID=4326" That'll return all the agents in a single string separated by carage return. -Andy ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] One-to-many join
Hello, I am attempting to get a one (polys) to many (table) to load postgis data within a mapserver wms. I have the following defined in the .map file. DATA "the_geom FROM (SELECT polys.gid AS gid, polys.the_g eom AS the_geom, table.pt_id AS pt_id, table.agent AS agent FROM polys RIGHT OUTER JOIN table ON polys.pt_id = table.pt_id) as new_table USING UNIQUE gid USING SRID=4326" It does load, the layer, however, it doesn't seem to be performing the one-to-many for the attributes on identify. Thanks for any feedback. Cheers, Derek ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
RE: [postgis-users] one to many join
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
RE: [postgis-users] one to many join
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
[EMAIL PROTECTED]
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/
-Original Message-
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
[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
