[postgis-users] RE : spatial join and summarize, list all names

[Hugues François]

Hello,

I agree Stephen : arrays should do the job.

Note that array functions are here : http://www.postgresql.org/docs/9.1/static/functions-array.html

But array_agg is in the aggregate functions part of the doc : http://www.postgresql.org/docs/9.1/static/functions-aggregate.html

Hugues


-------- Message d'origine--------
De: postgis-users-bounces at lists.osgeo.org de la part de Stephen Woodbridge
Date: sam. 23/03/2013 13:46
À: postgis-users at lists.osgeo.org
Objet : Re: [postgis-users] spatial join and summarize, list all names
 

How about something like:

SELECT p.p_name,
     array_to_string(ARRAY_AGG(Grade.Mark),';') As marks
FROM Student
LEFT JOIN Grade ON Grade.Student_id = Student.Id
GROUP BY Student.Name;

Aggregate into an array and then convert the array to a string. Look at 
the array functions in the manual for more information.

-Steve W

On 3/22/2013 7:20 PM, Tyler Frazier wrote:
> Hello,
>
> I'm pretty new to postgreSQL / postGIS and am running with pgadmin and
> using with QGIS and R.
>
> I am attempting to perform a Spatial Join of 3000 thousand towns
> (points) to 1000 enumeration areas (polygons) where the name of each
> town which shares a common geometry with its enumeration area is
> included as an attribute in a field.  In doing so, I have started with
> the following command.
>
> SELECT towns_r1.name, towns_r1.twn_ea_nos,
>                   ea_r1.gid, ea_r1.ea_nos, ea_r1.the_geom
> INTO TABLE region1_eas
> FROM towns_r1, ea_r1
> WHERE ST_Within(towns_r1.the_geom, ea_r1.the_geom);
>
> which gives me a table of 3000 enumerations areas, with the name of each
> individual town and the geometry for each enumeration area repeated,
> depending on how many towns were located within it.
>
> What I am attempting to achieve, is to summarize or aggregate to 1000
> enumeration areas, where each row has a field (single field the names of
> all towns listed within?), which includes the name of each town located
> within that enumeration area.
>
> any pointers on how to proceed?
>
> Thanks!
> Ty
>
>
> Tyler Frazier, Postdoctoral Fellow
> Santa Fe Institute
> tyler at santafe.edu <mailto:tyler at santafe.edu>
>
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 4020 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130323/eff9bd92/attachment.bin>