[postgis-users] One-to-many join

[John Morgan]

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 <andy at squeakycode.net> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120131/4475c993/attachment.html>