[postgis-users] One-to-many join

[Andy Colson]

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